Using Forms to Add or Change Data

This article has been adapted from the first part of Chapter 13 of ColdFusion 4 Web Application Construction Kit by Ben Forta. Published by permission of Macmillan
Publishers Ltd. and the author. Chapter 11 appeared in two parts in the February and March issues of ColdFusion Developer's Journal. Parts 1 and 2 of Chapter 12 appeared in April and May. Part 2 of this article will appear in a forthcoming issue.

Using a Web Browser as a Universal Client
Your online employee list was well received. Everyone has access to up-to-date employee lists and they can search for employees by name, department, or phone extension.

You and your users discover that a Web browser can be used as a front end to access almost any type of data. Using the same front end, a Web browser, makes it easier for people to switch between applications, and greatly lowers the learning curve that each new application introduces. Why? Because there is only one application to learn - the Web browser itself.

The popular term that describes this type of front-end application is universal client. This means that the same client application, your Web browser, is used as a front end to multiple applications.

Adding Data with ColdFusion
When you created the employee search forms in "ColdFusion Forms," you had to create two templates for each search. One created the user search screen that contains the search form, and the other performs the actual search using the ColdFusion <CFQUERY> tag.

Breaking an operation into more than one template is typical of ColdFusion, as well as all Web-based data interaction. A browser's connection to a Web server is made and broken as needed. An HTTP connection is made to a Web server whenever a Web page is retrieved. That connection is broken as soon as that page is retrieved. Any subsequent pages are retrieved with a new connection that is used just to retrieve that page.

There is no way to keep a connection alive for the duration of a complete process - when searching for data for example. Therefore, the process must be broken up into steps, as you read in the April and May issues [CFDJ, Vol. 2, issues 4, 5], and each step is a separate template.

Adding data via your Web browser is no different. You need at least two templates to perform the insertion. One displays the form that you use to collect the data, and the other processes the data and inserts the record.

Adding data to a table involves the following steps:

1. Display a form to collect the data. The names of any input fields should match the names of the columns in the destination table.

2. Submit the form to ColdFusion for processing. ColdFusion adds the row via the ODBC driver using a SQL statement.

Creating an Add Record Form
Forms used to add data are no different from the forms you created to search for data. The form is created using the standard HTML <FORM> and <INPUT> tags, as shown in Listing 1.

The <FORM> ACTION attribute specifies the name of the template to be used to process the insertion; in this case it's EMPADD2.CFM.

Each <INPUT> field has a field name specified in the NAME attribute. These names correspond to the names of the appropriate columns in the Employees table.

You also specified the SIZE and MAXLENGTH attributes in each of the text fields. SIZE is used to specify the size of the text box within the browser window. Without the SIZE attribute, the browser uses its default size, which varies from one browser to the next.

The SIZE attribute does not restrict the number of characters that can be entered into the field. SIZE="30" creates a text field that occupies the space of 30 characters, but the text scrolls within the field if you enter more than 30 characters. In order to restrict the number of characters that can be entered, you must use the MAXLENGTHattribute.MAXLENGTH="30" instructs the browser to allow no more than 30 characters in the field.

The SIZE attribute is primarily used for aesthetics and the control of screen appearance. MAXLENGTH is used to ensure that only data that can be handled is entered into a field. Without MAXLENGTH, users could enter more data than would fit in a field, and that data would be truncated upon insertion.

You do not have to specify the same SIZE and MAXLENGTH values. The following example only allocates 20 characters of screen space for the field, but allows 30 characters to be entered. Once 20 characters have been entered into the field, the text scrolls to accommodate the extra characters.

<INPUT TYPE="text" NAME="FirstName" SIZE="20" MAXLENGTH="30">

Processing Additions
The next thing you need is a template to process the actual data insertion. Use the SQL INSERT statement to add the row.

As shown in Listing 2, the <CFQUERY> tag can be used to pass any SQL statement - not just SELECT statements. The SQL statement here is INSERT, which adds a row to the Employees table and sets the FirstName, LastName, and PhoneExtension columns to the form values passed by the browser.

