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.

Jason - check out "Managing an @@IDentity Crisis" by Bill Vaughn at
http://www.betav.com -> Articles -> MSDN
"Jason James" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

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.

On Thu, 18 Aug 2005 09:10:00 -0400, "W.G. Ryan MVP"
<(E-Mail Removed)> wrote:

>Jason - check out "Managing an @@IDentity Crisis" by Bill Vaughn at
>http://www.betav.com -> Articles -> MSDN
>"Jason James" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> 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.
>
>