Using Scope_Identity with a data adapter

[Jason James]

HI all,

I have built the SELECT, INSERT, UPDATE and DELETE SP
for my application. However, what I would like to be able to
do is return the SCOPE_IDENTITY value from the INSERT SP
to my VB.Net app. Can anyone tell me how to get the
SCOPE_IDENTITY value from the SP and put it back into the
datarow for the row that has just been added?

I have previously been using @@IDENTITY and executing
a cmd.ExecuteScalar to obtain the ID of the last inserted
record by handling the rowupdated event of the data adapter.

Like this:

Private Sub da_RowUpdated(ByVal sender As Object, ByVal e As
System.Data.SqlClient.SqlRowUpdatedEventArgs) Handles da.RowUpdated
If e.StatementType = StatementType.Insert And e.Status =
UpdateStatus.Continue Then
Dim cmd As New SqlCommand("SELECT @@IDENTITY FROM
tblParts", conn)
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Dim id As Int32 = cmd.ExecuteScalar()
conn.Close()
e.Row("iID") = id
ds.AcceptChanges()
End If
End Sub



Any thoughts would be greatly appreciated.

Many thanks,

Jason.

 

[W.G. Ryan MVP]

Jason - check out "Managing an @@IDentity Crisis" by Bill Vaughn at
http://www.betav.com -> Articles -> MSDN

 

[Jason James]

Bill,

that's fantastic. Thanks for pointing me in the right direction.
I already had the JET technique sorted, but I was using it for
SQL2K as well. This will save some coding.

Kind regards,

Jason.