How do I get the IDENTITY / AUTONUMBER value for the row I inserted?
This will give the most recent IDENTITY value for 'table' - regardless of whether you created it or not (this overrides the connection limitation of @@IDENTITY -- which can be useful).
Another thing you can do is:
This will give the IDENTITY value last created within the current stored procedure, trigger, etc.
If you using a version of SQL Server prior to 2000 (or you are in compatibility mode < 80), the best way is to use a single stored procedure that handles both the INSERT and the IDENTITY retrieval using @@IDENTITY.
Here is sample code for the stored procedure:
And you would call this from ASP as follows:
If you are using SQL Server 7.0, simply change the line in the stored procedure from ...
... to ...
The reason SCOPE_IDENTITY() is preferred over @@IDENTITY is that if you perform an INSERT, and that table has an INSERT TRIGGER which then, in turn, inserts into another table with an IDENTITY column, @@IDENTITY is populated with the second table's IDENTITY value. So, if you are stuck using SQL Server 7.0 and need a workaround to retrieving the @@IDENTITY value because you have a trigger that also inserts into another IDENTITY-bound table, you're in luck. You can add this code to the first line of the trigger, but you will have to update all of your application and stored procedure code to deal with this new SELECT:
With that said, there are also potential cases where SCOPE_IDENTITY() can fail, but I think this possibility is more remote than with @@IDENTITY. Observe this repro, provided by David Portas:
This is because the actual INSERT happened outside of the scope of the caller, so SCOPE_IDENTITY() was not populated there. I have requested that the documentation for SCOPE_IDENTITY() be updated to reflect the above scenario.
So with that new information, here is the technique for obtaining this value using Access:
If you are unable to use JET 4.0, you can do a more risky hack like this:
This is more risky because it is remotely possible for two people to "cross" inserts, and receive the wrong autonumber value back. To be frank, if there is a possibility of two or more people simultaneously adding records, you should already be considering SQL Server (see Article #2182). However, if you're stuck with Access and need more security that this won't happen, you can use a Recordset object with an adOpenKeyset cursor (this is one of those rare scenarios where a Recordset object actually makes more sense than a direct T-SQL statement):
Related ArticlesCan I fix this mm/dd/yyyy <-> dd/mm/yyyy confusion once and for all?
Could I get some help with JOINs?
How can I tell which version of MDAC I'm running?
How do I access MIN, MAX, SUM, COUNT values from SQL statements?
How do I change column order in a table structure?
How do I change the order of columns in a table?
How do I concatenate strings from a column into a single row?
How do I convert columns of values into a single list?
How do I determine if a database exists?
How do I document / compare my SQL Server database(s)?
How do I solve 'ADO Could Not Find The Specified Provider'?
Should I use BETWEEN in my database queries?
Why can't I use the * wildcard in a database search?
Why do I get 'Syntax Error in INSERT INTO Statement' with Access?
Why do I get weird results when using both AND and OR in a query?
Why do some SQL strings have an 'N' prefix?
Why does AbsolutePosition return as -1?
Why doesn't SQL Server allow me to separate DATE and TIME?
Why is Query Analyzer only returning 255 characters?
Why should I avoid NULLs in my database?
How do I deal with an apostrophe (') in a SQL statement?