How do you clear a cell (so it gets NULL value) in (WPF) DataGrid?

Question

We have a column bound to a sql integer value that is nullable. When the user tries to "clear" the cell in the DataGrid, we get the validation error

"...could not be converted".

How can I make it empty and bind a "null" value to the underlying column?

I have googled this problem for 2 days now and found anything. I tried to edit my comment to you HCL, but it would never save.

I am familiar with the IValueConverters. I wrote one to handle our percentages (so the user can enter "50 %" and it gets converted to ".5"). I am trying to do something very similar, but when the value comes back as a Null (I wrote a dummy IValueConterter to debug it), I just want it to be saved to the database as a Null. Maybe I just need to set it to DBNullValue? It just seems like a lot of work for something that I think might have a built in property. I tried using the TargetNullValue property on the column (DataGridBoundColumn) and setting that to DBNull.Value, but it didn't change the value (based on my dummy IValueConverter it is still coming in as a regular (string) NULL.

I simply want to be able to save a null value to the database for this (integer type) column.

** Latest addition **

Good point HCL, I forget that the whole world isn't ADO sometimes.

I am binding to a SQL table. The column in question is an int that allows NULLs.

The columns are created using AutoGeneratingColumn, so it basically just hooks them up "automagically" (certain styles such as Right Justify are applied in this method, but not for this column). It takes this approach, because the app is pretty much an "Access" replacement. Our CIO mandated we remove Access from the users, so this was the solution (create our own MS Access). Anyway, since it can open any table (and create tables, columns, etc) it just "AutoGenerates" the columns based on the table that was opened. Although, since there are certain columns that the app inherently knows about such as Discount_Pct, when it encounters one of those columns, it does do some "special stuff" (like assign the IValueConverter I described above). Although, like I said... for this particular column, there isn't anything "special" done to it. It is just a regular SQL integer (nullable) that is "AutoGenerated".

Answer 1

I just found the property I was looking for!!

TargetNullValue

This post explains it: HERE

The problem is, these columns are generated using "AutoGenerateColumns" (because the datasource...a sql table... is "dynamic"). If I could figure out how to get the table column (primitive) type, I already know how to see if it is Nullable, then I can set this value for that column. (I still don't understand why the AutoGenerateColumns doesn't handle this automatically!!)