Missing GENERATEDKEY from Query Result

user_3459786 0
Viewed 453

I have some code which was using GENERATEDKEY from result attribute of cfquery tag from last few years. I recently noticed that it is no more the part of structure returned by Result.

<cfquery name="qryTest" datasource="#DSN#" result="sResult">
  INSERT INTO users(fName, lName, City) VALUES(
  'Test1', 'Test2', 'Test3')
</cfquery>
<cfdump var="#sResult#">

I only get CACHED, EXECUTIONTIME, RECORDCOUNT & SQL. Environment is CF2016, SQL Server 2014

coldfusion-2016 coldfusion
2 Answers

Are you trying to do something like this?

<cfquery name="qryTest" datasource="#DSN#">
  INSERT 
  INTO users(fName, lName, City) 
  OUTPUT inserted.id 
  VALUES('Test1', 'Test2', 'Test3')
</cfquery>
user_18458690

I just go through you issue. I believe that you table ( users ) does not have any Constraints - PK ( Primary Key ) and IDENTITYCOL ( Auto increment ) options. So that it's not return any IDENTITYCOL & GENERATEDKEY keys in your result structure. Here,

 <cfquery name="InsertData1" datasource="testmssql" result="test" >
     INSERT INTO loginDetails VALUES( 'xxx','yyy') 
 </cfquery>

I've insert the two column data and my table loginDetails have PK & Identitycol options. So while dump result it's should return the structure with key & values for CACHED,EXECUTIONTIME,GENERATEDKEY,IDENTITYCOL,RECORDCOUNT and SQL Like below image. enter image description here

<cfquery name="InsertData1" datasource="testmssql" result="test" >
   INSERT INTO test2 VALUES( 'xxx','yyy')
</cfquery>

Here my table test2 I didn't set the Autoincrements ( Constraints ) options. So it's not return GENERATEDKEY,IDENTITYCOL in result structure. Like my below image. enter image description here

So I suggest please check you DB side about that table have proper Constraints or not.

user_64594120
Related