Note: The <CFQUERY> in Listing 2 has no NAME attribute. NAME is an optional attribute and is only necessary if you need to manipulate the data returned by <CFQUERY>. Because the operation here is an INSERT, no data is returned; the NAME attribute is unnecessary.

Save this template as C:\A2Z\SCRIPTS\13\EMPADD2.CFM and then execute the EMPADD1.CFM template with your browser. Try adding an employee to the table; your browser display should look like the one shown in Figure 2.

You can verify that the employee was added by browsing the table with Microsoft Access, Microsoft Query, or any of the employee search templates that you created in the article [CFDJ, Vol. 2, issues 4, 5].

Introducing <CFINSERT>
The example in Listing 2 demonstrates how to add data to a table using the standard SQL INSERT command. This works very well if you only have to provide data for 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, while 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 situation you should always try to avoid. As a rule, you should 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.

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.

While this might be a workable solution, it is 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 is the name of the ODBC data source that contains the table to which the data is to be inserted.

TABLENAME is the name of the destination table.

FORMFIELDS is an optional comma-separated list of fields to be inserted. If this attribute is not provided, all the fields in the submitted form are used.

Look at the following ColdFusion tag:

<CFINSERT DATASOURCE="A2Z" TABLENAME="Employees">

This code does exactly the same thing as the <CFQUERY> tag in Listing 2. 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 (see Listing 3).

Try modifying the form in template EMPADD1.CFM so that it submits the form to template EMPADD3.CFM instead of EMPADD2.CFM; then add a record. You'll see the code in Listing 3 does exactly the same thing as the code in Listing 2, but with a much simpler syntax and interface.

Of course, because <CFINSERT> builds its SQL statements dynamically, EMPADD3.CFM can be used even if you add fields to the data entry form. Listing 4 contains an updated template that adds several fields to the Add an Employee form. Even so, it submits data to the same template that you just created. Using <CFINSERT> allows for a cleaner action template - one that does not require changing every time the form itself changes.

Try adding an employee using this new form; your browser display should look no different than it did before.

When to Use <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 not want ColdFusion to include certain fields. For example, you might have hidden fields in your form that are not table columns, like the hidden field shown in Listing 5. That field might be there as part of a security system you have implemented; it is not a column in the table. If you try to pass this field to <CFINSERT>, ColdFusion passes the hidden Login field as a column. Obviously this generates an ODBC error, as seen in Figure 3.

In order to solve this problem you must use the FORMFIELDS attribute. FORMFIELDS instructs ColdFusion to only process form fields that are in the list. Any other fields are ignored.

It is important to note that FORMFIELDS is not 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 are not present, they are not processed. Any fields that are not listed in the FORMFIELDS list are ignored.

Listing 6 contains an updated data insertion template. The <CFINSERT> tag now has a FORMFIELDS attribute, and so now ColdFusion knows to ignore the hidden Login field in EMPADD5.CFM. The following code ensures that only these fields are processed, and that any others are ignored:

Collecting Data for More Than One INSERT
Another situation in which <CFINSERT> FORMFIELDS can be used is 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 both simpler and helps prevent the creation of multiple similar templates.

Why would you ever avoid using <CFINSERT>? Is there ever a reason to use SQL INSERT instead of <CFINSERT>?

The truth is that both are needed. <CFINSERT> can only be used for simple data insertion to a single table. If you want to insert the results of a SELECT statement, you could not use <CFINSERT>. Similarly, if you want to insert values other than FORM fields - perhaps variables or URL parameters - you'd be unable to use <CFINSERT>.

Here are some guidelines to help you decide when to use each method:

1. Whenever possible, use <CFINSERT> to add data to ODBC tables.

2. If you find that you need to add specific form fields - and not all that were submitted - use the <CFINSERT> tag with the FORMFIELDS attribute.

3. If <CFINSERT> cannot be used because you need a complex INSERT statement or are using fields that are not form fields, use SQL INSERT.

Updating Data with ColdFusion
Updating data with ColdFusion is very similar to inserting data. You need two templates to update a row - a data entry form template and a data update template. The big difference between a form used for data addition and one used for data modification is that the latter needs to be populated with existing values, like the screen shown in Figure 4.

