Member Avatar for 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

Edited by emaduddeen because: n/a
  • 6 Contributors
  • 12 Replies
  • 6K Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by gr8fasushi

Dani AI

Generated

Selecting MAX(ID) is not concurrency-safe. In SQL Server, the most reliable way to get the exact identity value generated by your insert is to return it from the same statement using the OUTPUT clause. This works in SQL Server Express and is immune to other users inserting rows at the same time and to trigger side-effects.

Example T-SQL pattern:

INSERT INTO dbo.Customers (FirstName, LastName, ...)
OUTPUT inserted.CustomerID
VALUES (@FirstName, @LastName, ...);

From ADO.NET, send that as a single command and call ExecuteScalar() to get the new key. Keep the insert and the read in the same command/connection (and transaction if you are doing multiple related writes). Do not open a new connection and then try to fetch an identity value afterward; connection pooling and concurrent users can lead to wrong results. Also avoid IDENT_CURRENT('Customers') as it returns the last identity for the table regardless of session.

The OUTPUT clause returns only the rows affected by your DML, making it safe for multi-user apps. See the official docs for details in OUTPUT Clause (Transact-SQL) and the comparison of identity-returning functions in @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT. For calling patterns, ExecuteScalar is documented at SqlCommand.ExecuteScalar Method.

Recommended Answers

All 12 Replies

Member Avatar for jlego

SELECT LAST_INSERT_ID() as Last_Value

Member Avatar for 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.

Member Avatar for 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

Member Avatar for 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

Member Avatar for Member #857553

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
Edited by Member #857553 because: n/a
Member Avatar for jlego

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

Member Avatar for 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

Member Avatar for Pgmer

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

Member Avatar for 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
Member Avatar for Member #857553

@@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.

Member Avatar for 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

Member Avatar for gr8fasushi

woops, already solved

Edited by gr8fasushi because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.