getting back my sql table @@IDENTITY

[aussie rules]

Hi,

I have a stored proc that inserts a record into a table, with an identity
value.

In my stored proc I return this value with the following code :

set @Pr_ID = @@IDENTITY
return @Pr_ID

I can see using the SQL Profile(as well executing the cmd in SQL Query
Analyzer), that the correct value is being returned.

My problem is 'getting' this value back in my VB.Net code.

the following code returns a 0 value. What is this code doing wrong. Is
executescalar not the proper way to do this ?

Thanks


.......
Dim myParm As SqlParameter = oCmd.Parameters.Add("@Pr_ID", SqlDbType.Int, 4,
SavedPr)

myParm.Direction = ParameterDirection.Output

SavedPr= oCmd.ExecuteScalar()

.........

 

[William Ryan eMVP]

This article should help you
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp
You'll also want to ensure the connection is closed . Bill has another
article on managing an @@Identity crisis which is great, but just close the
connectoin and I think you'll be good to go.

Let me know if this doesn't fix it for you.

HTH,

Bill

 

[Evert Timmer]

Hi, Look below for solution

Hi,

I have a stored proc that inserts a record into a table, with an identity
value.

In my stored proc I return this value with the following code :

set @Pr_ID = @@IDENTITY
return @Pr_ID

Correct...

I can see using the SQL Profile(as well executing the cmd in SQL Query
Analyzer), that the correct value is being returned.

My problem is 'getting' this value back in my VB.Net code.

the following code returns a 0 value. What is this code doing wrong. Is
executescalar not the proper way to do this ?

Thanks


......
Dim myParm As SqlParameter = oCmd.Parameters.Add("@Pr_ID", SqlDbType.Int, 4,

Correct...

myParm.Direction = ParameterDirection.Output

InCorrect!
myParm.Direction = ParameterDirection.ReturnValue

SavedPr= oCmd.ExecuteScalar()

InCorrect!
Your stored procedure is not returning rows so you can not get the first
column of the first row (MSDN Library explanation of the executescalar
method)

Instead of using the ExecuteScalar method, You should use the
ExecuteNonQuery method
And after having executed the stored procedure:

SavedPr = myParm.Value

........

So, your code would be:

' Set up connection and command
....

' Add parameter
Dim myParm As SqlParameter = New SqlParameter("", SqlDbType.Int)
myParm.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(myParm)

myConnection.Open()
myParm.ExecuteNonQuery()
myConnection.Close()

SavedPr = myParm.Value

HTH,
Evert