Getting Identity back

in my VB.NET program, when I do the following, how can I get the value of
the primary key back
which is an Identity column?
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
(I notice you also posted in the dotnet forum as I answered there also)
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:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> 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" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> 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
>
You need to call SCOPE_IDENTITY in the same batch that does the INSERT:
strSQL = "INSERT INTO Orders "
...
strSQL = strSQL + "; SELECT SCOPE_IDENTITY()"
The result set is the IDENTITY value. There are a couple of points to note.
Firstly, SCOPE_IDENTITY is SQL Server 2000 / 2005 only. In SQL Server 7
you'll have to use @.@.IDENTITY. Using @.@.IDENTITY means that the return value
will reflect any INSERT done in a trigger if one exists on your table.
That's generally not what is wanted, so in the case of SQL Server 7 on a
table with a trigger you'll have to use a different technique: Issue a
SELECT with a WHERE clause based on alternate key values from among those
you inserted. IDENTITY should never be the only key of a table so it should
always be possible to retrieve the value without using either SCOPE_IDENTITY
or @.@.IDENTITY. For single row inserts however, SCOPE_IDENTITY is more
efficient.
Secondly, the above is bad advice :-). Any INSERT should be done with a
parameterized stored proc rather than dynamically in client code unless you
have an exceptional reason to do otherwise. Since your code is parameterized
anyway I don't know why you wouldn't use a proc here.
I notice you posted independently to at least two other groups.
Multi-posting is very inconsiderate and devalues the newsgroup experience
for everyone. If you really must hit several different groups with your
question then it's better to cross-post - i.e. the same message copied to
multiple groups so that there is just a single thread to continue the
discussion in. Don't cross-post excessively, but 1 or 2 well-chosen groups
in a cross-post is generally acceptable whereas pretty everyone hates
multi-posting.
Hope this helps.
David Portas
SQL Server MVP
|||> I notice you posted independently to at least two other groups.
Sorry. I think you posted twice to the SAME group actually. It was still a
multi-post to this one though.
David Portas
SQL Server MVP
|||To top it off, I gave Tina the same answer as you YESTERDAY in the adonet
group. Go figure.
Greg
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>
|||David,
I posted to one other group - the adonet group. The groups behave
differently so I do that to try to get best answers. The SQL Server group
always replys and very quickly but they are not always ADO oriented - I
ususally get T_SQL type answers. The adonet group doesn't answer so fast or
so well but they are ado oriented.
I didn't know that was against the rules. Sorry.
T
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>