Highlighted

How to return generatedkey?

New Here ,
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.

 

Adobe Community Professional
Correct answer by Charlie Arehart | Adobe Community Professional

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. 

Views

64

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

How to return generatedkey?

New Here ,
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.

 

Adobe Community Professional
Correct answer by Charlie Arehart | Adobe Community Professional

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. 

Views

65

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
Sep 15, 2020 0
Explorer ,
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
Reply
Loading...
Sep 15, 2020 0
Participant ,
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
Reply
Loading...
Sep 15, 2020 0
Adobe Community Professional ,
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
Reply
Loading...
Sep 15, 2020 0
New Here ,
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
Reply
Loading...
Sep 15, 2020 0
Adobe Community Professional ,
Sep 16, 2020

Copy link to clipboard

Copied

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
Reply
Loading...
Sep 16, 2020 0