Updating Data with ColdFusion

Updating data with ColdFusion is similar to inserting data. You generally need two templates to update a rowa data-entry form template and a data update one. 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. See the screen in Figure 14.4.

Building a Data Update Form

Populating a269 form is a simple process. First, you must 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 th260 form.

Listing 14.9 contains the code for update1.cfm, a template that updates a movie. Save it as update1.cfm, and then execute it. Be sure to append the FilmIDfor example, ?FilmID=13as a URL parameter. Your screen should look like Figure 14.4.

Listing 14.9. update1.cfmMovie Update Form

<!---
Name:        update1.cfm
Author:      Ben Forta (ben@forta.com)
Description: Table row update demo
Created:     12/21/04
--->
<!--- Check that FilmID was provided --->
<cfif NOT IsDefined("URL.FilmID")>
<h1>You did not specify the FilmID</h1>
<cfabort>
</cfif>
<!--- Get the film record --->
<cfquery datasource="ows" name="film">
SELECT FilmID, MovieTitle, PitchText,
AmountBudgeted, RatingID,
Summary, ImageName, DateInTheaters
FROM Films
WHERE FilmID=#URL.FilmID#
</cfquery>
<!--- Get ratings --->
<cfquery datasource="ows" name="ratings">
SELECT RatingID, Rating
FROM FilmsRatings
ORDER BY RatingID
</cfquery>
<!--- Page header --->
<cfinclude template="header.cfm">
<!--- Update movie form --->
<cfform action="update2.cfm">
<!--- Embed primary key as a hidden field --->
<cfoutput>
<input type="hidden" name="FilmID" value="#Film.FilmID#">
</cfoutput>
<table align="center" bgcolor="orange">
<tr>
<th colspan="2">
<font size="+1">Update a Movie</font>
</th>
</tr>
<tr>
<td>
Movie:
</td>
<td>
<cfinput type="Text"
name="MovieTitle"
valu="#Trim(film.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"
value="#Trim(film.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#"
<cfif ratings.RatingID IS film.RatingID>
selected
</cfif>>#Rating#
</option>
</cfoutput>
   </select>
</td>
</tr>
<tr>
<td>
Summary:
</td>
<td>
<cfoutput>
<textarea name="summary"
cols="40"
rows="5"
wrap="virtual">#Trim(Film.Summary)#</textarea>
</cfoutput>
</td>
</tr>
<tr>
<td>
Budget:
</td>
<td>
<cfinput type="Text"
name="AmountBudgeted"
value="#Int(film.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"
value="#DateFormat(film.DateInTheaters, "MM/DD/YYYY")#"
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"
            value="#Trim(film.ImageName)#"
required="NO"
size="20"
maxlength="50">
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="Update">
</td>
</tr>
</table>
</cfform>
<!--- Page footer --->
<cfinclude template="footer.cfm">

There is a lot to look at in Listing 14.9. And don't submit the form yet; you have yet to create the action page.

To populate a form with data to be updated, you must first retrieve that row from the table. Therefore, you must specify a FilmID to use this template. Without it, ColdFusion wouldn't know which row to retrieve. To ensure that the FilmID is passed, the first thing you do is check for the existence of the FilmID parameter. The following code returns TRUE only if FilmID 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 NOT IsDefined("URL.FilmID")>

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 FilmID = #URL.FilmID# references a nonexistent field.

The first <cfquery> tag retrieves the row to be edited, and the passed URL is used in the WHERE clause to retrieve the appropriate row. The second <cfquery> retrieves the list of ratings for the <select> control. To populate the data-entry fields, the current field value is passed to the <input> (or <cfinput>) value attribute. Whatever is passed to value is displayed in the field, so value="#Film.MovieTitle#" displays the MovieTitle table column.

Appendix C, "ColdFusion Function Reference," for a complete explanation of the ColdFusion trim() functions.

Dates and numbers are also formatted specially. By default, dates are displayed in a rather unusable format (and a format that won't be accepted upon form submission). Therefore, DateFormat() is used to format the date in a usable format.

The AmountBudgeted column allows numbers with decimal points; to display the number within the trailing decimal point and zeros, the Int() function can be used to round the number to an integer. You also could have used NumberFormat() for more precise number formatting.

One hidden field exists in the FORM. The following code creates a hidden field called FilmID, which contains the ID of the movie being updated:

<input type="hidden" name="FilmID" value="#Film.FilmID#">

This hidden field must be present. Without it, ColdFusion has no idea which row you were updating when the form was actually submitted. Also, because it is an <input> field (not <cfinput>), it must be enclosed within <cfoutput> tags.

Remember that HTTP sessions are created and broken as necessary, and every session stands on its own two feet. ColdFusion might retrieve a specific row of data for you in one session, but it doesn't know that in the next session. Therefore, when you update a row, you must specify the primary key so ColdFusion knows which row to update. Hidden fields are one way of doing this because they are sent to the browser as part of the form, but are never displayed and thus can't be edited. However, they are still form fields, and they are submitted along with all other form fields intact upon form submission.

Processing Updates

As with adding data, there are two ways to update rows in a table. The code in Chapter 6 for an explanation of the UPDATE statement.

Listing 14.10. update2.cfmUpdating a Table with SQL UPDATE

<!---
Name:        update2.cfm
Author:      Ben Forta (ben@forta.com)
Description: Table row update demo
Created:     12/21/04
--->
<!--- Update movie --->
<cfquery datasource="ows">
UPDATE Films
SET MovieTitle='#Trim(FORM.MovieTitle)#',
PitchText='#Trim(FORM.PitchText)#',
AmountBudgeted=#FORM.AmountBudgeted#,
RatingID=#FORM.RatingID#,
Summary='#Trim(FORM.Summary)#',
ImageName='#Trim(FORM.ImageName)#',
DateInTheaters=#CreateODBCDate(FORM.DateInTheaters)#
WHERE FilmID=#FORM.FilmID#
</cfquery>
<!--- Page header --->
<cfinclude template="header.cfm">
<!--- Feedback --->
<cfoutput>
<h1>Movie '#FORM.MovieTitle#' updated</h1>
</cfoutput>
<!--- Page footer --->
<cfinclude template="footer.cfm">