But why? An update using the dataadapter inserts a new record

[stuartco]

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

 ' 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)

unique = (component.AddComponent("parent"))

' set up the new row

 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

dataRows(0) = ds.Tables("tblCostingComponent").Rows.Find(unique)
dataRows(0).Item("GrossQuantity") = 10000

' then update the database

 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

 

[jmcilhinney]

You have called the NewRow method of your DataTable, called your variable "newRow", and then called the Add method to put it in the table, and you're asking why it's inserting a new row? If you want to update a row then you need to edit an existing row, i.e. one that was retrieved from the database rather than one you just added yourself.