Adding a row using a DataSet

  • Thread starter Thread starter Rik Hemsley
  • Start date Start date
R

Rik Hemsley

I'm adding a row to a table, using this form:
ADataSet.TableName.AddTableNameRow(the, typed, parameters)

My problem is that the Row returned by the above call does not have its
ID field set.

The row is assigned an ID by the database: I can see this by looking at
the data.

Should the Row's ID field be updated, or should I be retrieving this
using another method?

Cheers,
Rik
 
Rik said:
I'm adding a row to a table, using this form:
ADataSet.TableName.AddTableNameRow(the, typed, parameters)

My problem is that the Row returned by the above call does not have its
ID field set.

The row is assigned an ID by the database: I can see this by looking at
the data.
Click to expand...

Of course, this doesn't happen until I use DataAdapter.Update...
Should the Row's ID field be updated, or should I be retrieving this
using another method?
Click to expand...

It seems that DataAdapter.Update only works one way - it doesn't update
the Row in my DataSet with the newly assigned ID.

How do people normally add a new row and get an assigned ID back?

Rik
 
Rik said:
How do people normally add a new row and get an assigned ID back?
Click to expand...

The only thing I can think of is to do a select Max(ID) immediately
after the insert, but I'm worried that this could cause a concurrency
problem in future.

Is there a better way?

Cheers,
Rik
 
Rik,


It should be update when you save it to database.
It seems that DataAdapter.Update only works one way - it doesn't update
the Row in my DataSet with the newly assigned ID.
Click to expand...

If properly configured it does update it - depends on the database though.
How do people normally add a new row and get an assigned ID back?

Rik
Click to expand...

As I've said, the id should be updated only when row is actually saved to
dabase. And it isn't necessary to save it immediately.
 
Miha said:
It should be update when you save it to database.
Click to expand...

It turns out that it _is_ updated, but it's wrong...
If properly configured it does update it - depends on the database though.
Click to expand...

I'm using Access, and I thought the Row object's ID wasn't being
updated, but it was being set to 0. If I add another row, that Row's
object's ID is set to 1, etc.

This looks right, but it's not: If you empty out a table, in Access,
then the next ID to be assigned will not necessarily be 0. It'll be one
after the last one that was assigned.

Going from a blank table to a table with one record might give you a
record with ID 42. The Row object doesn't get this value, though.

I'd say it's very important for the Row object to have the correct ID.
If you experiment with this, you see the Row objects getting consecutive
IDs assigned, which makes you think that these are correct - until you
delete some data from your table, and the IDs become meaningless.

Rik
 
It turns out that it _is_ updated, but it's wrong...


I'm using Access, and I thought the Row object's ID wasn't being
updated, but it was being set to 0. If I add another row, that Row's
object's ID is set to 1, etc.
Click to expand...


If I understand correctly, you're working with an autoincrement
field as your primary key.

If so, add the following code to your program:

cmdRefresh = New OleDbCommand("SELECT @@IDENTITY", cn)
AddHandler DataAdapter.RowUpdated, AddressOf HandleRowUpdated

Private Sub HandleRowUpdated(ByVal sender As Object, & _
ByVal e As OleDbRowUpdatedEventArgs)

If e.Status = UpdateStatus.Continue AndAlso & _
(e.StatementType = StatementType.Insert) Then
e.Row("pk") = CInt(cmdRefresh.ExecuteScalar)
e.Row.AcceptChanges()
End If
End Sub

This should get the keys that the database assigns updated in
your dataset properly.
 
Back
Top