• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1247
  • Last Modified:

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
0
DeniseGoodheart
Asked:
DeniseGoodheart
  • 3
  • 3
2 Solutions
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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