User Guide

Completing the Trip Maintenance application 123
Reviewing the code
The following table describes the SQL INSERT and cfquery code used to add data:
For more information about adding data to a database using SQL and
cfquery, see Developing
ColdFusion MX Applications. For more information about SQL, consult any SQL primer.
Adding data using the simpler, cfinsert approach
For those who would prefer not to have to remember SQL syntax to add information to SQL
databases, ColdFusion simplifies the coding for inserting SQL rows through the use of the
cfinsert tag. As you might expect, the cfinsert tag has datasource and tablename attributes
to specify where the data is inserted. The tag also has a
formfields attribute to identify which
fields to insert.
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. The following example uses the
cfinsert with these attributes:
<cfinsert datasource="CompassTravel" tablename="Trips"
formfields="tripName, eventType, tripDescription, tripLocation, departureDate,
returnDate, price, tripLeader, photo, baseCost, numberPeople,
depositRequired">
The cfinsert tag used in the previous code snippet uses the following attributes:
Exercise: insert trip data using cfinsert
In this exercise, you change the approach the action page uses to insert the data into the database.
You will replace the SQL INSERT statement with the
cfinsert tag.
Code Explanation
<cfquery name="AddTrip"
datasource="CompassTravel">
Using the datasource attribute, cfquery connects to the data
source CompassTravel and returns a result set identified by the
name attribute.
INSERT INTO Trips (TripName,
EventType, tripDescription,
tripLocation, departureDate,
returnDate, price,
tripLeader,photo,
baseCost, numberPeople,
depositRequired)
VALUES ( '#Form.TripName#',
#Form.EventType#,
'#Form.tripDescription#',
'#Form.tripLocation#',
'#Form.departureDate#',
'#Form.returnDate#',
#Form.price#,
'#Form.tripLeader#',
'#Form.photo#',
#Form.baseCost#,
Form.numberPeople#,
'#Form.depositRequired#)
The SQL INSERT statement identifies that the data are to be
inserted into the Trips table. The table column names are cited in
a comma-separated list surrounded by parenthesis (
TripName,
EventType....) after the table name Trips.
The
VALUES keyword indicates the list of values that are inserted
into the columns in the same order as the columns are specified
earlier in the statement.
The values refer to form variables passed from the data entry
form to the action page. The variables are surrounded by pound
signs; for example,
#Form.baseCost#. Additionally, note that if
the column data type is a string data type, then the values are
surrounded by single quotation marks; for example:
'#Form.TripName#'.
Attribute Description
datasource The data source name associated with the database where the data is inserted.
tablename The name of the SQL table within the database where the data are inserted.
formfields A comma-separated list of form fields to insert.