Winforms (VS2005) DataGridViewComboBoxColumn, CTRL+0 and Cannot set Column 'myColumnName' to be null. Please use DBNull instead.

Hello,

I recently had some trouble with setting a dataGridViewComboboxColumn to a dbNull value with the CTRL+0 (zero) keystroke. As you may know, the datagrid supports the CTRL+0 keycode to force a dbNull value into cell and subsequentialy into the bound datasource (a datatable in my case). In other words you can use that to clear out a combo box's value and set it back to "nothing". At least when the bound datatable column allows DbNull to be true.

This is where I experienced some strange behaviour. I used a datagridView to display some customer data. One of the fields specifies an optional characteristic , namely the qualification of a customer. At database level this is enforced with a referential integrity constraint in the form of foreign key specification with a look-up table. The primary key of this look-up table is an integer. Of course in the datagrid , on the comboxbox showing the customer qualification , you will see a description (cfr. valuemember versus DisplayMember).

This is where it goes wrong. When I try to clear an existing value in the combobox , I get the following error message. Cannot set Column '' to be null. Please use DBNull instead.

What is even more strange is that when you have a combobox in the datagridview where the valueMember's type is System.String, You will not have this exception. It just works. It is also only apparent with a datagridViewComboxBoxColumn. Because if I would use a
DataGridViewTextBoxColumn for the "integer" case, I would neither experince any problem with CTRL+0.

I looked for a solution on Google and I stumbled on this post . Somewhere on the middle you will see an entry by Mark Rideout , DataGridView Program Manager Microsoft (27 Jan 2006, 3:34 PM ) . ...And he talks about a bug. But more interssenting for me he also hints a possible work-around.

He talks about overriding the DataGridView.ProcessDataGridViewKey Method . This method calls the key-processing method appropriate to the key pressed. In this method you could check for the CTRL+0 keystroke (D0) . That is exactly what I immediately tried out. I made a custom DataGridView (i.e. I created a class inheriting from the System.Windows.Forms.DataGridView and overrided the ProcessDataGridViewKey(see MSDN) . In this method I check for the CTRL+0 keystroke and some other characteristics . And if everything is true, I programmatically set the value for the currentcell to system.DbNull.Value.

Public Class CustomDataGridView : Inherits System.Windows.Forms.DataGridView


    Protected Overrides Function ProcessDataGridViewKey(ByVal e As System.Windows.Forms.KeyEventArgs) As Boolean

        If (e.KeyCode = Keys.D0 or  e.KeyCode = keys.NumPad0) _
            AndAlso TypeOf MyBase.Columns(MyBase.CurrentCell.ColumnIndex) Is _
              System.Windows.Forms.DataGridViewComboBoxColumn _
            AndAlso MyBase.Columns(MyBase.CurrentCell.ColumnIndex).ValueType IsNot _
              GetType(System.String) Then
            MyBase.CurrentCell.Value = System.DBNull.Value
            Return True
        Else
            Return MyBase.ProcessDataGridViewKey(e)
        End If


    End Function
End Class

And guess what? It works (at least for me)! I also tried it in VS2008. There the same error is thrown ( I gues the bug report got lost somewhere ). But the same trick with ProcessDataGridViewKey works in VS2008.

I haven't tried it yet on other dataTypes like guid's and so. That why i check for datatypes in the code. But for my situation, this solution is OK .

Maybe there is another solution? If you know of one, don't hesitate to let me know...

Meanwhile if you like, you can download my test-program.

Thanks for reading.

Best regards,

Alexander