How to return generatedkey?

[Luke5C33]

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.

[BeRadB]

2 quick questions 

1) what version of Cold fusion are you using

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

[John123]

The thread here https://community.adobe.com/t5/coldfusion/coldfusion-retrieve-the-index-of-a-record-that-has-just-be... has useful information for this.

[Charlie Arehart · Accepted solution]

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.

[Luke5C33]

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.

[Charlie Arehart · Accepted solution]

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.