Set value of bound field to NULL using DataGridViewComboBoxColumn in DataGridView

Question

I have a DataGridViewComboBoxColumn in a DataGridView that's based on a lookup table.

The ValueMember and DisplayMember fields are bound to string columns in the DataTable. All rows have a value for both fields - except for a special record where the value field is deliberately set to NULL.

However, when I choose this record an empty string is used instead of DBNull.Value in the DataTable bound to the DataGridView. This is happening at data entry time, before the data is pushed to the database.

I've checked out the DataGridViewComboBoxColumn and DataGridView classes - there appears to be no simple way to customise this behaviour. Does anybody know if this is possible?

Answer 1

Well, I did come up with a solution. Because the DataGridView is data bound, I implemented the ColumnChanging event for the DataTable. Sample code:

void Schedule_ColumnChanging(object sender, DataColumnChangeEventArgs e)
    {
        if 
            ( 
                e.Column.ColumnName == "Site" &&
                e.ProposedValue is string && 
                e.ProposedValue as string == ""
            )
            e.ProposedValue = DBNull.Value;
    }

This does the trick. Although I'd like to know if there is a solution that can be implemented at the DataGridView control level.

Answer 2

I use a typed dataset to provide the values for the dropdown. After filling values from the database I add another row to provide the null support. The final lines of the method that populates the datatable look like this...

        // Fill dt from datatable...
        var nullRow = rv.NewRunDropDownEntryRow();
        nullRow.SetIDNull();
        nullRow.Title = "None";
        rv.AddRunDropDownEntryRow(nullRow);
        rv.AcceptChanges();

After that it just works! The null value displays as "None" in the dropdown, you can easily update to or from null in the bound row.

HTH - Richard