How to return generatedkey?

New Here ,
Sep 15, 2020 Sep 15, 2020

Copy link to clipboard

Copied

ColdFusion 2018

 

Hope someone can help me figure out why GENERATEDKEY is not returning with the following query?

 

<cfset dte = CreateDateTime(Year(Now()), Month(Now()), Day(Now()), hrs, mins, secs)>

 

<cfquery name="A" datasource="#dns#" result="A">
INSERT INTO Table 
(CustID,X1,X2,X3,SessionID)
VALUES
(#session.ID#,#X1#,#X2#,#X3#,'#session.sID#')
</cfquery>

<cfset session.recno=#A.generatedkey#>
<script language="JavaScript" type="text/javascript">
document.location.href='file.cfm'
</script>

 

The error that is returning is: 

Error Occurred While Processing Request
Element GENERATEDKEY is undefined in A.

 

I have tried a lot of things including updating the JDBC driver.

This used to work on an older version of CF.

Thanks.

 

Views

254

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 2 Correct Answers

Adobe Community Professional , Sep 15, 2020 Sep 15, 2020
Adding to the helpful comment so far, I see a real problem: you are using the same variable name for the cfquery NAME and RESULT attributes. That's not right (and I'm surprised CF did not report an error). I suspect that somehow it is making the name attribute take precedence (since that has the actual query results). Besides changing the result to a new name (and then changing your use of it to refer to the autogenerated key), you may want to do a cfdump now of that "a" struct, to see if it l...

Likes

Translate

Translate
Adobe Community Professional , Sep 16, 2020 Sep 16, 2020
Glad to have helped. And it is indeed odd if it worked in cf 10. Again, it should really be an error in either. If you have a spare minute, you could help others by filing a quick bug report at tracker.adobe.com, reporting both the change in behavior and how it should seemingly report an error. 

Likes

Translate

Translate
Explorer ,
Sep 15, 2020 Sep 15, 2020

Copy link to clipboard

Copied

2 quick questions 

1) what version of Cold fusion are you using

2) what is your database, Microsoft SQL server, access, MySQL?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Sep 15, 2020 Sep 15, 2020

Copy link to clipboard

Copied

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Sep 15, 2020 Sep 15, 2020

Copy link to clipboard

Copied

Adding to the helpful comment so far, I see a real problem: you are using the same variable name for the cfquery NAME and RESULT attributes. That's not right (and I'm surprised CF did not report an error). I suspect that somehow it is making the name attribute take precedence (since that has the actual query results).

 

Besides changing the result to a new name (and then changing your use of it to refer to the autogenerated key), you may want to do a cfdump now of that "a" struct, to see if it looks like a dump of the query (as it wuold be if it was the "name") or the result struct (in which case you may different variable names or the auto-generated key, depending on the DB driver).

 

Note as well that this feature is controlled by a setting for each DSN in the CF Admin (under its "advanced settings"), called "Disable Autogenerated Keys", so of course make sure that is not checked. 🙂 And FWIW, that is offered regardless of whether the DSN is using CF's built-in JDBC drivers or an "other" one.


/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 15, 2020 Sep 15, 2020

Copy link to clipboard

Copied

Thank you!

 

So changing too:

 

<cfset dte = CreateDateTime(Year(Now()), Month(Now()), Day(Now()), hrs, mins, secs)>

 

<cfquery name="A" datasource="#dns#" result="B">
INSERT INTO Table 
(CustID,X1,X2,X3,SessionID)
VALUES
(#session.ID#,#X1#,#X2#,#X3#,'#session.sID#')
</cfquery>

<cfset session.recno=#B.generatedkey#>
<script language="JavaScript" type="text/javascript">
document.location.href='file.cfm'
</script>

 

This worked.

 

Strange how it was working on CF10 with MySQL 5.1 but not with CF2018 with MySQL 5.7

 

Thanks anyway.

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Sep 16, 2020 Sep 16, 2020

Copy link to clipboard

Copied

LATEST

Glad to have helped. And it is indeed odd if it worked in cf 10. Again, it should really be an error in either. If you have a spare minute, you could help others by filing a quick bug report at tracker.adobe.com, reporting both the change in behavior and how it should seemingly report an error. 


/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines