none
get SCOPE_IDENTITY() RRS feed

  • Question

  • hello there...

    I trying for a couple of days to get the returned value(scope_identity) from an insertion stored procedure in sql 2005 expr.

    I 've created the stored procedure using SQL Server Management Studio Express

    The procedure is executed perfectly on the SQL management studio:

    [dbo].[ins]

    @val nvarchar(50)=null

    AS

    BEGIN

    INSERT INTO tbl1

    (val)

    VALUES (@val)

    return scope_identity()

    END

    ----------------------------

    //---- this is the execution code---//

    EXEC @return_value = [dbo].[ins]

    @val = N'anyval'

    SELECT 'Return Value' = @return_value

    //---- this is the execution code---//

     

    I tried to catch this return value in several ways using VB.Net 2005 but i can't figure it out how it works...

     

    First, i tried to read the data from the queriesTableAdapter(where the stored proc. been placed there automaticaly with drag'n'drop from the server explorer)  inside a dataSet but it didn't worked...

    Now i am trying to ignore the dataSet completely and program my command execution.

    This is the code i wrote:

     

    Dim conns As String = ConfigurationManager.ConnectionStrings("testCS").ConnectionString

    Dim conn As New SqlConnection(conns)

    conn.Open()

    Dim cmd As New SqlCommand("ins", conn)

    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters("@return_value").Direction = ParameterDirection.ReturnValue  '(i am not sure if i have to use this line)

    cmd.Parameters.AddWithValue("@val", TextBox1.Text.ToString)

    Dim dr As SqlDataReader = cmd.ExecuteReader()

    ...

    I don't know what to do afterwards.I 've tried so many combinations that my brain feels like chewing gum!!!

    ANY clear VB solution about getting scope_identity after inserting with stored proc. is my passport to calmness Smile

    Your time is very much appreciated

    Sunday, February 10, 2008 1:04 AM

Answers

  • Code Snippet

    Imports System.Data.SqlClient

    Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Using cn As SqlConnection = New SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Adam;Data Source=TURNER-1B9A9553\SQLEXPRESS")

    cn.Open()

    Using cmd As SqlCommand = New SqlCommand("usp_ReturnScopeIdentity", cn)

    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.AddWithValue("@value", 1)

    Dim dr As SqlDataReader = cmd.ExecuteReader

    While dr.Read

    MsgBox(dr(0).ToString)

    End While

    End Using

    cn.Close()

    End Using

    End Sub

    End Class

     

     

    Code Snippet

    CREATE PROCEDURE usp_ReturnScopeIdentity

    @value int

    AS

    INSERT INTO myScope(myValue)VALUES(@value)

    SELECT SCOPE_IDENTITY()

     

     

    First, you want to SELECT to return a resultset not -1, 0, 1 which is the purpose of RETURN

    Secondly, You have to read your reader in a while loop to get the value.

     

    Other than that, you're all set.

     

    Adam

    Sunday, February 10, 2008 2:36 AM
  • ...or more accurately, since you're only returning 1 value, you can use the cmd.ExecuteScalar method instead of the reader. It will return the first row of the first column of the resultset which is what you want.

     

    Code Snippet

    Using cn As SqlConnection = New SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Adam;Data Source=TURNER-1B9A9553\SQLEXPRESS")

    cn.Open()

    Using cmd As SqlCommand = New SqlCommand("usp_ReturnScopeIdentity", cn)

    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.AddWithValue("@value", 1)

    MsgBox(cmd.ExecuteScalar())

    End Using

    cn.Close()

    End Using

     

     

    ...but just remember to SELECT the SCOPE_IDENTITY() not RETURN it.

     

    (ExecuteNonQuery will only return -1, 0, 1 only)

     

    Adam

    Sunday, February 10, 2008 6:24 PM
  • See this knowledge base article:  http://support.microsoft.com/kb/q194792/
    Sunday, February 10, 2008 9:15 PM

All replies

  • First of all, note that stored procedure return values are of type INT.  If your identity columns use a different data type, your approach may be invalid.

     

    Based on the stored proc that you used, you could use cmd.ExecuteNonQuery().

     

    After you ExecuteNonQuery(), assuming everything else is right, DirectCast(cmd.Parameters("@return_value").Value, Integer) should yield the value.

     

    Sunday, February 10, 2008 1:57 AM
  • Code Snippet

    Imports System.Data.SqlClient

    Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Using cn As SqlConnection = New SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Adam;Data Source=TURNER-1B9A9553\SQLEXPRESS")

    cn.Open()

    Using cmd As SqlCommand = New SqlCommand("usp_ReturnScopeIdentity", cn)

    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.AddWithValue("@value", 1)

    Dim dr As SqlDataReader = cmd.ExecuteReader

    While dr.Read

    MsgBox(dr(0).ToString)

    End While

    End Using

    cn.Close()

    End Using

    End Sub

    End Class

     

     

    Code Snippet

    CREATE PROCEDURE usp_ReturnScopeIdentity

    @value int

    AS

    INSERT INTO myScope(myValue)VALUES(@value)

    SELECT SCOPE_IDENTITY()

     

     

    First, you want to SELECT to return a resultset not -1, 0, 1 which is the purpose of RETURN

    Secondly, You have to read your reader in a while loop to get the value.

     

    Other than that, you're all set.

     

    Adam

    Sunday, February 10, 2008 2:36 AM
  • ...or more accurately, since you're only returning 1 value, you can use the cmd.ExecuteScalar method instead of the reader. It will return the first row of the first column of the resultset which is what you want.

     

    Code Snippet

    Using cn As SqlConnection = New SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Adam;Data Source=TURNER-1B9A9553\SQLEXPRESS")

    cn.Open()

    Using cmd As SqlCommand = New SqlCommand("usp_ReturnScopeIdentity", cn)

    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.AddWithValue("@value", 1)

    MsgBox(cmd.ExecuteScalar())

    End Using

    cn.Close()

    End Using

     

     

    ...but just remember to SELECT the SCOPE_IDENTITY() not RETURN it.

     

    (ExecuteNonQuery will only return -1, 0, 1 only)

     

    Adam

    Sunday, February 10, 2008 6:24 PM
  • thanks a lot for your immediate response.

    I have already tried to catch this loop of data reader but the code inside the loop where not executed. (while ds.read)

    I will try again tomorow, using also the solution with DirectCast and I will let you know..

     

     

    Sunday, February 10, 2008 6:58 PM
  • See this knowledge base article:  http://support.microsoft.com/kb/q194792/
    Sunday, February 10, 2008 9:15 PM
  • ok guys,

    the solution is found,

    thanks a lot,really...

     

    Tuesday, February 12, 2008 11:42 AM