Skip to main content
A
Anonymous
August 8, 2006
Answered

Retrieve @@IDENTITY on CFQUERY

  • August 8, 2006
  • 3 replies
  • 1838 views
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.
This topic has been closed for replies.
Correct answer
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.

3 replies

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
August 7, 2009

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

N
Newsgroup_User
Inspiring
August 8, 2006
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
WolfShade
Legend
August 1, 2009

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.

^_^

N
Newsgroup_User
Inspiring
August 8, 2006
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.
A
Anonymous
Correct answer
August 8, 2006
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.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices