Retrieve the Id of recently inserted record

Question

I currently have this SQL insert code in code behind

Dim con As New SqlConnection
Dim conString As String
conString = ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString
con = New SqlConnection(conString)

con.Open()

Dim cmd As New SqlCommand("INSERT INTO AdditionalDaysRequest(Status, AdditionalDays, Justification,RequestDaySubmitted) VALUES (@Status,@AdditionalDays,@Justification,@RequestDaySubmitted)", con)

cmd.Parameters.AddWithValue("@Status", "Pending Request")
cmd.Parameters.AddWithValue("@AdditionalDays", TB_Days.Text)
cmd.Parameters.AddWithValue("@Justification", TB_Justification.Text)
cmd.Parameters.AddWithValue("@RequestDaySubmitted", Date.Now)

cmd.ExecuteNonQuery()
con.Close()

The Id in this table is automatically generated and incremented

What I would like to have now is the Id of this record inserted to add it to another table

Hi SeRo, What is the primary key of AdditionalDaysRequest table ? — Anura Adhikari, Apr 26 '14 at 07:28
Hi, The PK is the Id I'm trying to get, which is also auto-incremented — SeRo, Apr 26 '14 at 07:30

Steve · Accepted Answer · 2014-04-26T12:53:16.703

Change your query text to add a second statement:

 ...;SELECT SCOPE_IDENTITY();

The SELECT SCOPE_IDENTITY() statement Returns the last identity value inserted into an identity column in the same scope as from the MSDN article above.
In addition, you can use the ability of the Sql engine to understand and process two or more command statements passed as a single string if you separe the statements with a semicolon.
In this way you have the great benefit to execute a single trip to the database.

 Dim cmd As New SqlCommand("INSERT INTO AdditionalDaysRequest(Status, " & _ 
     "AdditionalDays, Justification,RequestDaySubmitted) VALUES " & _
     "(@Status,@AdditionalDays,@Justification,@RequestDaySubmitted);" & _ 
     "SELECT SCOPE_IDENTITY()", con)

 cmd.Parameters.AddWithValue("@Status", "Pending Request")
 cmd.Parameters.AddWithValue("@AdditionalDays", TB_Days.Text)
 cmd.Parameters.AddWithValue("@Justification", TB_Justification.Text)
 cmd.Parameters.AddWithValue("@RequestDaySubmitted", Date.Now)
 Dim result = cmd.ExecuteScalar()
 con.Close()     
 if result IsNot Nothing Then

       Dim lastInsertId = Convert.ToInt32(result)

 End If

Notice that the two statements are now executed using ExecuteScalar instead of ExecuteNonQuery because we want to catch the result of the last command.

Perfect answer mate. Appreciated ! – SeRo Apr 26 '14 at 07:43 — SeRo, Apr 26 '14 at 07:43

score 2 · Answer 2 · answered Apr 26 '14 at 07:32

2

You will want to run a new SqlCommand. Set the value of lastInsertId with this statement:

SELECT SCOPE_IDENTITY()

answered Apr 26 '14 at 07:32

smoore4

4,520
3
36
55

score 0 · Answer 3 · answered Apr 26 '14 at 11:15

This would be an additional knowledge.

we have multiple options like:

@@IDENTITY
SCOPE_IDENTITY
IDENT_CURRENT

All three functions return last-generated identity values. However

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

Retrieve the Id of recently inserted record

3 Answers3