SCOPE_IDENTITY issue

[Barno77]

I want to return the value of Identity Key which in this case is an AutoNumber field in my table and place the value of that field of the newly added record into a textbox. Here's what I got so far, my insert works, but no return as of yet:

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim sql As New SqlCommand
'Dim myconn As Data.SqlClient.SqlConnection
Dim mycmd As Data.SqlClient.SqlCommand
Dim ResultReader As SqlDataReader
Dim result As String
Dim mySqlConnection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrin gs("MAS_XCMConnectionString").ConnectionString)

mySqlConnection.Open()
result = ""
ResultReader = Nothing

mycmd = New SqlCommand("SELECT * FROM CA_IT_STORYBOOK", mySqlConnection)

mycmd.CommandText = "INSERT INTO CA_IT_STORYBOOK(SalesOrderNumber, InvoiceNumber, StoryBook) VALUES (012,334,'NewTest3');Select @OutVar = SCOPE_IDENTITY()"
mycmd.ExecuteNonQuery()
' mycmd.ExecuteScalar()


If ResultReader.HasRows Then
result = result & vbCrLf & ResultReader.Item(0).ToString()

TextBox1.Text = result
End If

mySqlConnection.Close()
End Sub

[deric]

I think it's because "Select @OutVar = SCOPE_IDENTITY()" is just an assignment statement. If you really need that statement there, select @OutVar again to get its value. If not, you can select SCOPE_IDENTITY() directly instead.

You can do it like this:

mycmd.CommandText = "INSERT INTO CA_IT_STORYBOOK(SalesOrderNumber, InvoiceNumber, StoryBook) VALUES (012,334,'NewTest3');Select @@IDENTITY"

Visit this to know more about it.

Also, is your TextBox1 a multiline control? You are using a carriage return (vbCrLf), try to take that away if your textbox is not a multiline.

[ganeshkumar08]

The main difference between @@SCOPE_IDENTITY and @@IDENTITY is

@@SCOPE_IDENTITY - Gets identity value for the last executed SQL statement with in the scope. Its a local variable
@@IDENTITY - Its a global variable. For the last executed statement if you have any trigger/function then after that stmt executed, it returns that stmts executed identity value.

Based on your requirement you use those varibles

[Barno77]

The main difference between @@SCOPE_IDENTITY and @@IDENTITY is

@@SCOPE_IDENTITY - Gets identity value for the last executed SQL statement with in the scope. Its a local variable
@@IDENTITY - Its a global variable. For the last executed statement if you have any trigger/function then after that stmt executed, it returns that stmts executed identity value.

Based on your requirement you use those varibles

Thanks, how can I actually show the value of the Identity Id in a textbox?

[ck9663]

This would depend on your front-end tool that you used.

-- CK

[Barno77]

This would depend on your front-end tool that you used.

-- CK

I just have a regular asp page with a button and a textbox. When I hit the button I want my record is being inserted into the table, but then I want to retrieve the Identity Id and show it in the textbox.

[ck9663]

First, make sure your stored proc/function is already returning what you want. On your front-end side, create a recordset object. Execute your module. It will return the value as an object of the recordset (column). You can store that value as value property of your textbox object inside your form.

-- CK