Cannot Insert The Value of Null Using DataGridView and DataTable

Question

I'm using a DataGridView control in combination with a DataTable to edit an SQL table. I have two fields of the table that are Checkboxes bound to bit not null SQL columns named acoustic and hud. When I add a new row and leave the hud column unchecked I get the error below. It does not matter what I set the acoustic column to. If I check the hud column, but leave the acoustic column unchecked, it works fine.

Cannot insert the value NULL into column 'hud', table 'rawVinyl'; column does not allow nulls. INSERT fails.

The definition for the table is:

CREATE TABLE rawVinyl
(
    storesId    nvarchar(12) NOT NULL,
    baseColor   nvarchar(50) NOT NULL,
    shadeColor  nvarchar(50) NULL,
    rollWidth   float NOT NULL,
    shadeHeight float NULL,
    acoustic    bit NOT NULL,
    hud         bit NOT NULL
)

this is the designer generated code for the checkbox I'm having trouble with. The code for the acoustic column looks exactly the same with the names changed.

private System.Windows.Forms.DataGridViewCheckBoxColumn hud;
...
this.hud = new System.Windows.Forms.DataGridViewCheckBoxColumn();
...
this.hud.DataPropertyName = "hud";
this.hud.HeaderText = "H.U.D.";
this.hud.Name = "hud";
this.hud.Width = 46;

The code to populate the DataGridView is

private const string selectQuery = "SELECT storesId, baseColor, shadeColor, rollWidth, shadeHeight, acoustic, hud FROM rawVinyl ORDER BY storesId";
...
DataTable tbl = new DataTable();
using( SqlDataAdapter data = new SqlDataAdapter(selectQuery, Global.ConnectionString) )
{
    data.Fill(tbl);
    bindingSource1.DataSource = tbl;
}

And last but not least, the code to save the changes is:

DataTable tbl = bindingSource1.DataSource as DataTable;
DataTable changes = tbl.GetChanges();
if( changes != null )
{
    using( SqlDataAdapter sda = new SqlDataAdapter(selectQuery,Global.ConnectionString) )
    using( SqlCommandBuilder scb = new SqlCommandBuilder(sda) )
    {
        sda.UpdateBatchSize = 0; // do it all at once.
        sda.Update(changes);
        tbl.AcceptChanges();
    }
}

I also tried setting the default values for the Checkbox column as follows, but it did not make one bit of difference:

this.hud.FalseValue = false;
this.hud.IndeterminateValue = false;
this.hud.TrueValue = true;

The main thing I don't understand is why don't I have this issue with the acoustic column?

tezzo · Accepted Answer · 2015-10-07 12:21:34Z

1

You can set DataColumn.DefaultValue of acoustic and hud.

tbl.Columns["acoustic"].DefaultValue = false;
tbl.Columns["hud"].DefaultValue = false;

Maybe in SQL server you have already set acoustic default value so the error appears only for hud field.

edited Oct 7, 2015 at 12:21

answered Oct 6, 2015 at 9:03

tezzo

11.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Drew Chapin Over a year ago

This worked! Thank you very much. Also, you used parentheses instead of brackets. I put in an edit to correct that.

Collectives™ on Stack Overflow

Cannot Insert The Value of Null Using DataGridView and DataTable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related