Skip to main content
Known Participant
March 30, 2010
Answered

Capturing the ID of an insert query

  • March 30, 2010
  • 1 reply
  • 615 views

I have a bit of code here but the question is fairly basic, if you could bare with me. I need to know the ID of the newly created table row in query "rs_insert_1". I've tried a few things but since this double form upload is happening on a single page i'm not sure how to capture this ID.

<cfif IsDefined('form.file_one') AND Trim(form.file_one) neq "">
        <cffile
            action="upload"
            destination="#dstfile#"
            filefield="file_one"
            nameconflict="overwrite">
      
            <cfset cookie.file_one = "#cffile.serverfile#">
            <cfset cookie.file_one_ext = "#cffile.serverfileext#">
  
        <cfquery name="rs_insert_1" datasource="062409js06ag">
            Insert Into presets
                (pfilename, pfilenameext, artistid, artistname, presetname, presetgenre, bpm, soundslike, daws, vsts, license, instructions)
                Values
                (<cfqueryparam value="#cookie.file_one#" cfsqltype="cf_sql_varchar">
                , <cfqueryparam value="#cookie.file_one_ext#" cfsqltype="cf_sql_varchar">, #findname.ID#, '#findname.uname#', '#requiredname#', '#requiredgenre#', '#requiredbpm#', '#requiredsoundslike#', '#requireddaw#', '#requiredvst#', '#requiredlicense#', '#requiredinstructions#')
        </cfquery>
       
       <cfset cookie.mp3id = "#id#">       <---- this code gives me the id is undefined error
                  
    </cfif>

    <!--- Begin processing second file submission --->
   
    <cfset subdir2=#GetCurrentTemplatePath()#>
    <cfset subdir2='#replace(subdir2,'\upload1.cfm','\presetsmp3')#'>
    <cfset dstfile2='#subdir2#'>
   
   
    <cfif IsDefined('form.file_two') AND Trim(form.file_two) neq "">
        <cffile
            action="upload"
            destination="#dstfile2#"
            filefield="file_two"
            nameconflict="overwrite">
      
            <cfset cookie.file_two = "#cffile.serverfile#">
            <cfset cookie.file_two_ext = "#cffile.serverfileext#">
  
               <cfquery name="rs_insert_2" datasource="062409js06ag">
            update presets set pmp3preview=<cfqueryparam value="#cookie.file_two#" cfsqltype="cf_sql_varchar">, pmp3previewext=<cfqueryparam value="#cookie.file_two_ext#" cfsqltype="cf_sql_varchar"> where id=<cfoutput>#cookie.mp3id#</cfoutput>
               
            </cfquery>
      
    </cfif>

    This topic has been closed for replies.
    Correct answer Dan_Bracuk

    You didn't read the previous answer carefully enough.  That answer only works for the database specified and you are using a different one.

    With Access, you'll have to do a

    select max(id)

    from yourtable

    where as many conditions as you can think of are met.

    1 reply

    March 30, 2010

    You didn't mention what type of database you were using but if you're using SQL Server here's something to look into (or Google for exact usage). HTH

    SET NOCOUNT ON

    Your Query here

    Select @@identity AS MyID

    SET NOCOUNT OFF

    End your query

    <cfset newid = qry_name.myID />

    ag3900683Author
    Known Participant
    March 30, 2010

    Whats NOCOUNT?