mbizup
asked on
Get Primary Key of newly inserted record in SQL Server 2008/VB.net
I have code which
- inserts a record into table 1 (this works)
- attempts to grab the primary key value of that record (this does not work)
- inserts a related record into table 2. I am trying to include the primary key from the earlier insert, but the @@IDENTITY returned is null
The field type of the primary key is "uniqueidentifier"
What am I doing wrong? Or is there a better way to do this?
- inserts a record into table 1 (this works)
- attempts to grab the primary key value of that record (this does not work)
- inserts a related record into table 2. I am trying to include the primary key from the earlier insert, but the @@IDENTITY returned is null
The field type of the primary key is "uniqueidentifier"
What am I doing wrong? Or is there a better way to do this?
Dim cn As New SqlConnection
Dim cmd As New SqlCommand
cn = GetConnected()
cmd.Connection = cn
cmd.CommandText = sSQL
cmd.Parameters.Add(New SqlParameter("@FromDate", SqlDbType.DateTime))
' etc - more parameters added
' This works - the record is added with a visible uniqueidentifier
cmd.ExecuteNonQuery()
' This is where I'm trying to grab the Primary Key, but it returns a null....
cmd.Parameters.Clear()
cmd.CommandText = "SELECT @@IDENTITY"
Dim newID As String = "" & cmd.ExecuteScalar()
' ... so all of the record added here have a blank SessionID
sSQL = "INSERT INTO tblScheduleStuff (Scheduler_SessionID, GenericStuff) SELECT '" & newID & "', Stuff FROM tblTEMPStuff"
cmd.CommandText = sSQL
cmd.ExecuteNonQuery()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah - that makes sense.
I added an identity column and it is working correctly now using @@Identity.
I added an identity column and it is working correctly now using @@Identity.
http://technet.microsoft.com/en-us/library/ms177564.aspx