getting Identity value back

11 Nov 2005 6:48 PM

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

11 Nov 2005 8:22 PM

Greg Burns

"Tina" <tinamseaburn@nospammeexcite.com> wrote in message
news:%23%23lM%23Bv5FHA.252@TK2MSFTNGP15.phx.gbl...
> when I do the following, how can I get the value of the primary key back
> which is an Identity column?

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

12 Nov 2005 5:08 PM

Tina

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.

Show quote

"Greg Burns" <bluebunny@newsgroups.nospam> wrote in message
news:%238oGn2v5FHA.3416@TK2MSFTNGP15.phx.gbl...
> "Tina" <tinamseaburn@nospammeexcite.com> wrote in message
> news:%23%23lM%23Bv5FHA.252@TK2MSFTNGP15.phx.gbl...
>> when I do the following, how can I get the value of the primary key back
>> which is an Identity column?
>
> 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
>

12 Nov 2005 7:02 PM

REMOVE_BEFORE_REPLYING_dportas

Answered in microsoft.public.sqlserver.server.

Please don't multi-post.

--
David Portas
SQL Server MVP
--

12 Nov 2005 7:30 PM

Greg Burns

"Tina" <tinamseaburn@nospammeexcite.com> wrote in message
news:%23Pf69u65FHA.1864@TK2MSFTNGP12.phx.gbl...
> 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.

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

12 Nov 2005 10:51 PM

Tina

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

Show quote

"Greg Burns" <bluebunny@newsgroups.nospam> wrote in message
news:OqGWK%2375FHA.3592@TK2MSFTNGP12.phx.gbl...
> "Tina" <tinamseaburn@nospammeexcite.com> wrote in message
> news:%23Pf69u65FHA.1864@TK2MSFTNGP12.phx.gbl...
>> 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.
>
> <speachless>
>
>> 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.
>
> 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
>
>