How do I get back a newly created Primary key

[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.SqlCo nnection
MyConn.Connecti onString = g_SQLConnStr
Dim Mycommand As New SqlClient.SqlCo mmand
Try
Dim NewRecordPK As Integer
Mycommand.Comma ndText = "Insert into MyTable (Data1,Data2) " & _
"VALUES ('Data1Value',' Data2Value')
Mycommand.Conne ction = MyConn
MyConn.Open()
NewRecordPK = Mycommand.Execu teScalar()

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

[Tiago Salgado]

Re: How do I get back a newly created Primary key

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 <bdufour@sgiims .comwrote:

ted,

[Tim Patrick]

RE: How do I get back a newly created Primary key

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

[Robert Dufour]

Re: How do I get back a newly created Primary key

Thanks to both of you.
Bob
"rdufour" <bdufour@sgiims .comwrote in message
news:unA6MjU9GH A.568@TK2MSFTNG P05.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.SqlCo nnection
MyConn.Connecti onString = g_SQLConnStr
Dim Mycommand As New SqlClient.SqlCo mmand
Try
Dim NewRecordPK As Integer
Mycommand.Comma ndText = "Insert into MyTable (Data1,Data2) " &
_
"VALUES ('Data1Value',' Data2Value')
Mycommand.Conne ction = MyConn
MyConn.Open()
NewRecordPK = Mycommand.Execu teScalar()
>
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
>