Introducing cfinsert

Introducing `<cfinsert>`

The example in Listing 14.4 demonstrates how to add data to a table using the standard SQL INSERT command. This works very well if you have to provide data for only a few columns, and if those columns are always provided. If the number of columns can vary, using SQL INSERT gets rather complicated.

For example, assume you have two or more data-entry forms for similar data. One might collect a minimal number of fields, whereas another collects a more complete record. How would you create a SQL INSERT statement to handle both sets of data?

You could create two separate templates, with a different SQL INSERT statement in each, but that's a poor solution. You should always try to avoid having more than one template perform a given operation. That way, you don't run the risk of future changes and revisions being applied incorrectly. If a table name or column name changes, for example, you won't have to worry about forgetting one of the templates that references the changed column.

TIP

As a rule, never create more than one template to perform a specific operation. This helps prevent introducing errors into your templates when updates or revisions are made. You're almost always better off creating one template with conditional code than creating two separate templates.

Another solution is to use dynamic SQL. You could write a basic INSERT statement and then gradually construct a complete statement by using a series of <cfif> statements.

This is a workable solution, but not a very efficient one. The conditional SQL INSERT code is far more complex than conditional SQL SELECT. The INSERT statement requires that both the list of columns and the values be dynamic. In addition, the INSERT syntax requires that you separate all column names and values by commas. This means that every column name and value must be followed by a comma except the last one in the list. Your conditional SQL has to accommodate these syntactical requirements when the statement is constructed.

A better solution is to use <cfinsert>, which is a special ColdFusion tag that hides the complexity of building dynamic SQL INSERT statements. <cfinsert> takes the following parameters as attributes:

datasource The name of the data source that contains the table to which the data is to be inserted.
tablename The name of the destination table.
formfields An optional comma-separated list of fields to be inserted. If this attribute isn't provided, all the fields in the submitted form are used.

Look at the following ColdFusion tag:

 <cfinsert datasource="ows" tablename="Films">

This code does exactly the same thing as the <cfquery> tag in Listing 14.4. When ColdFusion processes a <cfinsert> tag, it builds a dynamic SQL INSERT statement under the hood. If a formfields attribute is provided, the specified field names are used. No formfields attribute was specified in this example, so ColdFusion automatically uses the form fields that were submitted, building the list of columns and the values dynamically. <cfinsert> even automatically handles the inclusion of single quotation marks where necessary.

CAUTION

If you're using Windows 98 or ME and are using Microsoft Access, you won't be able to use the <cfinsert> tag, due to limitations with the Access database drivers on these platforms. You can still insert data using <cfquery> and INSERT, and <cfinsert> will function correctly if you are using Access on Windows 2000 or Windows XP.

While we are it, the form created in insert1.cfm did not perform any data validation, which could cause database errors to be thrown (try inserting text in a numeric field and see what happens).

Listing 14.5 contains a revised form (a modified version of insert1.cfm); save this file as insert3.cfm. Listing 14.6 contains a revised action page (a modified version of insert2.cfm); save this file as insert4.cfm.

