Introducing cfupdate

Introducing <cfupdate>

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.

CAUTION

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

The <cfupdate> tag is similar to the <cfinsert> tag discussed earlier in this chapter. <cfupdate> requires just two attributes: the data source and the name of the table to update, and supports an optional formfields too.

• datasource The name of the data source that contains the table to which the data is to be updated.

• tablename The name of the destination table.

• formfields An optional comma-separated list of fields to be updated. If this attribute isn't 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 ensure that the primary key value is passed, as you did in Listing 14.9 using a hidden field.

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

Listing 14.11. update3.cfmUpdating Data with the <cfupdate> Tag

 <!--- Name:        update3.cfm Author:      Ben Forta ([e-mail]) Description: Table row update demo Created:     12/21/04 ---> <!--- Update movie ---> <cfupdate datasource="ows" tablename="Films"> <!--- Page header ---> <cfinclude template="header.cfm"> <!--- Feedback ---> <cfoutput> <h1>Movie '#FORM.MovieTitle#' updated</h1> </cfoutput> <!--- Page footer ---> <cfinclude template="footer.cfm"> 

To use this code, you must change the <form> action attribute in update1.cfm so that it points to update3.cfm. Make this change, and try updating several movies.

<cfupdate> Versus SQL UPDATE

As with adding data, the choice to use <cfupdate> or SQL UPDATE is yours. The following guidelines as to when to use each option are similar as well:

• Whenever appropriate, use <cfupdate> to update data.

• If you find you need to update specific form fieldsnot all that were submitteduse the <cfupdate> tag with the formfields attribute.

• If <cfupdate> can't be used because you need a complex UPDATE statement or you are using fields that aren't form fields, use SQL UPDATE.

• If you ever need to update multiple (or all) rows in a table, you must use SQL UPDATE.