coldfusion retrieve the index of a record that has just been inserted.

Question

Hello,I would like to retrieve the index of a record that has just been inserted. INSERT INTO Impr_commandes (SiteName, ICImp_ID, ICClient_ID, ICLivre_ID, ICProd_ID, CDate_crea, ICQuantite ) VALUES ('#SiteName#', #Impp#, #get_Clients.Client_ID#, #get_Livre.Livre_ID#, #get_Prod.Prod_ID#, '#DateFormat(now(),"dd/mm/yyyy")#', 1 ) SELECT Scope_Identity() AS [IC_ID]; This buggy!Is this the best method?Thank you in advance for your help.

ZNB · Accepted Answer

INSERT INTO Impr_commandes(SiteName,ICImp_ID,ICClient_ID,ICLivre_ID,ICProd_ID,CDate_crea,ICQuantite)VALUES('#SiteName#',#Impp#,#get_Clients.Client_ID#,#get_Livre.Livre_ID#,#get_Prod.Prod_ID#,'#DateFormat(now(),"dd/mm/yyyy")#',1)In your query, above, I added result="queryResult" which is where CF returns the information about the query. You would then use one of the below values to get your new id column value, the one you use depends on which databsae you are using. This is all in the CFQuery documentation. queryResult.identitycol // MSSQLqueryResult.rowid // OraclequeryResult.sys_identity // SybasequeryResult.serial_col // InformixqueryResult.generated_key // MySQLHello,Sorry but I couldn't test before ;-(The method is good and easy to implement.I do not know.thanks again

WolfShade · Answer

Another thing I just thought of. You say you'd like to retrieve the index of a record just inserted. Do you actually mean a unique ID? We use a varchar with a max length of 36 for unique IDs, and have ColdFusion create a UUID and insert that as the unique ID for the record. That way, we don't need to retrieve it. CF created it, we just use that elsewhere.

HTH,

^ _ ^

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.