By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,396 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

SCOPE_IDENTITY issue

P: 22
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
Apr 22 '08 #1
Share this Question
Share on Google+
6 Replies


P: 92
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 23 '08 #2

ganeshkumar08
P: 31
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
Apr 23 '08 #3

P: 22
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?
Apr 29 '08 #4

ck9663
Expert 2.5K+
P: 2,878
This would depend on your front-end tool that you used.

-- CK
Apr 29 '08 #5

P: 22
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.
Apr 29 '08 #6

ck9663
Expert 2.5K+
P: 2,878
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
Apr 30 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.