Building a Data Update Form
Populating an HTML form is a very simple process. First you need to retrieve the row to be updated from the table. You do this with a standard <CFQUERY>; the retrieved values are then passed as attributes to the HTML form. (See "The CFQUERY Tag" [CFDJ, Vol. 2, issue 2] for a detailed discussion of the ColdFusion CFQUERY tag and how it is used.)

Listing 7 contains the code for EMPUPD1.CFM, a template that updates an employee record. You must specify an employee ID to test this template. Without it, ColdFusion would not know what row to retrieve. To ensure that an employee ID is passed, the first thing you do is check for the existence of the EmployeeID parameter. The following code returns TRUE only if EmployeeID was not passed, in which case an error message is sent back to the user and template processing is halted with the <CFABORT> tag:

<CFIF IsDefined("EmployeeID") IS "No">

Without the <CFABORT> tag, ColdFusion continues processing the template. An error message is generated when the <CFQUERY> statement is processed because the WHERE clause WHERE EmployeeID = #EmployeeID# references a nonexistent field.

Test the EMPUPD1.CFM template, passing ?EmployeeID=7 as a URL parameter. Your screen should look like the one shown in Figure 4.

Before you create the data update template, take a closer look at Listing 7. The template is similar to the Add an Employee template, but has some important differences.

The first thing you do is verify that the primary key, EmployeeID, is
present. ColdFusion can then retrieve the employee data with the <CFQUERY> tag. The WHERE clause WHERE EmployeeID = #EmployeeID# selects data by the primary key value, ensuring that no more than one row will ever be retrieved. The rest of the template is contained with a <CFOUTPUT> tag, allowing you to use any of the retrieved columns within the page body. (See "Displaying Query Results with the CFOUTPUT Tag" [CFDJ, Vol. 2, issue 2] for an explanation of that particular tag.)

The retrieved data is used throughout the template. Even the page title is dynamically created with the code:

<TITLE>Update an Employee - #LastName#, #FirstName#</TITLE>

To populate the data entry fields, the current field value is passed to the <INPUT> VALUE attribute. For employee 7, Kim Black, this code:

To ensure that there are no blank spaces after the retrieved value, the fields are trimmed with the ColdFusion Trim() function before they are displayed. Why would you do this? Some databases, such as Microsoft SQL Server, pad text fields with spaces so that they take up the full column width in the table. The FirstName field is a 30-character-wide column, and so the name Kim is retrieved with 27 spaces after it! The extra space can be very annoying when you try to edit the field. To append text to a field, you'd first have to backspace or delete all of those extra characters.

There is one hidden field in the FORM. The following code creates a hidden field called EmployeeID, which contains the ID of the employee being updated:

<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="#EmployeeID#">

This hidden field must be present. Without it, ColdFusion has no idea what row you were updating when the form was actually submitted.

Remember that HTTP sessions are created and broken as needed, and every session stands on its own two feet. ColdFusion retrieved a specific row of data for you in one session, but it does not know that in the next session. Therefore, when you update a row, you must specify the primary key so that ColdFusion knows which row to update.

Processing Updates
Just as with adding data, there are two ways to update rows in a table. The code in Listing 8 demonstrates a row update using the SQL UPDATE statement.

This SQL statement updates the six specified rows for the employee whose ID is the passed EmployeeID.

To test this update template, try executing template EMPUPD1.CFM with different EmployeeID values (pass as URL parameters), and then submit your changes.

Introducing <CFUPDATE>
Just as you saw earlier in regards to inserting data, hard-coded SQL statements are neither flexible nor easy to maintain. ColdFusion provides a simpler way to update rows in database tables.

The <CFUPDATE> tag is very similar to the <CFINSERT> tag discussed earlier in this article. <CFUPDATE> requires just two attributes: the ODBC data source and the name of the table to update.

Just like <CFINSERT>, the following attributes are available to you:

DATASOURCE is the name of the ODBC data source that contains the table to which the data is to be updated.

TABLENAME is the name of the destination table.

FORMFIELDS is an optional comma-separated list of fields to be updated. If this attribute is not provided, all the fields in the submitted form are used.

