Get primary key after using CFINSERT - ColdFusion

Question

When I use CFINSERT , the form data is inserted into my database because the field names correspond to the column names.

MY QUESTION:. How to get the primary key of the string I just added using CFINSERT?

I know that I cannot use the "Result =" variable ", similar to the cfquery standard, so what is the best way to get the primary key?

If I run the following request immediately after my cfinsert, it should return the PK you are looking for:

 <cfquery name="getID" datasource="#mydsn#" result="#result#"> select Max(id) as NewID from myTablename; </cfquery> 

Is this the best way to accomplish what I'm trying to do?

Answer 1

Since you are on SQL Server, you can use the SCOPE_IDENTITY() function to safely capture the last inserted identifier value in the current scope.

The documentation says

SCOPE_IDENTITY (Transact-SQL)

Returns the last identity value inserted into the identifier column to the same extent. Volume is a module: stored procedure, trigger, function, or part. Therefore, two operators are in the same area if they are in the same stored procedure, function, or batch.

When used in two separate ColdFusion tags ( <cfinsert> and then <cfquery> ), the two parties and SCOPE_IDENTITY() will no longer work. Therefore, the INSERT and SELECT must be part of the same batch. Unfortunately, this cannot be achieved with <cfinsert> .

You said that you have many fields in your form post, so I would do something like this:

 <cfset fieldNames = "all,relevant,field,names,from,http,post"> <cfset fieldTypes = "INTEGER,VARCHAR,VARCHAR,DATETIME,INTEGER,VARCHAR,VARCHAR"> <cfset fieldNullable = "false,true,true,true,false,true,false"> <cfset fieldCount = ListLen(fieldNames)> <!--- default "" for any fields missing from the HTTP POST ---> <cfloop from="1" to="#fieldCount#" index="i"> <cfparam name="FORM.#ListGetAt(fieldNames, i)#" default=""> </cfloop> <cfquery name="insert" datasource="#yourdatasource#"> INSERT YourTable (#fieldNames#) VALUES ( <cfloop from="1" to="#fieldCount#" index="i"> <cfif i gt 1>,</cfif> <cfset val = FORM[ListGetAt(fieldNames, i)]> <cfset type = "CF_SQL_#ListGetAt(fieldTypes, i)#"> <cfset null = ListGetAt(fieldNullable, i) eq "true" and val eq ""> <cfqueryparam value="#val#" cfsqltype="#type#" null="#null#"> </cfloop> ) SELECT SCOPE_IDENTITY() as NewId </cfquery> <cfdump var="#insert#"> 

Answer 2

The best way to handle this is to get the primary key from generatedKey from the query result structure.

 <cfquery name="myQuery" result="queryResult" datasource="#myDSN#"> INSERT INTO some_table (column_one) VALUES (<cfqueryparam value="#stringForColOne#" cfsqltype="CF_SQL_VARCHAR">) </cfquery> <cfoutput> Generated Key from SQL Insert = #queryResult.generatedKey# </cfoutput> 

See https://wikidocs.adobe.com/wiki/display/coldfusionen/cfquery#cfquery-Usage