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.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

[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 <bd*****@sgiims.comwrote:

ted,

[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

[Robert Dufour]

Thanks to both of you.
Bob
"rdufour" <bd*****@sgiims.comwrote in message
news:un*************@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