Getting Identity back

12 Nov 2005 5:11 PM

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

12 Nov 2005 6:13 PM

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

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

Show quote

"Tina" <tinamseaburn@nospammeexcite.com> wrote in message
news:uHA%23Qw65FHA.1248@TK2MSFTNGP14.phx.gbl...
> 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
>
>

12 Nov 2005 9:59 PM

Tina

Gary, thanks. That worked perfectly.
T
Show quote

"Gary Blakely" <seemywebsite@nospam.com> wrote in message
news:u32eXT75FHA.1248@TK2MSFTNGP14.phx.gbl...
> 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
>
> "Tina" <tinamseaburn@nospammeexcite.com> wrote in message
> news:uHA%23Qw65FHA.1248@TK2MSFTNGP14.phx.gbl...
>> 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
>>
>>
>
>

13 Nov 2005 5:00 PM

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.

Tina wrote:
Show quote

> Gary, thanks. That worked perfectly.
> T
> "Gary Blakely" <seemywebsite@nospam.com> wrote in message
> news:u32eXT75FHA.1248@TK2MSFTNGP14.phx.gbl...
>> 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
>>
>> "Tina" <tinamseaburn@nospammeexcite.com> wrote in message
>> news:uHA%23Qw65FHA.1248@TK2MSFTNGP14.phx.gbl...
>>> 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
>>>
>>>
>>
>
>

13 Nov 2005 5:13 PM

Greg Burns

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

Show quote

"Sericinus hunter" <serh***@flash.net> wrote in message
news:SiKdf.11197$Lv.5904@newssvr24.news.prodigy.net...
>    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.
>
> Tina wrote:
>> Gary, thanks. That worked perfectly.
>> T
>> "Gary Blakely" <seemywebsite@nospam.com> wrote in message
>> news:u32eXT75FHA.1248@TK2MSFTNGP14.phx.gbl...
>>> 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
>>>
>>> "Tina" <tinamseaburn@nospammeexcite.com> wrote in message
>>> news:uHA%23Qw65FHA.1248@TK2MSFTNGP14.phx.gbl...
>>>> 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
>>>>
>>>>
>>>
>>

13 Nov 2005 5:29 PM

Sericinus hunter

Greg Burns wrote:

> 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