I recently had a problem with CFINSERT when I had to insert data into an autonumber column. Even after deleting all columns in the table, when I tried CFINSERT ing I got the error: Cannot insert explicit value for identity column in table 'style' when IDENTITY_INSERT is set to OFF. <br>The error occurred on line 65535.

Now, being a lazy git, I couldn't be bothered interrogating the DB structure so that I could use the correct CFQUERYPARAM for each field. So, as CFINSERT is dead easy to use, I thought I'd rather find a workaround..

Simple really, enclose the CFINSERT in a CFTRANSACTION so as far as the DB server is concerned it's all part of the same query.

   <cftransaction>

    <cftry>
     <cfquery datasource="#odbc_datasource#">
     set identity_insert #arguments.tablename# ON;
     </cfquery> 
     <cfcatch type="any">
     <!--- Probably doesn't have an identity column --->
     </cfcatch>
    </cftry>

    <cfinsert tablename="#arguments.tablename#" tableowner="dbo" formfields="#lcase(tColumns)#" datasource="#odbc_datasource#">

    <cftry>
     <cfquery datasource="#odbc_datasource#">
     set identity_insert #arguments.tablename# OFF;
     </cfquery> 
     <cfcatch type="any">
     <!--- Probably doesn't have an identity column --->
     </cfcatch>
    </cftry>

   </cftransaction>

The CFCATCH in the IDENTITY part catches the following error:-

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Table 'style_links' does not have the identity property. Cannot perform SET operation. <br>The error occurred on line 65535.