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
================================================== ==============
--
Joe Fallon
"Wayne Wengert" <wa***************@wengert.com> wrote in message
news:On**************@TK2MSFTNGP11.phx.gbl...
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