When using <CFUPDATE>, ColdFusion automatically locates the row you want to update by looking at the table to ascertain its primary key. All you have to do is make sure that primary key value is passed, as you did in Listing 7 using a hidden field.

The code in Listing 9 performs the same update as that in Listing 8, but uses the <CFUPDATE> tag rather than the SQL UPDATE tag. Obviously this code is both more readable, reusable, and accommodating of form field changes you might make in the future.

You have to change the <FORM> ACTION attribute in EMPUPD1.CFM in order to use EMPUP3.CFM to test this form. Make this change and try updating several employee records.

CFUPDATE Versus SQL Update
Just as with adding data, the choice to use <CFUPDATE> or SQL UPDATE is yours. The guidelines as to when to use each option are similar as well.

The following are some guidelines that help you decide when to use each method.

1. Whenever possible, use <CFUPDATE> to update data to ODBC tables.

2. If you find that you need to update specific form fields - not all that were submitted - use the <CFUPDATE> tag with the FORMFIELDS attribute.

3. If <CFUPDATE> cannot be used because you need a complex UPDATE statement or you are using fields that are not form fields, use SQL UPDATE.

4. If you ever need to update all rows in a table, you must use SQL UPDATE.

Related Links

Ben Forta is Adobe's Senior Technical Evangelist. In that capacity he spends a considerable amount of time talking and writing about Adobe products (with an emphasis on ColdFusion and Flex), and providing feedback to help shape the future direction of the products. By the way, if you are not yet a ColdFusion user, you should be. It is an incredible product, and is truly deserving of all the praise it has been receiving. In a prior life he was a ColdFusion customer (he wrote one of the first large high visibility web sites using the product) and was so impressed he ended up working for the company that created it (Allaire). Ben is also the author of books on ColdFusion, SQL, Windows 2000, JSP, WAP, Regular Expressions, and more. Before joining Adobe (well, Allaire actually, and then Macromedia and Allaire merged, and then Adobe bought Macromedia) he helped found a company called Car.com which provides automotive services (buy a car, sell a car, etc) over the Web. Car.com (including Stoneage) is one of the largest automotive web sites out there, was written entirely in ColdFusion, and is now owned by Auto-By-Tel.

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

Adobe ColdFusion software solves the day-to-day challenges of Internet application development, allowing Web application developers to be extremely productive as they create and deliver engaging online applications. And because it fits into any IT environment, Web designers can use ColdFusion for everything from small departmental applications to highly scalable, reliable implementations of the most important business applications. ColdFusion Developer's Journal was first launched 12 years ago. Ulitzer brings more than 7,000 original CFDJ articles and ColdFusion authors from archives in ColdFusion Developer's Journal 2.0.

Cloud Expo

Cloud Computing & All That
It Touches In One Location Cloud Computing - Big Data - Internet of Things
SDDC - WebRTC - DevOps
Cloud computing is become a norm within enterprise IT.

The competition among public cloud providers is red hot, private cloud continues to grab increasing shares of IT budgets, and hybrid cloud strategies are beginning to conquer the enterprise IT world.

Big Data is driving dramatic leaps in resource requirements and capabilities, and now the Internet of Things promises an exponential leap in the size of the Internet and Worldwide Web.

The world of SDX now encompasses Software-Defined Data Centers (SDDCs) as the technology world prepares for the Zettabyte Age.

Add the key topics of WebRTC and DevOps into the mix, and you have three days of pure cloud computing that you simply cannot miss.

Delegates will leave Cloud Expo with dramatically increased understanding the entire scope of the entire cloud computing spectrum from storage to security.

Cloud Expo - the world's most established event - offers a vast selection of 130+ technical and strategic Industry Keynotes, General Sessions, Breakout Sessions, and signature Power Panels. The exhibition floor features 100+ exhibitors offering specific solutions and comprehensive strategies. The floor also features two Demo Theaters that give delegates the opportunity to get even closer to the technology they want to see and the people who offer it.

Attend Cloud Expo. Craft your own custom experience. Learn the latest from the world's best technologists. Find the vendors you want and put them to the test.