This post highlights an old method to retrieve last record. Coldfusion has changed a lot since then. Keep searching.
If you are a developer, there is no doubt that you have the need to retrieve the last record of a database insert without requiring another input from the end user. You want your user experience to appear as fluid as possible and an unnecessary multiple page submit does not accomplish that.
If you have a record that you will be inserting and need the ID of that just inserted record, your code will depend upon the database that you use. I will cover two of the most common DB’s used, MSSQL and MySQL.
Retrieve last record inserted (ID) if using Microsoft SQL Server (MSSQL):
I won’t go deep into the how and why, what I am looking to do is provide a basis for you to get over this little stumbling block. When using MSSQL, you can accomplish the task within your record insert query. I am assuming that you know the basics about inserting data using CF Query. I am also using <cfqueryparam> to protect my queries from hacks and to require particular datatypes. This has nothing to do with the key retrieval, it is just good practice.
The important parts of the query are the “SET NOCOUNT ON” at the top of the query and the “SELECT myID= @@identity SET NOCOUNT OFF” at the bottom of the query. The key that you just inserted is what should be referenced where I label it “myID”. For example, if you are inserting into an employees table and you key your employees with employeeID, you would use employeeID in place of myID.
Code example:
<cfquery datasource=”yourDSN” name=”qName”>
SET NOCOUNT ON
INSERT INTO tableName (otherData)
VALUES (<cfqueryparam value=”#otherDataValue#”>)
SELECT myID= @@identity
SET NOCOUNT OFF
</cfquery>
You can then reference the value as #myID# as if you had just done a query calling for the last record.
Easy enough?
————————————————————————————–
Retrieve last record inserted using MySQL:
MySQL is handled a bit differently. There may be a more simple solution for this insert, but for my needs (and most others) the 2 query solution listed below works well. You might want to consider a <cftransaction> block on a higher volume site.
<cftransaction>
<cfquery datasource=”yourDSN” name=”qName”>
INSERT INTO tableName (otherData)
VALUES (<cfqueryparam value=”#otherDataValue#”>)
</cfquery>
<cfquery datasource=”yourDSN” name=”getMyID”>
SELECT LAST_INSERT_ID() AS myID
</cfquery>
</cftransaction>
Reference the value as #getMyID.myID#
I am sure this get last record code will help someone. If you have a better solution (isn’t there always one), do share.
If you’re on coldfusion 8, use the result attribute of cfquery.
In the result struct you will find a structkey called IDENTITYCOL. This is the ID of the last inserted record. Note : the variables name with the last inserted ID varies depending on your database type.
Take a look at :
http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_17.html#1102316
Stephen,
Thank you very much for the contribution. This will certainly be helpful for those on Coldfusion 8.