@@identity

[NoWayIsThisInUse]

How do I pass the identity variable back to the page?
<cfquery name="x"....>
insert blah blah

select @@identity AS 'Whatever'
</cfquery>

If I say:
<cfset y = #x.whatever#>
it's undefined.

 

[GUJUm0deL]

That should work. Make sure that the INSERT statement actually inserts the data into the dB to get the @@identity to exist.



[sub]
____________________________________
Just Imagine.

http://www.imajinarts.com

[sub]

 

[ecobb]

Just a note, for SQL Server you should use SCOPE_IDENTITY() instead of @@identity. It will pull the identity of the actual newly inserted recored whereas @@identity will pull the identity of the last record used in this procedure. Normally, it won't matter, but if there's a trigger on that table then @@identity will return the trigger's identity and not the inserted record's.



Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[imstillatwork]

another note, use LAST_INSERT_id() for MySQL

 

[NoWayIsThisInUse]

I found some other code that has a couple of subtle differences. The variable in the "AS" does not have single quotes and then after the query it sets the variable and doesn't use it directly. Hope this helps someone else.

<CFQUERY name="SetupGroup" datasource="#ds#">
SET NOCOUNT ON
INSERT INTO Groups
( [ClientKey], [Name])
VALUES
( #ClientKey#, 'Client Site Access' )

SELECT @@Identity AS GroupKey
</CFQUERY>
<cfset GroupKey = SetupGroup.GroupKey>