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

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

Thanks Barry, I will try this

Bob
Show quoteHide quote

VB.NET events creates a hidden delegate, but how do I access it?
Console app no accessible 'Main' method with an app...
CurrencyManager Question
Overnight processes
why are there still COM controls in VS.Net 2005?
Convert VBScript to Visual Basic .Net
LockWindowUpdate VB 2005
datagridview cell - get row/column info for updating data- how?
Converting IP Address as string to MyIP as IPAddress
type 'pageClass' is not defined