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("Unit sTable").Rows.A dd(dr) ' Add the new row

da.InsertComman d = cmdBuilder.GetI nsertCommand

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_Identit y

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.RowUp dated, AddressOf da_Handle_RowUp dated

'parent table
da_Eimhdr.Updat e(NewEimhdrReco rds)

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

Private Sub da_Handle_RowUp dated(ByVal sender As Object, ByVal e As
SqlRowUpdatedEv entArgs)
If e.Status = UpdateStatus.Co ntinue And e.StatementType =
StatementType.I nsert Then
e.Row("eimkey") = GetIdentity(e.C ommand.Connecti on)
e.Row.AcceptCha nges()

'use this if you do not want to AcceptChanges for each row.
'e.Status = UpdateStatus.Sk ipCurrentRow
End If
End Sub
=============== =============== =============== =============== ====
Private Function GetIdentity(ByR ef cnn As SqlConnection) As Integer
Dim oCmd As New SqlCommand("SEL ECT @@IDENTITY", cnn)
Dim x As Object = oCmd.ExecuteSca lar()
Return CInt(x)
End Function
=============== =============== =============== =============== ====
--
Joe Fallon


"Wayne Wengert" <wa************ ***@wengert.com > wrote in message
news:On******** ******@TK2MSFTN GP11.phx.gbl...

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

da.InsertComman d = cmdBuilder.GetI nsertCommand

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

[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

"Joe Fallon" <jf******@nospa mtwcny.rr.com> wrote in message
news:%2******** *******@TK2MSFT NGP09.phx.gbl.. .

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_Identit y

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.RowUp dated, AddressOf da_Handle_RowUp dated

'parent table
da_Eimhdr.Updat e(NewEimhdrReco rds)

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

Private Sub da_Handle_RowUp dated(ByVal sender As Object, ByVal e As
SqlRowUpdatedEv entArgs)
If e.Status = UpdateStatus.Co ntinue And e.StatementType =
StatementType.I nsert Then
e.Row("eimkey") = GetIdentity(e.C ommand.Connecti on)
e.Row.AcceptCha nges()

'use this if you do not want to AcceptChanges for each row.
'e.Status = UpdateStatus.Sk ipCurrentRow
End If
End Sub
=============== =============== =============== =============== ====
Private Function GetIdentity(ByR ef cnn As SqlConnection) As Integer
Dim oCmd As New SqlCommand("SEL ECT @@IDENTITY", cnn)
Dim x As Object = oCmd.ExecuteSca lar()
Return CInt(x)
End Function
=============== =============== =============== =============== ====
--
Joe Fallon


"Wayne Wengert" <wa************ ***@wengert.com > wrote in message
news:On******** ******@TK2MSFTN GP11.phx.gbl...

I have a VB.NET app in which I am using the following code to add a new

row.

ds.Tables("Unit sTable").Rows.A dd(dr) ' Add the new row

da.InsertComman d = cmdBuilder.GetI nsertCommand

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