dataset update fails when inserting new row
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 fai开发者_运维问答ls. 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.
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)
just pass a fixed value to the dataset in the update parameters
like
<asp:Parameter Name="col1" Type="boolean" DefaultValue="true"/>
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.
精彩评论