Getting The new Identity Value

[Wayne Wengert]

I have a VB.NET app in which I am using the following code to add a new row.
ds.Tables("UnitsTable").Rows.Add(dr) ' Add the new row

da.InsertCommand = cmdBuilder.GetInsertCommand

da.Update(ds, "UnitsTable")

At this point I want to get the value of the Identity field that has just
been added to the underlying table ("UnitsTable" is based on a Select
against a table named "Units")
I searched google but the examples I found were using recordsets and I could
not adapt them to work. Pointers to any examples or explinations will be
appreciated.

Wayne

 

[Joe Fallon]

For SQL Server, there is a way to send 2 statements separated by a
semi-colon.

The first is your update statement the second is:
Select @@Scope_Identity

So you should be able to set your ID to the returned Identity value if you
send 2 statements.

======================================================
For Access and Oracle (and SQL Server if you don't use multiple statements):
You have to trap the RowUpdated event and then post the new identity value.
================================================================
In my update method I have some code like this:

'handle the RowUpdated event to get the Identity value back from SQL Server
'w/o the real Identity value, the child records won't be added to SQL
Server.

AddHandler da_Eimhdr.RowUpdated, AddressOf da_Handle_RowUpdated

'parent table
da_Eimhdr.Update(NewEimhdrRecords)

'child table
da_Eimln.Update(NewEimlnRecords)
================================================================
'this is how to handle the insert of each row:

Private Sub da_Handle_RowUpdated(ByVal sender As Object, ByVal e As
SqlRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue And e.StatementType =
StatementType.Insert Then
e.Row("eimkey") = GetIdentity(e.Command.Connection)
e.Row.AcceptChanges()

'use this if you do not want to AcceptChanges for each row.
'e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub
================================================================
Private Function GetIdentity(ByRef cnn As SqlConnection) As Integer
Dim oCmd As New SqlCommand("SELECT @@IDENTITY", cnn)
Dim x As Object = oCmd.ExecuteScalar()
Return CInt(x)
End Function
================================================================

 

[Wayne Wengert]

Joe;

Thanks for all that information. I used your function approach and that
works fine. I am trying to understand the handler approach and will also try
that to see what I learn.

Wayne