How to prevent blank string from becoming DBNull.Value when DGV is bound to DataTable

Question

Hard to post code, but easy to describe how to repro:

Create a new Winforms project (I used VS 2017 and .NET 4.7)

1. Add a DataSet to the project.
2. Within the DataSet, add a DataTable (no adapter).
3. Within the DataTable, add a single column. It will default to varchar, but change it to NOT allow nulls.
4. Add a Form to the project.
5. Add a DataGridView to the form.
6. Set the DataSource for the DGV to the DataTable in the DataSet.
7. Run the program.
8. Click in the single column of the single row.
9. Type some content.
10. Select all the content and hit backspace.
11. Click in the empty second row that appeared.

What happens: A System.Data.NoNullAllowedException is raised indicating that the data column does not allow DBNull.Value.

What should happen: The column in the row bound to the first row of the dgv should be set to blank, not null.

What I've tried:

• Using a CellEndEdit handler to catch the bad value and convert it to string.Empty. This does actually fix SOME cases (when there's more than one column)
• Using a CellValidating handler - not helpful
• Using a CellFormatting handler - not helpful
• Using a CellParsing handler - not helpful
• Using an AddingNew handler on the BindingSource to supply new rows that have non-null values - not helpful

Any other ideas?

Answer 1

The DataGridView can be configured to accommodate the desired usage case to send an Empty.String when a null input is received.

From: DataGridViewCellStyle.DataSourceNullValue Property

Gets or sets the value saved to the data source when the user enters a null value into a cell.

From: DataGridViewColumn.DefaultCellStyle Property

Gets or sets the column's default cell style.

The above properties are used to configure the DataGridView. However, the DataTable needs a slight modification.

From: DataColumn.DefaultValue Property

Gets or sets the default value for the column when you are creating new rows.

You might think that the DataGridVierwColumn's CellTemplate's DefaultNewRowValue Property would supply this, but not in the case of a bound column. If additional columns are used and this property is not changed from the default, the original issue would resurface.

The following example is designed to handle your issue as described in your reproduction guidelines and assumes the DataGridview.DataSource is set to either a DataTable or DataView instance. It uses the DataGridView.BindingContextChanged event to process the DataGridView's auto-generated columns after setting the DataSource property.

public partial class Form1 : Form
{
    private DataTable dt;
    public Form1()
    {
        InitializeComponent();
        dataGridView1.BindingContextChanged += new System.EventHandler(this.dataGridView1_BindingContextChanged);
        dt = new DataTable();
        DataColumn dc = dt.Columns.Add("C0");
        dc.AllowDBNull = false;
        dataGridView1.DataSource = dt.DefaultView;
    }

    private void dataGridView1_BindingContextChanged(object sender, EventArgs e)
    {
        if (dataGridView1.DataSource != null)
        {
            DataTable boundTable = dataGridView1.DataSource as DataTable;
            if (boundTable == null)
            {
                DataView dv = dataGridView1.DataSource as DataView;
                if (dv != null)
                {
                    boundTable = dv.Table;
                }
            }
            if (boundTable != null)
            {
                foreach (DataGridViewColumn c in dataGridView1.Columns)
                {
                    if (c.IsDataBound)
                    {
                        DataColumn dc = boundTable.Columns[c.DataPropertyName];
                        if (!dc.AllowDBNull && dc.DataType == typeof(string))
                        {
                            c.DefaultCellStyle.DataSourceNullValue = string.Empty;
                            dc.DefaultValue = string.Empty;  // this value is pulled for new rows
                        }
                    }
                }
            }
        }
    }
}

Answer 2

This looks like an acceptable solution - tweak as needed to get rid of hard-wired column indexes, etc.

private void dataGridView1_RowValidating(object sender, DataGridViewCellCancelEventArgs e)
{
  var dgv = sender as DataGridView;
  if (null != dgv)
  {
    var row = dgv.Rows[e.RowIndex];
    var drv = row.DataBoundItem as DataRowView;
    if (null != drv)
    {
      var dr = drv.Row as DataSet1.DataTable1Row;
      if (dr.IsNull(0))
      {
        dr.Path = string.Empty;
      }
    }
  }
}

Answer 3

I had a similar problem and solved it by adding the 'ConvertEmptyStringToNull' property on my parameters in aspx page, like this

        <UpdateParameters>
            <asp:Parameter Name="myparam" ConvertEmptyStringToNull="false" />
        </UpdateParameters>

This is my first answer so hope that's OK, it was so simple I though I would post to help others searching