Retrieving Table ID

  • Thread starter Thread starter Roy Thompson
  • Start date Start date
R

Roy Thompson

Hi

I have a SQL table that has a field "Customer_Id". This is a bigint and it
is an Identity field with an auto increment of 1. The other fields are
Forename and Surname.

I wish to add a new record in Code so I use:


Dim rowNew As DataRow = dstCustomer.Tables(0).NewRow
rowNew("Forename") = Me.txtForename.Text
rowNew("Surname") = Me.txtSurname.Text
dstCustomer.Tables(0).Rows.Add(rowNew)
Dim cb As New SqlCommandBuilder(daCustomer)
daCustomer.Update(dstCustomer.GetChanges())
dstCustomer.AcceptChanges()

Which is all great. My question is how do I now get the Customer _ID field
for the newly added record? I wish to add this ID field to another table.
 
Assuming you are using SQL Server,

1) Set daCustomer.InsertCommand.CommandText = "INSERT INTO
Customer(ForeName, SurName) VALUES (@ForeName, @SurName); SELECT Customer_Id
WHERE Customer_ID = SCOPE_IDENTITY"

2) Change your update statement from
daCustomer.Update(dstCustomer.GetChanges()) to
daCustomer.Update(dstCustomer.Tables(0))

If you use GetChanges(), it gets a little more complicated:

1) You have to trap the RowUpdated event - daCustomer.RowUpdated += new
System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated);

static void da_RowUpdated(object sender,
System.Data.SqlClient.SqlRowUpdatedEventArgs e)
{
if (e.StatementType == System.Data.StatementType.Insert) e.Status =
System.Data.UpdateStatus.SkipCurrentRow;
}

2) You need to merge the changes back to the original dataset
DataSet dsChanges = dstCustomer.GetChanges();
if (dsChanges != null)
{
daCustomer.Update(dsChanges.Tables[0]);
dstCustomer.Merge(dsChanges);
dstCustomer.AcceptChanges();
}

It gets even more complicated if you are updating a set of related tables in
a dataset. You have to do the deleted up the ancestory, the adds next down
the ancestry and finally the updates down the ancestry.

I finally wrote a static helper class that deals with all this stuff.
 
If you are updating a single row then you can use
Select @@IDENTITY
query to retrieve the last inserted column value.
(works with SQL server & access 2000 dbs)
 
Back
Top