Return Scope_Identity() to .NET?

[jararaca]

Hi,

This is probably an easy question for you experts out there:

After an insert, I know that Scope_Identity() will return the identity of the record just inserted. But how do I pass that back from a stored procedure being called from .NET? In .Net, I'm using the command object's ExecuteNonQuery method to run the stored procedure. I need to know the identity of the record created right after I make the call to the command object's ExecuteNonQuery method.

Thank you!

 

[andrea96]

You could set an output parameter in your stored procedure to the value of Scope_Identity() and get the output parameter's value in .NET.

 

[jararaca]

Thanks. How do you "set an output parameter?" Then, on the .net side, would it simply be:

Dim LastId as integer = commandObj.ExecuteNonQuery("MyInsertProc")?

 

[andrea96]

When declaring the stored procedure parameters, add a line like this.

@ID int OUTPUT

Then you should be able to do this after your insert.

SELECT @ID = Scope_Identity()

In your program add this after the .ExecuteNonQuery statement.

If Not IsDBNull(cmdGetPrice.Parameters("@ID").Value) Then
    Dim LastId As Integer = CInt(commandObj.Parameters("@ID").Value)
End If

Hope this helps.
Andrea

 

[jararaca]

Well, I'd say you ought to take the rest of the day off for the amount of work (on my end) you've accomplished!

Hey, Andrea, thanks a bunch! This is immesureably helpful to me! Really: you've saved me a lot of headache! )) <--NOT triple chins!