# Visual Basic > Visual Basic FAQs >  Database - How can I get the AutoNumber/Identity value for the record just added?

## si_the_geek

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!

----------

