Adding a row using a DataSet

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
 
R

Rik Hemsley

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.

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?

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
 
R

Rik Hemsley

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

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
 
M

Miha Markic [MVP C#]

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.

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

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.
 
R

Rik Hemsley

Miha said:
It should be update when you save it to database.

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

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
 
G

Gregory A Greenman

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.


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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top