?
Solved

Get Primary Key of newly inserted record in SQL Server 2008/VB.net

Posted on 2014-02-20
3
Medium Priority
?
1,847 Views
Last Modified: 2014-02-20
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

0
Comment
Question by:mbizup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 39873979
@@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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39874082
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
 
LVL 61

Author Comment

by:mbizup
ID: 39874107
Ah - that makes sense.

I added an identity column and it is working correctly now using @@Identity.
0

Featured Post

Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question