Get SQL Server SCOPE_IDENTITY Using ADO.NET Command Parameter

Hi,

I’m using ASP.NET 2005.  The following code works using a stored procedure and the SqlDataReader (my Rdr.Read), but I would like to replace the following code with the ADO.NET command parameter to get the SCOPE_IDENTITY:

'Get Identity ID
        If Rdr.Read Then
            Me.lblStatus.Text = "Created Record ID: " & Rdr(0)
        End If

I tried the following with no success to get the SCOPE_IDENTITY:
        cmd.Parameters.Add("SCOPE_IDENTITY", Data.SqlDbType.Int)
        cmd.Parameters(0).Direction = Data.ParameterDirection.ReturnValue

My Stored Pocedure is as follows:
CREATE PROCEDURE [dbo].[spInsTestSite]
@fknGeneratorID [numeric]
AS
INSERT INTO .dbo.aTest
( [GeneratorID])
VALUES
( @fknGeneratorID)
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]



********My entire VB.NET code is as follows:

Private Sub ExecStoredProc()

        Dim sSQL As String

        'Set up sql string and connection
        sSQL = "spInsTestSite"

        'Use clsDatabase class for connection string
        sCon = oCon.SqlConString
        Dim Con As SqlConnection = New SqlConnection(sCon)

        Dim cmd As New SqlCommand
        With cmd
            'Create connection object
            .Connection = New SqlConnection(sCon)
            'Set the SQL
            .CommandText = sSQL
            .CommandType = Data.CommandType.StoredProcedure
        End With

        cmd.Parameters.Add("@fknGeneratorID", Data.SqlDbType.Int)
        cmd.Parameters("@fknGeneratorID").Value = Me.ddlGenerator.SelectedValue
       
        cmd.Connection.Open()
              Dim Rdr As SqlDataReader = cmd.ExecuteReader()
   
        'Get Identity ID
        If Rdr.Read Then
            Me.lblStatus.Text = "Created Record ID: " & Rdr(0)
        End If

        cmd.Connection.Close()
    End Sub

Thanks,
Denise
DeniseGoodheartAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

naveenkohliCommented:
You can't access SQL server variable like that. You have two options.
1. Add an out prameter where you can set SCOPE_IDENTITY value and get it after command has been executed.
2. Add one line at the end of SP , SELECT SCOPE_IDENTITY. And use ExecuteScalar method which will return you this value.
0
DeniseGoodheartAuthor Commented:
Hi naveenkohli,

Will you please provide me with an example?

Thanks,
Denise
0
naveenkohliCommented:
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

naveenkohliCommented:
Here is another one that uses SCOPE_IDENTITY

http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx
0
DeniseGoodheartAuthor Commented:
Hi naveenkohli,

Thanks for your reply.  My code currently uses the SqlDataReader Read method to get the SCOPE_IDENTITY,
and so does the example at davidhayden.com.  I tried experimenting with the ExecuteScaler with no success.  I've been
searching for a good example on the Internet that uses the Command Parameter and ExecuteScaler methods with
no success.

Thanks,
Denise
0
mmarksburyCommented:
I add a Parameter to my SqlCommand, I set the ParameterDirection to "ReturnValue", and after I execute the query, I can get the value.  In the Stored Proc, you simply return SCOPE_IDENTITY()...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DeniseGoodheartAuthor Commented:
Hi All,

Thanks for the help.  I couldn’t get it to work using the ExecuteScalar method, but I was able to get it to work using the ExecuteReader method and by using mmarksbury suggestion, and by finding a good example at http://aspnet.4guysfromrolla.com/articles/062905-1.aspx

My modified code is as follows:
        '1
        cmd.Parameters.Add("@fknGeneratorID", Data.SqlDbType.Int)
        'Get value from drop down list control
        cmd.Parameters("@fknGeneratorID").Value = Me.ddlGenerator.SelectedValue
        '2
        Dim retValParam As New SqlParameter("@RETURN_VALUE", Data.SqlDbType.Int)
        retValParam.Direction = Data.ParameterDirection.ReturnValue
        cmd.Parameters.Add(retValParam)
        cmd.Connection.Open()

        Dim Rdr As SqlDataReader = cmd.ExecuteReader()
        Me.lblStatus.Text = Convert.ToInt32(retValParam.Value)
        cmd.Connection.Close()

I modified my stored procedure to the following:
ALTER PROCEDURE [dbo].[spInsTestSite]
@fknGeneratorID [numeric]
AS
INSERT INTO .dbo.aTest
( [GeneratorID])
VALUES
( @fknGeneratorID)
Return SCOPE_IDENTITY()

Many Thanks,
Denise




0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.