Getting Identity back

[Tina]

when I do the following in my VB.Net program, how can I get the value of the
primary key back
which is an Identity column?

(I know how to get identity back with SCOPE_IDENTITY() in T-SQL but I need
to get it back from this kind of code )

Thanks,
T

connPO.Open()
Dim strSQL As String
strSQL = "INSERT INTO Orders " & _
"(JobID, Description, Notes, Status)" & _
"VALUES (@JobID, @Description, @Notes, @Status)"
Dim mycommand As New SqlCommand(strSQL, connPO)
mycommand.Parameters.Add(New SqlParameter("@JobID", JobID))
mycommand.Parameters.Add(New SqlParameter("@Description", Description))
mycommand.Parameters.Add(New SqlParameter("@Notes", Notes))
mycommand.Parameters.Add(New SqlParameter("@Status", Status))
Try
rowsAffected = mycommand.ExecuteNonQuery()
If rowsAffected = 0 Then
Return "Rows Updated were Zero - Update was not effective"
End If
Return ""
Catch db As SqlException
If db.Number <> 2627 Then '2627 means dup add
Return db.Number & " " & db.Message
End If
Catch ex As System.Exception
Return ex.Message
Finally
connPO.Close()
End Try

 

[Gary Blakely]

Tina,
Right before you Return "" enter the following code

'now get the identity back
strSQL = "Select @@IDENTITY as 'Identity'"
Dim GetIDCommand As New SqlCommand(strSQL, connPO)
Dim myReturn as integer = GetIDCommand.ExecuteScalar

 

[Tina]

Gary, thanks. That worked perfectly.
T

Tina,
Right before you Return "" enter the following code

'now get the identity back
strSQL = "Select @@IDENTITY as 'Identity'"
Dim GetIDCommand As New SqlCommand(strSQL, connPO)
Dim myReturn as integer = GetIDCommand.ExecuteScalar

--
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com

 

[Sericinus hunter]

I think it would be better to add something like
SET @retId = @@IDENTITY to the original strSQL and
add a parameter to the SqlCommand.Parameters collection
as an output parameter.
Also, don't mix @@IDENTITY and SCOPE_IDENTITY(), they
are different.

 

[Greg Burns]

I agree, less trips to the server. But why recommend using @@IDENTITY at all
anymore?

 

[Sericinus hunter]

I agree, less trips to the server. But why recommend using @@IDENTITY at all
anymore?

With later versions of SQL Server we have INSTEAD OF triggers. If we
do inserts in this kind of trigger they are done outside of our scope,
so SCOPE_IDENTITY() is not going to work.
But I agree, @@IDENTITY can give you incorrect results in more
situations, than SCOPE_IDENTITY().
Here is the brief overview of different scenarios:
http://www.aspfaq.com/show.asp?id=2174