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

 

[Guest]

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')

 

[Robert Dufour]

Thanks to both of you.
Bob