How to get the primary key back after you done a single line insert?

[Bob]

This is the snippet of code
Dim myConnString As String = "Integrated Security=SSPI;Packet Size=4096;Data
Source=MyServer;" & _

"Initial Catalog=MyDatabase; " & _

"Persist Security Info=False;" & _

"Workstation ID=MyWS"

Dim SQL As String = "Insert Into Mytable (MyField) " & _

"Values (" & 1 & ")"

'this will create a single record when executed and that record will have a
field MYPK which is an autogenerated primary key.

'it is that key I need to retrieve immediately after the creation of the new
record.


Dim MyConn As New SqlClient.SqlConnection

MyConn.ConnectionString = myConnString

Dim Mycommand As New SqlClient.SqlCommand

Mycommand.Connection = MyConn

Mycommand.CommandText = SQL

Dim NewPK As Integer

MyConn.Open()

NewPK = Mycommand.ExecuteScalar

The execute scalar was supposed to return (according to docs) the first
column of the first record of the resultset returned by the query. So I get
0 because I suppose an insert does not return a resultset, So question is,
how do I get the newly created Primary key of the newly created record in
such a case.



Thanks for any help.

Bob

 

[Barry]

Bob,

I think your SQL statement should be re-written as

Sim sQL As String = "Set RowCount On " & _
& "Insert Into Mytable (MyField) " & _
& "Values (" & 1 & ") " & _
& "Select Scope_Identity() as 'ReturnPK'" & _
& "Set RowCount Off"

This should return your PK - providing you are using Identity on your
column as a PK?

HTH

Barry

 

[Bob]

Thanks Barry, I will try this

Bob