getting Identity value back

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

Tina

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 said:
when I do the following, how can I get the value of the primary key back
which is an Identity column?
Click to expand...

strSQL = "INSERT INTO Orders " & _
"(JobID, Description, Notes, Status)" & _
"VALUES (@JobID, @Description, @Notes, @Status);SELECT @ID=SCOPE_IDENTITY()"
....
mycommand.Parameters.Add("@ID", Data.SqlDbType.Int).Direction =
Data.ParameterDirection.Output
....
rowsAffected = mycommand.ExecuteNonQuery()
Dim id As Integer = CInt(cmd.Parameters("@ID").Value)

I usually use stored procedures, so I am not sure if the above SQL is valid
syntax or not.

BTW: You'll see @@IDENITY and SCOPE_IDENTITY() when working with SQL
Server. SCOPE_IDENTITY() is the preferred method. (Check out BOL)

Greg
 
Greg,
SCOPE_IDENTITY is T-SQL. I know how to get Identity back in T-SQL. I want
to get it back in a vb.net program. That's why I posted to this adonet
forum instead of the sql server forum.

The problem with these forums is that when anyone answers a question the
question pretty much gets ingored from that point on so when someone answers
with "I don't know" the question needs to be reposted. I know you are
trying to help but answering with "I don't Know" isn't much use.
 
Answered in microsoft.public.sqlserver.server.

Please don't multi-post.
 
Tina said:
Greg,
SCOPE_IDENTITY is T-SQL. I know how to get Identity back in T-SQL. I
want to get it back in a vb.net program. That's why I posted to this
adonet forum instead of the sql server forum.
Click to expand...

The problem with these forums is that when anyone answers a question the
question pretty much gets ingored from that point on so when someone
answers with "I don't know" the question needs to be reposted. I know you
are trying to help but answering with "I don't Know" isn't much use.
Click to expand...

I agree that is a problem with these forums. But did you ever consider the
answer I gave works and is correct?

I NEVER said I don't know. I said "I usually use stored procedures, so I am
not sure if the above SQL is valid syntax or not."

Turns out it was perfectally valid SQL snytax and my example does work.
(Although you should still be using a stored procedure for this)

Just what in the world do you mean when you say you know how to get the
value back in T-SQL but not in VB.net??? If you are connecting to SQL
Server with VB.net, then all the same rules apply.

Here is an example I just put together that does just that.

Dim connectStringBuilder As New SqlConnectionStringBuilder()
connectStringBuilder.DataSource = ".\SQLEXPRESS"
connectStringBuilder.AttachDBFilename = "C:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf"
connectStringBuilder.IntegratedSecurity = True
connectStringBuilder.UserInstance = True

Using connection As New
SqlConnection(connectStringBuilder.ConnectionString)

connection.Open()

Dim strSQL As String
strSQL = "INSERT INTO Sales.Customer " & _
"(CustomerType, ModifiedDate, rowguid)" & _
"VALUES ('S', GETDATE(), NEWID());SELECT @ID=SCOPE_IDENTITY()"

Dim cmd As New SqlCommand(strSQL, connection)

cmd.Parameters.Add("@ID", Data.SqlDbType.Int).Direction =
Data.ParameterDirection.Output

cmd.ExecuteNonQuery()
Dim id As Integer = CInt(cmd.Parameters("@ID").Value)

End Using

Returns the last CustomerID inserted just as advertised.

Did you even try what I showed you?

Greg
 
Yes, your solution will work. I didn't read it closely enough. It was
actually my evil twin that wrote that reply to you :)
Sorry,
T
 
Back
Top