Skip to main content
Z
zoz-uplink
Participant
March 15, 2012
Answered

set variable equal to value from a database recordset

  • March 15, 2012
  • 1 reply
  • 783 views

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 --->

    This topic has been closed for replies.
    Correct answer Dan_Bracuk

    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.       

    1 reply

    D
    Dan_BracukCorrect answer
    Inspiring
    March 15, 2012

    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.       

    Z
    zoz-uplinkAuthor
    Participant
    March 15, 2012

    Thanks for the reply, I'll try that.

    I am using Microsoft SQL Server 2008.

    Z
    zoz-uplinkAuthor
    Participant
    March 15, 2012

    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>

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices