Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

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))
0
vb9666
Asked:
vb9666
  • 3
  • 2
1 Solution
 
vb9666Author Commented:
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
0
 
PaulHewsCommented:
Add an output parameter to your proc:

CREATE PROCEDURE NameOfStoredProcedure
(@ID_Out int = NULL OUTPUT,
@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 @ID_Out = @@Identity

Add an output parameter here:
Dim ParmOut As New SqlClient.SqlParameter("@ID_Out", SqlDbType.Int)
        ParmOut.Direction = ParameterDirection.Output


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


Dim IDOut as integer = Cint(ParmOut.Value)
0
 
PaulHewsCommented:
You might also look at Scope_Identity vs @@Identity

http://dotnetjunkies.com/Article/86F0988E-FED4-414F-BA2E-D01D953C11BE.dcik
0
 
vb9666Author Commented:
PaulHews,

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

0
 
PaulHewsCommented:
Glad to hear it.  :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now