Database - How can I get the AutoNumber/Identity value for the record just added?

When you add a new record to a table with an AutoNumber/Identity field, you often want to find what the auto-generated value of that field was, so that you can use it in the next part of your code.. but how do you find it?

There are three main ways.. the first only applies to a recordset (or similar) object, and the other two can be used in any situation.

Method 1 - read it from the recordset (or similar) object
After you have used the .AddNew/.Update method on an recordset, the current record will still be the record you added, but it will also contain any changes that the database system made to the data (such as setting the AutoNumber/Identity value), so you can simply read the value like this:

vb Code:

Dim NewID as Long  'this will contain the number
  With objRS  
    .AddNew
    .Fields("MyNumberField").Value = 3
    .Fields("MyTextField").Value = "hello"
    .Update
    NewID = .Fields("MyAutoNumberField").Value
  End With

Note that while this example is using ADO code, the same idea is likely to apply to whatever technology you use.

This method does the least amount of work, so it is best to use this method (or similar) if it works for you.


Method 2 - ask the database for the last created number
Many database systems allow you to specifically ask for the last number that was generated. The syntax for this varies by system, but generally it is a simple Select query.

For Access you can use "SELECT @@identity", and this will return a recordset with a single field that contains the last value that was created for you.

For SQL Server you could use "SELECT @@identity", but this is not safe - as it returns the last value that was created in the database by any user. Instead you should use "SELECT Scope_Identity()", which returns the last value that was created for you.

For MySQL you can use "SELECT LAST_INSERT_ID()"

Here is a ADO example for Access (for others use the same method, but change the SQL as apt):

vb Code:

Dim NewID as Long  'this will contain the number
  objRS.Open "SELECT @@Identity", objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
  NewID = objRS.Fields(0).Value  '0 specifies that we want the first field
  objRS.Close
  Set objRS = Nothing

(you can find a ADO.Net example here)

This method is the "second best" option.. it does more work than Method 1, but not by much (as the database system has 'remembered' the latest value).


Method 3 - ask the database for the record you added, and read the number from it
This isn't a very safe method, and is the slowest too.

The idea is to use an SQL statement to return the record that you added (by using a Where clause with values for every field), for the same fields as the example from method 1 this could be:
"SELECT MyAutoNumberField FROM table1 WHERE MyNumberField = 3 AND MyTextField = 'hello' "

This is not reliable tho... if two or more records have the same values for the fields you specified, which record are you going to read the value from? :eek:
(you could add the Max function to the SQL statement too, but you may still find a record that somebody else has added!).

Another issue is that by doing this, the entire table needs to be searched (which is slow). The more data there is in your table, the worse this is!