Listing 14.5. `insert3.cfm`Using `<cfform>` For Field Validation

 <!--- Name:        insert3.cfm Author:      Ben Forta (ben@forta.com) Description: Table row insertion demo Created:     12/21/04 ---> <!--- Get ratings ---> <cfquery datasource="ows" name="ratings"> SELECT RatingID, Rating FROM FilmsRatings ORDER BY RatingID </cfquery> <!--- Page header ---> <cfinclude template="header.cfm"> <!--- New movie form ---> <cfform action="insert4.cfm"> <table align="center" bgcolor="orange">  <tr>   <th colspan="2">    <font size="+1">Add a Movie</font>   </th>  </tr>  <tr>   <td>    Movie:   </td>   <td>    <cfinput type="Text"             name="MovieTitle"             message="MOVIE TITLE is required!"             required="Yes"             validateAt="onSubmit,onServer"             size="50"             maxlength="100">   </td>  </tr>  <tr>   <td>    Tag line:   </td>   <td>    <cfinput type="Text"             name="PitchText"             message="TAG LINE is required!"             required="Yes"             validateAt="onSubmit,onServer"             size="50"             maxlength="100">   </td>  </tr>  <tr>   <td>    Rating:   </td>   <td>    <!--- Ratings list --->    <select name="RatingID">     <cfoutput query="ratings">      <option value="#RatingID#">#Rating#</option>     </cfoutput>    </select>   </td>  </tr>  <tr>   <td>    Summary:   </td>   <td>    <textarea name="summary"              cols="40"              rows="5"              wrap="virtual"></textarea>   </td>  </tr>  <tr>   <td>    Budget:   </td>   <td>    <cfinput type="Text"             name="AmountBudgeted"             message="BUDGET must be a valid numeric amount!"             required="NO"             validate="integer"             validateAt="onSubmit,onServer"             size="10"             maxlength="10">   </td>  </tr>  <tr>   <td>    Release Date:   </td>   <td>    <cfinput type="Text"             name="DateInTheaters"             message="RELEASE DATE must be a valid date!"             required="NO"             validate="date"             validateAt="onSubmit,onServer"             size="10"             maxlength="10">   </td>  </tr>  <tr>   <td>    Image File:   </td>   <td>    <cfinput type="Text"             name="ImageName"             required="NO"             size="20"             maxlength="50">   </td>  </tr>  <tr>   <td colspan="2" align="center">    <input type="submit" value="Insert">   </td>    </tr> </table> </cfform> <!--- Page footer ---> <cfinclude template="footer.cfm">

Listing 14.6 is the same form used previously, except that <input> has been replaced with <cfinput> so as to validate submitted data, and form field validation included, using the techniques described in Chapter 13.

Listing 14.6. `insert4.cfm`Adding Data with the `<cfinsert>` Tag

 <!--- Name:        insert4.cfm Author:      Ben Forta (ben@forta.com) Description: Table row insertion demo Created:     12/21/04 ---> <!--- Insert movie ---> <cfinsert datasource="ows" tablename="Films"> <!--- Page header ---> <cfinclude template="header.cfm"> <!--- Feedback ---> <cfoutput> <h1>New movie '#FORM.MovieTitle#' added</h1> </cfoutput> <!--- Page footer ---> <cfinclude template="footer.cfm">

Try adding a movie with these new templates. You'll see that the database inserting code in Listing 14.6 does exactly the same thing as the code in Listing 14.4, but with a much simpler syntax and interface.

Controlling `<cfinsert>` Form Fields

<cfinsert> instructs ColdFusion to build SQL INSERT statements dynamically. ColdFusion automatically uses all submitted form fields when building this statement.

Sometimes you might want ColdFusion to not include certain fields. For example, you might have hidden fields in your form that aren't table columns, such as the hidden field shown in Listing 14.7. That field might be there as part of a security system you have implemented; it isn't a column in the table. If you try to pass this field to <cfinsert>, ColdFusion passes the hidden Login field as a column to the database. Obviously, this generates an database error, as seen in Figure 14.3, because no Login column exists in the Films table.

Listing 14.7. `insert5.cfm`Movie Addition Form With Hidden Login Field

 <!--- Name:        insert5.cfm Author:      Ben Forta (ben@forta.com) Description: Table row insertion demo Created:     12/21/04 ---> <!--- Get ratings ---> <cfquery datasource="ows" name="ratings"> SELECT RatingID, Rating FROM FilmsRatings ORDER BY RatingID </cfquery> <!--- Page header ---> <cfinclude template="header.cfm"> <!--- New movie form ---> <cfform action="insert6.cfm"> <!--- Login field ---> <input type="hidden" name="Login" value="Ben"> <table align="center" bgcolor="orange">  <tr>   <th colspan="2">    <font size="+1">Add a Movie</font>   </th>  </tr>  <tr>   <td>    Movie:   </td>   <td>    <cfinput type="Text"             name="MovieTitle"             message="MOVIE TITLE is required!"             required="Yes"             validateAt="onSubmit,onServer"             size="50"             maxlength="100">   </td>  </tr>  <tr>   <td>    Tag line:   </td>   <td>    <cfinput type="Text"             name="PitchText"             message="TAG LINE is required!"             required="Yes"             validateAt="onSubmit,onServer"             size="50"             maxlength="100">   </td>  </tr>  <tr>   <td>    Rating:   </td>   <td>    <!--- Ratings list --->    <select name="RatingID">     <cfoutput query="ratings">      <option value="#RatingID#">#Rating#</option>     </cfoutput>    </select>   </td>  </tr>  <tr>   <td>    Summary:   </td>   <td>    <textarea name="summary"              cols="40"              rows="5"              wrap="virtual"></textarea>   </td>  </tr>  <tr>   <td>    Budget:   </td>   <td>    <cfinput type="Text"             name="AmountBudgeted"             message="BUDGET must be a valid numeric amount!"             required="NO"             validate="integer"             validateAt="onSubmit,onServer"             size="10"             maxlength="10">   </td>  </tr>  <tr>   <td>    Release Date:   </td>   <td>    <cfinput type="Text"             name="DateInTheaters"             message="RELEASE DATE must be a valid date!"             required="NO"             validate="date"             validateAt="onSubmit,onServer"             size="10"             maxlength="10">   </td>  </tr>  <tr>   <td>    Image File:   </td>   <td>    <cfinput type="Text"             name="ImageName"             required="NO"             size="20"             maxlength="50">   </td>  </tr>  <tr>   <td colspan="2" align="center">    <input type="submit" value="Insert">   </td>    </tr> </table> </cfform> <!--- Page footer ---> <cfinclude template="footer.cfm">

