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

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 Jus开发者_如何转开发tify 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".

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!!)

Extending off of Shayne's self-answer, I discovered that the TargetNullValue property is available on DataGridBoundColumn objects. You could use the additional logic described from this comment to make this conditional. Unfortunately, that post does not explain how to relate its logic to a DataGrid, so here is my solution:

<DataGrid AutoGenerateColumns="True" AutoGeneratingColumn="m_grid_AutoGeneratingColumn"/>

And...

public void m_grid_AutoGeneratingColumn(object sender, DataGridAutoGeneratingColumnEventArgs e)
{
    // Make all columns nullable
    ((DataGridBoundColumn)e.Column).Binding.TargetNullValue = string.Empty;
}

I was so frustrated with this problem. I hope this helps someone.

I decided to go the IValueConverter route for this field too.

Here is what I wrote (and it works, I still feel like there must be an easier way! lol):

    [ValueConversion(typeof(Int32), typeof(String))]
    public class IntDBNullConverter : IValueConverter
    {
        public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            if (value == null)
                return "";
            else if (DBNull.Value.Equals(value))
                return "";
            else
                return value.ToString();
        }

        public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            string str = value as string;
            if (String.IsNullOrEmpty(str))
                return DBNull.Value; // returns DBNull.Value 

            Int32 result = 0;

            Int32.TryParse(str, out result);

            return result;
        }
    }