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?


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

Open in new window

LVL 61
mbizupAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
CodeCruiserConnect With a Mentor Commented:
@@Identity only works if you set the column to be an Identity column (auto increment integer column). You would need to run a select based on the field values that you have just inserted to get the primary key.
0
 
Brian CroweDatabase AdministratorCommented:
You can also use the OUTPUT clause.  I'm not sure how this would work with ADO so you may need to encapsulate the insert logic in a stored procedure and return the resultset.

http://technet.microsoft.com/en-us/library/ms177564.aspx
0
 
mbizupAuthor Commented:
Ah - that makes sense.

I added an identity column and it is working correctly now using @@Identity.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.