Get Primary Key after using CFINSERT - ColdFusion

Question

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

MY QUESTION: How can I get the primary key of the row I just added using the CFINSERT?

I know I cant use "Result='variable'" similar to a standard cfquery so what is the best way to get the primary key?

If I run the following query directly after my cfinsert it should return the pervious PK:

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

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

Answer 1

The best way to deal with this is to get the primary key from the generatedKey from the query's result struct.

<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

Answer 2

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

The documentation says

SCOPE_IDENTITY (Transact-SQL)

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

When used in two separate ColdFusion tags (<cfinsert> followed by <cfquery>) then that's two batches and SCOPE_IDENTITY() will not work anymore. Therefore the INSERT and the SELECT statement will have to be part of the same batch. Unfortunately this cannot be achieved with <cfinsert>.

You said you had 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#">