Multiple Insert/Update in ColdFusion

Dreamweaver has some built-in tools for doing database inserts and updates, but they only handle basic inserts/updates of one record. What if you want to insert multiple records at a time, or update multiple records? Dreamweaver is no help in this case, but using ColdFusion the process is simple. This tutorial will show two often-used methods for doing a multiple insert and multiple update, and the reasons not to use the first method. The file testinsert.cfm is included in the download package showing the final code. To use this file, create a database table using the following:

CREATE TABLE mytable (
myIDField int IDENTITY (1, 1) NOT NULL ,
firstname varchar (50) NULL ,
lastname varchar (50) NULL ,
address varchar (50) NULL ,
active int NULL
)

Form Fields

Form fields are generally named differently in your form. For example, you might have "firstname", "lastname", "address", etc. What if you are displaying multiple records, such as an address list? In this case, you could name like fields the same, taking care to give each like field a different ID to keep the html DOM and JavaScript happy and keep your html valid:

In this case, you have three sets of fields -- the like fields are named the same, but the ID is different. This allows you to retrieve the fields using server side code -- the like fields will be submitted as a list. Lists are easily parsed in ColdFusion:

<cfloop from="1" to="#ListLen(form.firstname)#" index="i">
   <cfquery datasource="#mydatasource#">
   INSERT mytable
   (firstname, lastname, address)
   VALUES (
    '#ListGetAt(form.firstname, i)#',
    '#ListGetAt(form.lastname, i)#',
    '#ListGetAt(form.address, i)#'
   }
   </cfquery>
</cfloop>

This technique can work well in some situations, but has a few caveats:

The field must not be empty. If the field is empty, the ColdFusion list functions will not work.
The field must not contain a comma. If the field contains a comma, the length of the list will be inaccurate.
The field must not be a checkbox or radio button. Checkboxes and radio buttons do not exist to the server if they are not checked, so no list is created for non-checked items.

For example, let's say the person filled in the first set of fields (firstname1, etc) and the 3rd set of fields, but left the firstname blank in the 2nd set of fields. In addition, they used a comma in the address:

form.firstname would contain "John,,Jack"

form.lastname would contain "Doe,Smith,Jones"

form.address would contain "555 Mystreet, Apt 3,123 Main St.,555 Bridge St."

The list containing form.firstname would not be parsable by ColdFusion -- CF does not parse empty elements, so the list would only contain 2 elements and the loop would throw an error when attempting to access ListGetAt(form.firstname, 3).

In addition, the form.address list would show as having 4 elements to ColdFusion -- CF makes no distinction between a comma thrown in by CF for the form fields vs. a comma inserted by the user.

Obviously, this technique is limited and probably not a good option in most cases.

A Better Way -- Evaluate to the Rescue

ColdFusion contains an Evaluate function to evaluate dynamically named elements, among other things. For example, if you have a field named "firstname1", you could evaluate the field within a loop like this (assuming i is equal to 1 in this example):

<cfoutput>#Evaluate("form.firstname" & i)#</cfoutput>

or like this:

<cfoutput>#Evaluate("form.firstname#i#")#</cfoutput>

That puts a whole new wrinkle on the multiple insert/update. Instead of naming the form fields with the same name, you would append a counter to the end of the fieldname:

There is one caveat to this method, however -- you must know how many elements exist. You should keep track of how many fields you have, and put the value into a hidden form field:

Now, you can use this information on the next page to do the multiple insert/update:

<cfloop from="1" to="#form.totalrows#" index="i">
   <cfquery datasource="#mydatasource#">
   INSERT mytable
   (firstname, lastname, address)
   VALUES (
    '#Evaluate("form.firstname#i#")#',
    '#Evaluate("form.lastname#i#")#',
    '#Evaluate("form.address#i#")#'
   }
   </cfquery>
</cfloop>

Additionally, checkboxes and radio buttons can use this technique as well, although you have to check for the field's existance (which is a good idea for all fields), as in the following example, which adds a field called "active" to the mix:

<cfloop from="1" to="#form.totalrows#" index="i">
   <cfquery datasource="#mydatasource#">
   INSERT mytable
   (firstname, lastname, address, active)
   VALUES (
   <cfif isdefined("form.firstname#i#") and Evaluate("form.firstname#i#") NEQ ''>
    '#Evaluate("form.firstname#i#")#'
   <cfelse>
   NULL
   </cfif> ,
   <cfif isdefined("form.lastname#i#") and Evaluate("form.lastname#i#") NEQ ''>
    '#Evaluate("form.lastname#i#")#'
   <cfelse>
   NULL
   </cfif>,
   <cfif isdefined("form.address#i#") and Evaluate("form.address#i#") NEQ ''>
    '#Evaluate("form.address#i#")#'
   <cfelse>
   NULL
   </cfif>,
   <cfif isdefined("form.active#i#")>
   #Evaluate('form.active#i#')#
   <cfelse>
   NULL
   </cfif>
   }
   </cfquery>
</cfloop>

Using CFQUERYPARAM

When creating SQL in the ColdFusion page, it is also a good idea to use the <cfqueryparam> tag for the parameter. Adding that to the mix, we get the following final code:

<cfloop from="1" to="#form.totalrows#" index="i">
   <cfquery datasource="test">
   INSERT mytable
   (firstname, lastname, address, active)
   VALUES (
   <cfif isdefined("form.firstname#i#") and Evaluate("form.firstname#i#") NEQ ''>
      <cfqueryparam value="#Evaluate('form.firstname#i#')#" cfsqltype="cf_sql_varchar"/>
   <cfelse>
      NULL
   </cfif> ,
   <cfif isdefined("form.lastname#i#") and Evaluate("form.lastname#i#") NEQ ''>
      <cfqueryparam value="#Evaluate('form.lastname#i#')#" cfsqltype="cf_sql_varchar"/>
   <cfelse>
      NULL
   </cfif>,
   <cfif isdefined("form.address#i#") and Evaluate("form.address#i#") NEQ ''>
      <cfqueryparam value="#Evaluate('form.address#i#')#" cfsqltype="cf_sql_varchar"/>
   <cfelse>
      NULL
   </cfif>,
   <cfif isdefined("form.active#i#")>
      <cfqueryparam value="#Evaluate('form.active#i#')#" cfsqltype="cf_sql_integer"/>
   <cfelse>
      NULL
   </cfif>
   )
   </cfquery>
</cfloop>

Conclusion

This article showed two ways to do a multiple insert/update. The first method has been used in other tutorials and article on the web, however the problems associated with this method were described. Using the Evaluate function you can avoid the problems. The file testinsert.cfm is included in the download package.