How do you get the ID for a newly inserted SQL server row?

[emaduddeen]

Hi Everyone,

I have a form with multiple tabs that allows the user to insert data into a customer table. The ID is an Identity column.

After a row is inserted into the database I would like to retrieve the ID number for that row that was just inserted into the database. Can you tell me the best way to do this?

When the user clicks on a Relatives tab the application inserts the customer to the database. If I can get the the newly inserted customer ID then I can use it for the CustomerID column of the Relatives table when inserting a Relative so the 2 tables are properly related.

At present the program uses this code to do just that but this code will only be good for a single user system. It's possible that we may sell it as a multi-user system and I think the current way we are doing this will fail because there is not telling whose ID will be returned in that scenario.

Here's the query that we are using:

SqlStatement =
                "SELECT Max(ID) " & _
                  "FROM Customers"

Thanks.

Truly,
Emad

[jlego]

SELECT LAST_INSERT_ID() as Last_Value

[alc6379]

Why aren't you setting the Primary key for the row and letting SQL server handle that? You should write a stored procedure to write the data, and if you need to get back the ID of the row that you just inserted, you can return the value of the SCOPE_IDENTITY() command.

[emaduddeen]

Hi Everyone,

Thanks very much for the help and code examples.

I will try both of your solutions to see how to do it.

Truly,
Emad

[emaduddeen]

Hi Everyone,

I tried SELECT LAST_INSERT_ID() as Last_Value but got an "Unrecognized function" error.

I also tried SCOPE_IDENTITY() in the query and it return null.

Currently I don't know how to do SQL Server stored procedures yet so I was wondering if another function will work.

I'm using SQL Server Express if that has anything to do with the results.

Truly,
Emad

[Unhnd_Exception]

You should be able to use @@Identity after executing the insert command.

Set the command text to @@Identity and execute its Scalar

Command.CommandText = "Select @@Identity"
    Dim WaypointID as Integer = Command.ExecuteScalar

[jlego]

i apologize, the command i gave you was for mySQL not sql server express..

[emaduddeen]

Hi,

Thanks for the reply.

I will try Select @@Identity.

Quick question.

If I use Select @@Identity
is it possible for another user to mess up the results returned if they insert a new row before the query is executed?

Truly,
Emad

Hi jlego,

Don't worry. Maybe I will also need to use MySQL in the future.

Truly,
Emad

[Pgmer]

Yes @@identity will give you the the New id after insert

[emaduddeen]

Hi,

It returns DBNull in the error message.

Here is how I used it please let me know if the syntax is wrong:

' This this now because we need a customer ID for the relatives.
            ' NOTE THAT IS WILL ONLY WORK IF WE ARE NOT DOING THIS WITH A MULTI-USER APP.
            '----------------------------------------------------------------------------
            SaveCustomerToDatabase()

            ' Create the connection object.
            '------------------------------
            objConnection = New SqlConnection(FormMain.strDatabaseConnection)

            SqlStatement =
                "SELECT @@Identity " & _
                  "FROM Customers"

            ' Set up command to execute.
            '---------------------------
            ObjCommand = New SqlCommand(SqlStatement, objConnection)

            ' Tell the command what to execute.
            '----------------------------------
            ObjCommand.CommandText = SqlStatement

            Try

                objConnection.Open()

                ' Get the ID that was just added.
                '--------------------------------
                strStringFromSearch = ObjCommand.ExecuteScalar

            Catch exSqlErrors As SqlException

                MessageBox.Show("Sorry, I can't get a customer ID number " & _
                                "because of this SQL error:" & vbCrLf & vbCrLf & exSqlErrors.Message, _
                                "Other Error")

            Catch exErrors As Exception
                MessageBox.Show("Sorry, I can't get a customer ID number " & _
                                "because of this error:" & vbCrLf & vbCrLf & exErrors.Message, _
                                "Other Error")
            Finally
                objConnection.Close()

                ' Changing the mode now will stop extra copies of the customer from being inserted.
                '----------------------------------------------------------------------------------
                strFormMode = "Update"
                headerCustomerDetails.Text = "Edit Customer"

            End Try

[Unhnd_Exception]

@@Identity and the other Identity Stored Procedure is ONLY going to return the Last Identity if you use an insert statement in the same session.

You could make your SaveCustomerToDatabase sub a function and return the ID.

To Do So:

In your SaveCustomer sub I'm sure you have a command that executes an Insert Statement.

After executing the insert Statement change the command text to @@Identity and the Id will be returned.

Command.ExecuteNonQuery 'The Insert Command in the SaveCustomer Sub.

'Right After Inserting Change the Command Text to the @@Identity.

Command.Parameters.Clear 'If you had parameters

Command.CommandText = "Select @@Identity"  
Dim WaypointID as Integer = Command.ExecuteScalar 'The ID will be returned.

I use WaypointID for an example because it comes straight from a function that does what your talking of. The Function Inserts a Waypoint then uses the @@Identity procedure and returns the id of the newly inserted waypoint.

[emaduddeen]

Hi,

Thanks for all the help. It worked.

Here is the final code I used.

' Execute the SQL statement.
            '---------------------------
            ObjConnection.Open()

            ObjCommand.ExecuteNonQuery()

            If strFormMode = "Insert" Then

                ' Get the ID that was just added.
                '--------------------------------
                SqlStatement = "SELECT @@Identity "

                ObjCommand.Parameters.Clear()

                ObjCommand.CommandText = SqlStatement

                strStringFromSearch = ObjCommand.ExecuteScalar

            End If

            blnValueToReturn = True

Will be work for a multi user program?

Truly,
Emad

[gr8fasushi]

woops, already solved