How do I get back a newly created Primary key

21 Oct 2006 7:39 PM

rdufour

Using Vs2005. In this snippet of code I would be creating a new record in a
sql server 2000 database table. How do I obtain the value of primary key -
field name PK - for the newly created record. The field PK is an identity
field that automatically increments its value.

         Dim MyConn As New SqlClient.SqlConnection
        MyConn.ConnectionString = g_SQLConnStr
        Dim Mycommand As New SqlClient.SqlCommand
        Try
            Dim NewRecordPK As Integer
            Mycommand.CommandText = "Insert into MyTable (Data1,Data2) " & _
                        "VALUES ('Data1Value','Data2Value')
            Mycommand.Connection = MyConn
            MyConn.Open()
            NewRecordPK = Mycommand.ExecuteScalar()

etc....

I thought that ExecuteScalar would return the value of the first field of
the affected record but it always returns 0, yet I can see in the table that
the record has been added OK with a PK value other than 0, ie it increments
correctly.

Any help would be greatly appreciated,
Bob

21 Oct 2006 8:01 PM

Tiago Salgado

Try to add at your SQL query, a Return @@IDENTITY ...

--
Tiago Salgado // http://weblogs.pontonetpt.com/tiagosalgado

On Sat, 21 Oct 2006 20:39:42 +0100, rdufour <bduf***@sgiims.com> wrote:

Show quoteHide quote

> ted,

21 Oct 2006 8:03 PM

Tim Patrick

If you are using SQL Server 2005, you can include the OUTPUT clause in your
SQL statement. Assuming that the name of your primary key field is PK, use
this statement.

INSERT INTO MyTable (Data1,Data2)
OUTPUT INSERTED.PK
VALUES ('Data1Value','Data2Value')

-----
Tim Patrick
Start-to-Finish Visual Basic 2005

22 Oct 2006 3:00 PM

Robert Dufour

Thanks to both of you.
Bob
Show quoteHide quote

"rdufour" <bduf***@sgiims.com> wrote in message
news:unA6MjU9GHA.568@TK2MSFTNGP05.phx.gbl...
> Using Vs2005. In this snippet of code I would be creating a new record in
> a sql server 2000 database table. How do I obtain the value of primary
> key - field name PK - for the newly created record. The field PK is an
> identity field that automatically increments its value.
>
>         Dim MyConn As New SqlClient.SqlConnection
>        MyConn.ConnectionString = g_SQLConnStr
>        Dim Mycommand As New SqlClient.SqlCommand
>        Try
>            Dim NewRecordPK As Integer
>            Mycommand.CommandText = "Insert into MyTable (Data1,Data2) " &
> _
>                        "VALUES ('Data1Value','Data2Value')
>            Mycommand.Connection = MyConn
>            MyConn.Open()
>            NewRecordPK = Mycommand.ExecuteScalar()
>
> etc....
>
> I thought that ExecuteScalar would return the value of the first field of
> the affected record but it always returns 0, yet I can see in the table
> that the record has been added OK with a PK value other than 0, ie it
> increments correctly.
>
> Any help would be greatly appreciated,
> Bob
>