asked on

Return an Identity from a stored procedure

I have a stored procedure that returns the identity value. However, I can't figure out how to get the value in my vb code. This is what I have but don't know what I need to do to get the value that is passed back from the stored procedure.

Dim dt As DataTable
Dim intIdentityID_Out As Integer

dt = DAC.ExecuteDataTable("NameOfStoredProcedure", _
New SqlClient.SqlParameter("Field1", Field1), _
New SqlClient.SqlParameter("Field2", Field2), _
.....
New SqlClient.SqlParameter("Field10", Field10))

vb9666

ASKER

Here's the stored procedure if you need to see that...

CREATE PROCEDURE NameOfStoredProcedure
(@Field1 Integer,
@Field2 Varchar(25),
....
@Field10 Varchar(25),
@IdentityID_OUT Integer OUTPUT)
AS
            INSERT INTO TableName
                        (Field1,
                        Field2,
....
                        Field10)
            VALUES (@Field1,
                  @Field2,
....
                  @Field10)
            --Return the inserted row
            Select @IdentityID_OUT = @@Identity

ASKER CERTIFIED SOLUTION

PaulHews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PaulHews

You might also look at Scope_Identity vs @@Identity

http://dotnetjunkies.com/Article/86F0988E-FED4-414F-BA2E-D01D953C11BE.dcik

vb9666

ASKER

PaulHews,

Thanks, your solution worked! The article on Scope_Identity vs @@Identity was also very useful!

PaulHews

Glad to hear it. :)