Tuesday 15 July 2008

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

8 comments:

Richard Majece said...
This comment has been removed by the author.
Richard Majece said...

If you want to learn something new about research paper methodology , try to check https://dissertationowl.com/blog/research-project-methodology out! It was useful for me and my college friends

Dedicatedhosting4u said...

I have gone through the search engine for a while to find some sensible data. I found your blog interesting with good presentation and data. keep posting.
Thanks
Resell dedicated servers

Jim Rhodes said...

I am pretty sure this problem is solved now. You can easily fix that. Right?

hp battery repair center said...

Nice post. I learn something new and challenging on blogs I stumbleupon every day. It will always be exciting to read through articles from other writers and use something from their websites.

philalv said...

No, this isn't a legitimate option. Keep in mind that college students do not have enough time to complete their essays or projects. It, if you want to write the greatest essay possible, you can do so by enlisting the help of pay for a paper providers. You can rest confident that only the best service will ensure that the essay papers for students are written by a refined team of specialists.

Alex Jarvis said...

Nice post, have a look at Denver Airport Transportation

Larry said...

Thank you for your remarkable posting! I clearly enjoyed analyzing it. Everyone can find needable information here. Also if you need any help related in choosing color correcting glasses/lenses then prefer the link given. This article will help you to learn about different kind of lenses and help you to pick the right one. Visit color vision deficiency and share your thoughts.