Problems retrieving Record Identity after INSERT 2

[JohnBeals]

I need to capture the Identity field after inserting a record. I was told to use the @@IDENTITY (

http://msdn.microsoft.com/library/en-us/tsqlref/ts_globals_50u1.asp)

function. So I have this:

<cfquery name=&quot;InsertRequest&quot; datasource=&quot;#db#&quot;>
INSERT INTO tbl_SchedJobs
(Station, TestProfile, TestCategory, BusinessGroup, RequestorWWID, ProductCode,
NumUnits, TestComments, RequestStartDate, RequestEndDate, Status, Funded)
VALUES
(#Station#, #Profile#, #Category#, #GetCustomerData.GroupCode#, '#WWID#', '#Product#',
#NumUnits#, '#Comments#', '#RequestStartDate#', '#RequestEndDate#', '#Status#', #GetCustomerData.FundingUnit#)
SELECT @@IDENTITY as 'ID'
</cfquery>
<cfset ID = #InsertRequest.ID#>

and I get an error setting the ID:

An error occurred while evaluating the expression:

ID = #InsertRequest.ID#

Error near line 73, column 8.
------------------------------------------------------------
Error resolving parameter INSERTREQUEST.ID

If I run my query in SQL Query Analyzer it returns the ID.

Any ideas on what's wrong?

 

[webmigit]

SELECT @@IDENTITY as Ident

Might work better...

Tony Did I help?
Vote!

 

[JohnBeals]

Nope, using &quot;Ident&quot; instead of &quot;ID&quot; doesn't work either. Nice thought though...

JB

 

[r937]

no idea

maybe there needs to be a semi-colon after the INSERT statement?

i can give you an alternate strategy

an autonumber primary key is actually a surrogate key

the table will have some column or combination of columns not including the autonumber that uniquely identify each row -- i.e. the &quot;real&quot; primary key

so you could always get back the row you just inserted by using a SELECT statement with a WHERE clause that specifies the values of those columns

this should be easy, because after all, you're still holding those values in the variables that you used to supply them to the INSERT statement

this method is not as efficient as @@IDENTITY, but it is guaranteed to work, and in all databases to boot

rudy

http://rudy.ca/

 

[neofactor]

If you are using SQL Server and its Identity column, you can perform the insert and ID retrieval in just one query:


<cfquery datasource=&quot;SQLServerDS&quot; name=&quot;InsertQuery&quot;>
SET NOCOUNT ON
INSERT INTO Table ...
VALUES ...
SELECT ThisID = @@Identity
SET NOCOUNT OFF
</cfquery>
<cfset LastID = InsertQuery.ThisID>



If you are using Access:

<cftransaction>
<cfquery datasource=&quot;AccessDS&quot;>
INSERT INTO Table ...
VALUES ...
</cfquery>
<cfquery datasource=&quot;AccessDS&quot; name=&quot;GetID&quot;>
SELECT MAX(ID_Field) AS ThisID
FROM Table
</cfquery>
<cfset LastID = GetID.ThisID>
</cftransaction>



Or Oracle:


<cftransaction>
<cfquery datasource=&quot;OracleDS&quot; name=&quot;GetID&quot;>
SELECT Table_Sequence.NextVal AS ThisID
FROM Dual
</cfquery>
<cfset LastID = GetID.ThisID>
<cfquery datasource=&quot;OracleDS&quot;>
INSERT INTO Table (ID, ...)
VALUES (#LastID#, ...)
</cfquery>
</cftransaction>


David McIntosh

http://www.neofactor.com

Let me know if this post helped you...
Please click below: &quot;This Post was Helpful&quot;

 

[neofactor]

oh--- you dont need the nocount on/off-

NOCOUNT is set to ON to reduce network traffic during the multiple queries, then set back to OFF when complete. @@Identity contains the value of the last inserted ID.


David McIntosh

http://www.neofactor.com

Let me know if this post helped you...
Please click below: &quot;This Post was Helpful&quot;

 

[SidianConsulting]

This link should clear it all up:

http://www.happyhacker.com/Code.cfm?Cat=CF&SubCat=SQLIns#StartHere

-Tek

 

[JohnBeals]

Set NoCount On and Set NoCount Off seems to be required for @@IDENTITY to work. Without it I get the error stated in my original message. With it the code works.

Thanks to everyone for this. It will help me bulletproof my code even better.

John