Getting Identity back

  • Thread starter Thread starter Tina
  • Start date Start date
T

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
 
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
 
Gary, thanks. That worked perfectly.
T
Gary Blakely said:
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
Click to expand...
 
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.
 
I agree, less trips to the server. But why recommend using @@IDENTITY at all
anymore?
 
Greg said:
I agree, less trips to the server. But why recommend using @@IDENTITY at all
anymore?
Click to expand...

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