Retrieve @@IDENTITY on CFQUERY

[Guest]

New to CF ... How can I retrieve @@IDENTITY when using a CFQUERY? I could do it in ASP but iccck, who wants to talk about that anymore.

[Newsgroup_User]

Yes you can, but I dont' so I can't tell you how. I have seen it
discussed many times so I know it is possible.

mikeap wrote:
> New to CF ... How can I retrieve @@IDENTITY when using a CFQUERY? I could do it in ASP but iccck, who wants to talk about that anymore.

[Guest · Accepted solution]

Google: http://www.dcfusion.com/blog/index.cfm/2006/6/30/Retrieve-the-ID-of-the-last-inserted-record

Very to easy understand code. Check it out.

[Newsgroup_User]

this would depend on the database being used...(works in MS SQL, MS
Access, MySQL to name a few)
If you can run more than 1 sql statement in a cfquery tag then you can
run it immediately after the insert (using a ;). If not, wrap 2 cfquery
tags in a transaction and run the @@IDENTITY query second.

SELECT @@IDENTITY AS thelatest

then retrieve it through yourqueryname.thelatest .

If on SQL Server...you might want to look at scope_identity()
HTH
--
Tim Carley
www.recfusion.com
info@NOSPAMINGrecfusion.com

[WolfShade]

Better yet, create the query as a stored procedure and put "SELECT @@IDENTITY AS thelatest" immediately after the insert; this way you won't have to put it inside a transaction in order to know that you've got the latest identity.

^_^

[JR__Bob__Dobbs-qSBHQ2]

Take a look at the CFQUERY documentation.  You can use IDENTITYCOL property of a result to get a new identity value.  Note that IDENTITYCOL is MS SQL Server specific, see the documentation for the properties used for other databases.

CFQUERY

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_17.html#1102316