dataset update fails when inserting new row

Question

I am inserting a new row into a dataset dsStaff1 via DataAdapter1.Update() method. I am getting the following error:

Cannot insert the value NULL into column 'Enabled', table 'dbo.tblStaff'; column does not allow nulls. INSERT fails. The statement has been terminated.

I do have a boolean field 'Enabled' in tblStaff, which is keyword in SQL Server I suppose. It is defaulted to 'True'. I can't really change the name of the field. Is there a work around? OR am I doing something wrong? PS: I am generating insert, update commands by an sqlcommand builder.

Answer 1

If you have a default, then your SQL Command builder must be sending NULL (DBNull.Value) to override the default. The default will apply only if:

• if you don't specify a value for the column in the INSERT
• or if you use the keyword DEFAULT

One of these 2 forms:

--Enabled is not mentioned, will use default
INSERT (col1, col1) VALUES (col1, col2) 

--Enabled will use DEFAULT
INSERT (col1, col1, Enabled) VALUES (col1, col2, DEFAULT) 

Answer 2

just pass a fixed value to the dataset in the update parameters

like

<asp:Parameter Name="col1" Type="boolean" DefaultValue="true"/>

Answer 3

My code:. It fails on Update.

        Dim dr As System.Data.DataRow

        dr = DsStaff1.Tables(0).NewRow()

        dr.BeginEdit()
        dr.Item("FirstName") = txtName.Text
        dr.Item("LastName") = txtSurname.Text

        dr.Item("StaffID") = txtStaffID.Text
        dr.Item("Enabled") = cbActive.Checked
        dr.EndEdit()
        DsStaff1.Tables(0).Rows.Add(dr)

        DataAdapter1.Update(DsStaff1)

Also I am using TableMappings in DataAdapter1

        DataAdapter1.TableMappings.Add("Table", DsStaff1.Tables("tblStaff").ToString)

P.S. Updating and deleting an existing row works fine. It is just inserting a new row doesn't work.