NewRow, RowState, and Detached with a CommandBuilder

[Andy]

Hi folks,

I'm using the ADO.NET 1.1 framework and have this ADO.NET dataset I
want to use to add and/or update records on the physical SQLServer
table it came from. I am using an ADO.NET commandBuilder to
automatically generate the update and insert SQL commands.

I start with a SQL statement to select a record from the table using a
unique record key. If the select comes back with an empty dataset, I
add a NewRow to the dataset's table and then populate the columns in
the new row to create a new record. Otherwise, I just modify the
columns in a returned record to update it.

I then reconnect to the database, recreate the ADO.NET dataAdapter with
the original select statement, pass it the modified dataset I first
created, and apply the .update command to the data adapter ... and
nothing happens.

If I display the RowState of the row I added before I call the update
on the dataAdapter, it shows to be "Detached".

My understanding is that NewRow should produce "Added" RowState records
- what am I doing wrong, and how do I get my new row from a "Detached"
to an "Added" state?


Dim nr As System.Data.DataRow
If ds.Tables("MyTable").Rows.Count = 0 Then
nr = ds.Tables("MyTable").NewRow() 'create a record
Else
nr = ds.Tables("MyTable").Rows(0) 'update the record
End If

nr("MyField")="data" 'modify the fields

result=nr.RowState.ToString 'result contains "DETACHED"

 

[Andy]

Hi folks,

It turns out that there are really two steps to add a new record to an
ADO.NET dataset.

The first is you have to define a record with a layout of fields that
match the table. This is done with the .NewRow command; this command
does not actually add a record to the table - it only takes the table's
field definitions and creates a variable in memory that can hold all
the fields the table requires.

You then have to ADD that record to the table. This is similar to
using .AddNew to add a record to a recordset in ADO.


Dim nr As System.Data.DataRow
If ds.Tables("MyTable").Rows.Count = 0 Then
nr = ds.Tables("MyTable").NewRow() 'create a record variable (IN
MEMORY)
ds.Tables("MyTable").Rows.Add(nr) ' add the variable's record to
the table
Else
nr = ds.Tables("MyTable").Rows(0) 'update the record
End If

nr("MyField")="data" 'modify the fields

result=nr.RowState.ToString 'result contains "ADDED"