How do I get back a newly created Primary key

  • Thread starter Thread starter rdufour
  • Start date Start date
R

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