Figure 14.3. An error message is generated if ColdFusion tries to insert fields that aren't table columns.

To solve this problem, you must use the formfields attribute. formfields instructs ColdFusion to process only form fields that are in the list. Any other fields are ignored.

It's important to note that formfields isn't used to specify which fields ColdFusion should process. Rather, it specifies which fields should not be processed. The difference is subtle. Not all fields listed in the formfields value need be present. They are processed if they are present; if they aren't present, they aren't processed (so no error will be generated). Any fields not listed in the formfields list are ignored.

Listing 14.8 contains an updated data insertion template. The <cfinsert> tag now has a formfields attribute, so now ColdFusion knows to ignore the hidden Login field.

Listing 14.8. `insert6.cfm`Using the `<cfinsert>` formfields Attribute

 <!--- Name:        insert6.cfm Author:      Ben Forta (ben@forta.com) Description: Table row insertion demo Created:     12/21/04 ---> <!--- Insert movie ---> <cfinsert datasource="ows"           tablename="Films"           formfields="MovieTitle,                       PitchText,                       AmountBudgeted,                       RatingID,                       Summary,                       ImageName,                       DateInTheaters"> <!--- Page header ---> <cfinclude template="header.cfm"> <!--- Feedback ---> <cfoutput> <h1>New movie '#FORM.MovieTitle#' added</h1> </cfoutput> <!--- Page footer ---> <cfinclude template="footer.cfm">

Collecting Data for More Than One `INSERT`

Here's another situation where <cfinsert> formfields can be used: when a form collects data that needs to be added to more than one table. You can create a template that has two or more <cfinsert> statements by using formfields.

As long as each <cfinsert> statement has a formfields attribute that specifies which fields are to be used with each INSERT, ColdFusion correctly executes each <cfinsert> with its appropriate fields.

`<cfinsert>` Versus SQL `INSERT`

Adding data to tables using the ColdFusion <cfinsert> tag is simpler and helps prevent the creation of multiple similar templates.

So why would you ever not use <cfinsert>? Is there ever a reason to use SQL INSERT instead of <cfinsert>?

The truth is that both are needed. <cfinsert> can be used only for simple data insertion to a single table. If you want to insert the results of a SELECT statement, you can't use <cfinsert>. And you can't use <cfinsert> if you want to insert values other than FORM fieldsvariables or URL parameters, say

These guidelines will help you decide when to use which method:

For simple operations (single table and no complex processing), use <cfinsert> to add data.
If you find that you need to add specific form fieldsand not all that were submitteduse the <cfinsert> tag with the formfields attribute.
If <cfinsert> can't be used because you need a complex INSERT statement or are using fields that aren't form fields, use SQL INSERT.

TIP

I have seen many documents and articles attempt to dissuade the use of <cfinsert> (and <cfupdate> discussed below), primarily because of the limitations already mentioned. In my opinion there is nothing wrong with using these tags at all, recognizing their limitations of course. In fact, I'd even argue that their use is preferable as they are dynamic (if the form changes they may not need changing) and are type aware (they handle type conversions automatically). So don't let the naysayers get you down. CFML is all about making your development life easier, so if these tags make coding easier, use them.