• Home
• Tutorials
• Forums
• Reference
• Developers
• Advertise
• About Us

Obtaining the ID of the record you just inserted

A common question I am asked is: “How do you obtain primary key (auto increment integer) of the record I just inserted?”.

The most common solution I have found people use is to insert the data, then attempt to query the table filtering by the data you just inserted. This actually works, most of the time on low traffic sites/applications. Want a better, more reliable way? This is a quick stab at how to retrieve the ID of the record you just inserted into a table. I will only cover MS Access, MSSQL and MySQL.

MS Access:
The problem with MS Access is that it does not have any built in functionality to obtain the last id. So, in order to obtain it with the highest level of confidence we’ll want to wrap the query in a <cftransaction> block.

<cftransaction> </cftransaction>

This will ensure that whatever is in this block will be executed in the same scope. This may slow things down a little, but is well worth it.

Now, inside the <cftransaction> block we’ll add our insert query:

<cftransaction>     <cfquery name=“insertRecord“ datasource=“myAccessDB“>         INSERT INTO tblComments (author,title,message)         VALUES (‘John Doe’,’I really like this tutorial’,’This is the best one yet!’)     </cfquery> </cftransaction>

Okay, easy enough. Now I want to return the user to a confirmation page with the book they just entered. The problem is, how do I know what book was just entered? Well, as I stated above, the most common solution I’ve seen is to query the table for the last record filtering on data we just entered. This approach leaves you wide open for errors if someone enters the same or similar enough data.

However, in our case we wrapped the transaction in a <cftransaction> block. That means we can ensure that subsequent queries inside this block execute in the same scope before any other database requests from other sessions. It locks the table for that brief moment and queues up any transactions that were submitted after this one. So, how do we get the ID? Like this:

<cftransaction>     <cfquery name=“insertRecord“ datasource=“AccessDB“>         INSERT INTO tblComments (author,title,message)         VALUES (‘John Doe’,’I really like this tutorial’,’This is the best one yet!’)     </cfquery>     <cfquery name=“getNewID“ datasource=“myAccessDB“>         SELECT MAX(id) as newID         FROM tblComments     </cfquery>     <cfset newID = getNewID.newID> </cftransaction>

There it is. Now we just pass back #newID# and we can identify the record we just inserted.

MS SQL:
MS SQL is even better at handling inserts and returning auto generated IDs. In fact, there are several functions that make this possible. I choose to use Scope_Identity (). This returns the ID of the last inserted record in the scope or session. So to use it, do the following:

<cfquery name=“insertRecord“ datasource=“MSSQLDB“>     INSERT INTO tblComments (author,title,message)     VALUES (‘John Doe’,’I really like this tutorial’,’This is the best one yet!’);     SELECT Scope_Identity () as newID </cfquery> <cfset newID = insertRecord.newID>

Even easier right? Now the local variable newID contains the ID of the record you just inserted.

MySQL:
As with MSSQL, MySQL has a built in function to retain the last inserted id. This function is called: LAST_INSERT_ID(). Unfortunately the JDBC drivers for MySQL prohibit the use of multiple queries in a single database transaction. So, therefore we use <cftransaction>:

<cftransaction>     <cfquery name=“insertRecord“ datasource=“MySQLDB“>         Insert Into tblComments (author,title,message)         Values (‘John Doe’,’I really like this tutorial’,’This is the best one yet!’)     </cfquery>     <cfquery name=“getNewID“ datasource=“MySQLDB“>         Select LAST_INSERT_ID() as newID     </cfquery>     <cfset newID = getNewID.newID> </cftransaction>