lundi 5 octobre 2015

Cannot Insert The Value of Null Using DataGridView and DataTable

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?




Aucun commentaire:

Enregistrer un commentaire