inserting records

HLRosenberger
HLRosenberger used Ask the Experts™
on
I'm inserting records into a table.  I want to retrieve the primary key of the inserted record.  How can I do this?  I know I could get the max of the column that is the primary key, but another record could have been written by the time I retrieve it.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
If the primary key is a Identity column you can use

@@SCOPE_IDENTITY variable to get the inserted value.
Commented:
Sorry it is not a global variable its a function use it as this

SCOPE_IDENTITY()
Imran Javed ZiaConsultant Software Engineer - .NET Architect
Commented:
You can use either of following right after insert statemnt

set @id = @@IDENTITY
or
set @id = SCOPE_IDENTITY()
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Do I have to use a stored proc?  Right now I'm doing the insert like this:

Connection = New SqlConnection(ConnectionString)
        Command = New SqlCommand(SQL, Connection)
        Connection.Open()
        Command.ExecuteNonQuery()
        Connection.Close()
Software Engineer
Top Expert 2011
Commented:
Add ;SELECT @@Identity to your sqlstring.

Then Use the result of ExecuteScaler to assign your new id to a variable:
Dim id as Integer
Connection = New SqlConnection(ConnectionString)
        Command = New SqlCommand(SQL, Connection)
        Connection.Open()
        id = Command.ExecuteScaler()
        Connection.Close() 

Open in new window

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial