set variable equal to value from a database recordset

[zoz-uplink]

This is for Coldfusion version 8.

I have two tables which I am inserting data into. After I insert a record into the first table, I need to use the id (auto incremented, not something I already have) of the new record as part of the data i input into the second table.

I'm having trouble nailing down the syntax to get me there. I've searched the documentation, any thoughts on what topic I should have been looking under would be welcome as well.

Any help would be appreciated, thanks.

-----------------------------------

<!--- This table has an id column which autoincrements --->

<cfquery datasource="labdsn" name="newLabDataRecord">INSERT INTO LabData (savedLab, lastSavedBy ) VALUES ('Data data data','555' )</cfquery>

<!--- find the value of that new id --->

<cfquery datasource="labdsn" name="maxLabIDRecord">Select max(id) FROM LabData</cfquery>

<!--- This generates the error. Not surprised, but not sure what it should be --->

<cfset maxID= <cfoutput query = "rsMaxLabIDRecord">#rsMaxLabIDRecord.id#</cfoutput>  >

<cfquery datasource="labdsn" name="newUserRecord">INSERT INTO UsersVsLab (userID, whichLab,LabID ) VALUES ('2',1 , <cfqueryparam value="#maxID#"> )</cfquery>

<!--- This was another syntax I tried --->

<!--- cfquery datasource="labdsn" name="newUserRecord">INSERT INTO UsersVsLab (userID, whichLab,LabID )

VALUES

('2',1 , <cfqueryparam value=<cfoutput query = "rsMaxLabIDRecord">#rsMaxLabIDRecord.id#</cfoutput>   > )</cfquery --->

[Dan_Bracuk · Accepted solution]

You can solve your specific error by adding an alias to the query where you get the max id.  However, there are often better ways to accomplish this.  The specifics depend on the type of db you are using.

If you must use select max(id), add a where clause to ensure you get the correct one.       

[zoz-uplink]

Thanks for the reply, I'll try that.

I am using Microsoft SQL Server 2008.

[zoz-uplink]

Thanks, that worked.

For any others that may find it helpful, the revised code is below:

--------------

<cfquery datasource="labdsn" name="newLabDataRecord">INSERT INTO LabData (savedLab, lastSavedBy ) VALUES ('Data data data','555' )</cfquery>

<cfquery datasource="labdsn" name="maxLabIDRecord">Select max(id) as maximumID FROM LabData</cfquery>

<cfquery datasource="labdsn" name="newUserRecord">INSERT INTO UsersVsLab (userID, whichLab,LabID ) VALUES ('2',1 , <cfqueryparam value="#maxLabIDRecord.maximumID#"> )</cfquery>