User Guide

124 Chapter 10: Lesson 6: Adding and Updating SQL Data
To add data using cfinsert:
1 Open tripeditaction.cfm from the my_app directory in your editor and do the following:
a Remove the entire AddTrip cfquery that you added in the last exercise (from the beginning
<cfquery name ="AddTrip" datasource="CompassTravel"> tag to the </cfquery>
end tag).
b Add the following cfinsert tag to insert data into the trips table in the same location as the
code that you just deleted:
<cfinsert datasource="CompassTravel" tablename="TRIPS">
2 Save the page and test it by opening the tripedit.cfm page in your browser.
3 Follow step 4 through step 9 in the previous exercise to verify this approach to inserting new
trips.
For more information about adding data to a database using the
cfinsert tag, see Developing
ColdFusion MX Applications.
Updating a SQL row using cfupdate
To update an existing SQL row, ColdFusion offers a simple approach for updating SQL rows
through the use of the
cfupdate tag. Like cfinsert, 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 be inserted.
Formfields is a comma-separated list of form
fields to insert. If this attribute is not specified, all 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 TripEdit page the cfupdate tag is used otherwise the
cfinsert tag is
executed. The following example uses the
cfupdate and cfinsert 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 ColdFusion function IsDefined
determines whether the hidden field tripID was
passed to the action page from tripedit.cfm. 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 displays with the updated
trip. If the
cfif statement evaluates to False,
the
cfinsert statement executes and the first
trip displays in the main page.