Exercise 4: Updating a SQL row using the cfupdate tag

To update an existing SQL row, ColdFusion offers a simple approach through the use of the cfupdate tag. Like the cfinsert tag, the cfupdate tag has datasource and tablename attributes to specify where the data is to be inserted. The tag also has a formfields attribute to identify which fields are to insert. The formfields attribute specifies a comma-separated list of form fields to insert. If this attribute is not specified, all the fields in the form are included in the operation.

All the fields of the tripedit.cfm page have corresponding columns in the Trips table, so you can omit the FormFields attribute for both the cfinsert and cfupdate tags. If the tripID form field is passed from the Trip Edit page, the cfupdate tag is used; otherwise the cfinsert tag is executed. The following example uses the cfupdate and cfinsert tags without the formfields attribute:

<cfif not isdefined("form.tripID")>
	<cfinsert datasource="CompassTravel" tablename="Trips"> 
		<cflocation url="tripdetail.cfm">
	<cfelse>
	<cfupdate datasource="CompassTravel" tablename="Trips">
		<cflocation url="tripdetail.cfm?ID=#Form.tripID#">
</cfif>

Reviewing the code

The following tables describes the cfinsert and cfupdate code:

Code

Explanation

<cfif not isdefined("form.tripID")>
	<cfinsert datasource="CompassTravel"
	tablename="Trips"> 
		<cflocation url="tripdetail.cfm">
<cfelse>
<cfupdate datasource="CompassTravel"
	tablename="Trips">
<cflocation url="tripdetail.cfm?ID=#Form.tripID#">
</cfif>

The IsDefined function determines whether the hidden field tripID was passed to the action page from the tripedit.cfm page. If there is a current trip, the IsDefined function returns True. When there is no current trip, the cfif statement is True. When the cfif statement is True, the cfinsert tag executes and the main page appears with the updated trip. If the cfif statement evaluates to False, the cfinsert statement executes and the first trip appears in the main page.

To update the database using a cfupdate tag:

  1. Open the tripeditaction.cfm file in the my_app directory in your editor.
  2. Add the following code before the </body> tag at the end of the file:
    <!--- Update the database --->
    	<cfif isOk EQ "Yes">
    	   <cfif isdefined("form.tripID")>
    		   <cfupdate datasource="CompassTravel" tablename="trips">
    	       <cflocation url="tripdetail.cfm?ID=#Form.tripID#">
    	   <cfelse>
    		   <cfinsert datasource="CompassTravel" tablename="TRIPS"> 
               <cflocation url="tripdetail.cfm">
    	   </cfif>
    	<cfoutput>You have added #Form.TripName# to the trips database.
    	</cfoutput>
    	</cfif>
    
  3. Save the file.

For more information about adding data to a database using the cfupdate tag, see ColdFusion MX Developer’s Guide.