getting back my sql table @@IDENTITY

  • Thread starter Thread starter aussie rules
  • Start date Start date
A

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

.........
 
Hi, Look below for solution

aussie rules said:
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
Click to expand...

InCorrect!
myParm.Direction = ParameterDirection.ReturnValue
SavedPr= oCmd.ExecuteScalar()
Click to expand...

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
........
Click to expand...

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