469,271 Members | 1,023 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Getting The new Identity Value

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
Nov 21 '05 #1
2 2353
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

Nov 21 '05 #2
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******@nospamtwcny.rr.com> wrote in message
news:%2***************@TK2MSFTNGP09.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_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


Nov 21 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Edward | last post: by
4 posts views Thread by Nathan Sokalski | last post: by
3 posts views Thread by Jason L James | last post: by
3 posts views Thread by Atul | last post: by
33 posts views Thread by JamesB | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.