“Element GENERATEDKEY is undefined…” after ColdFusion SQL Statement

Posted: October 26, 2012 in ColdFusion
Tags: , , , , ,

This is a followup post to “Returning IDENTITY ID via ColdFusion 9′s CFQuery“.

Today I created an Insert statement that I needed the new identity value returned. It’s pretty simple, calling the result.generatedkey as such:

<cfquery result="myResult">
    INSERT INTO users( username )
    VALUES( 'test' )
</cfquery>
<cfreturn myResult["GENERATEDKEY"]>

The above code would normally result in the new identity value created for that record. However I received a “GENERATEDKEY is undefined…” error. Not sure what to think I dumped out the myResult variable, put the query into a file of itself and even tried using myResult.IDENTITY instead to no avail. After taking awhile figuring this out and ended up going home for the day and trying there again after a couple of hours. All of a sudden the standalone test worked!

So I’m thinking, okay this has to be some setting somewhere, and I was right. I narrowed it down to a query that runs before it during the login process for my website.

SET NOCOUNT ON

UPDATE TABLE1
SET COL1 = 'myVal'

Notice that I didn’t set “SET NOCOUNT OFF”. There lies the issue. Apparently when you set NOCOUNT off and keep it off, ColdFusion doesn’t retrieve the new Identity value. So the following code fixed my issue:

SET NOCOUNT ON

UPDATE TABLE1
SET COL1 = 'myVal'

SET NOCOUNT OFF
Advertisements
Comments
  1. OlPeculier says:

    I just use SELECT SCOPE_IDENTITY() AS newid in the query.

  2. Doug S. says:

    If you are using MSSQL 2005 or above, the preferred method is to use an output clause:

    INSERT INTO users( username )
    VALUES( ‘test’ )
    OUTPUT Inserted.YourIDColumnName

    examples here: http://blog.sqlauthority.com/2007/10/01/sql-server-2005-output-clause-example-and-explanation-with-insert-update-delete/

  3. Sam Daams says:

    Chris, you wouldn’t have any idea why it works 99% of the time, but not 1%? I’m going to start dumping and sending results to myself, but I get an error message from time to time that it’s not defined on an “insert .. if duplicate update” query. I thought it was the update causing it, but unfortunately not. Really quite maddening!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s