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.

Figure 14.4. When using forms to update data, the form fields usually need to populated with existing values.


Building a Data Update Form

Populating an HTML 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 the HTML 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.

NOTE

The query name is necessary here as a prefix because it isn't being used within a <cfoutput> associated with a query.

<cfinput> is a ColdFusion tag, so you can pass variables and columns to it without needing to use <cfoutput>. If you were using <input> instead of <cfinput>, the <input> tags would need to be within a <cfoutput> block.

This is actually another benefit of using <cfinput> instead of <input><cfinput> makes populating form fields with dynamic data much easier.


To ensure that no blank spaces exist 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 some text fields with spaces so they take up the full column width in the table. The MovieTitle field is a 255-character-wide column, so a movie title could have a lot of 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 those extra characters.

When populating forms with table column values, you should always trim the field first. Unlike standard browser output, spaces in form fields arent ignored. Removing them allows easier editing. The ColdFusion trim() function removes spaces at the beginning and end of the value. If you want to trim only trailing spaces, you could use the RTrim() function instead. See 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 Listing 14.10 demonstrates a row update using the SQL UPDATE statement.

See 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"> 

This SQL statement updates the seven specified rows for the movie whose ID is the passed FORM.FilmID.

To test this update template, try executing template update1.cfm with different FilmID values (passed as URL parameters), and then submit your changes.



Macromedia Coldfusion MX 7 Web Application Construction Kit
Macromedia Coldfusion MX 7 Web Application Construction Kit
ISBN: 321223675
EAN: N/A
Year: 2006
Pages: 282

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net