This is driving me mad....I'm trying to update a record in a sql server using the the datadapter - and it refuse to work...
Instead of updating the record in the sql table it adds a new record! But why?
I've simplified the code for this example
' the call to this function adds a blank record into the sql table
' and returns the pk using SELECT_SCOPE() (pk is set to autoincrement)
' set up the new row
' do some other stuff then
' change the data in the row
' then update the database
Any ideas why it inserts rather than updates? If if put and AcceptChanges call after I've added a newRow in the datatable I get a concurreny violation.
Cheers for any help,
Stuart
Instead of updating the record in the sql table it adds a new record! But why?
I've simplified the code for this example
VB.NET:
' fill the dataset at the start
command.CommandText = "SELECT * FROM tblCostingComponent WHERE ParentRef = '" & parentRef & "' ORDER BY Sequence"
adapter.Fill(dt)
dt.TableName = "tblCostingComponent"
primarykey(0) = dt.Columns("ComponentUnique")
dt.PrimaryKey = primarykey
' the call to this function adds a blank record into the sql table
' and returns the pk using SELECT_SCOPE() (pk is set to autoincrement)
VB.NET:
unique = (component.AddComponent("parent"))
' set up the new row
VB.NET:
newRow = ds.Tables("tblCostingComponent").NewRow
newRow.Item("ComponentUnique") = unique
newRow.Item("GrossQuantity") = 0
ds.Tables("tblCostingComponent").Rows.Add(newRow)
' do some other stuff then
' change the data in the row
VB.NET:
dataRows(0) = ds.Tables("tblCostingComponent").Rows.Find(unique)
dataRows(0).Item("GrossQuantity") = 10000
' then update the database
VB.NET:
command = clsDal.OpenConnection
command.CommandText = "SELECT * FROM tblCostingComponent"
da = New SqlDataAdapter(command)
Dim builder As New SqlCommandBuilder(da)
da.Update(datarow)
Any ideas why it inserts rather than updates? If if put and AcceptChanges call after I've added a newRow in the datatable I get a concurreny violation.
Cheers for any help,
Stuart