System information

Exercise 3: Adding data using the cfinsert tag 135
Reviewing the code
The following table describes the SQL INSERT and cfquery code that ColdFusion uses to add
data:
For more information about adding data to a database using SQL and the
cfquery tag, see
ColdFusion MX Developer’s Guide. For more information about SQL, consult any SQL primer.
Exercise 3: Adding data using the cfinsert tag
For developers who 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. 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. The
following example uses the
cfinsert tag 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 preceding code snippet uses the following attributes:
Code Explanation
<cfquery name="AddTrip"
datasource="CompassTravel">
Using the datasource attribute, the cfquery tag connects to the
CompassTravel data source 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 the data to insert into the
Trips table. The table column names are cited in a comma-
separated list surrounded by parentheses
(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 that are passed from the data
entry form to the action page. The variables are surrounded by
number signs; for example,
#Form.baseCost#. Also, if the column
data type is a String data type, 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 is inserted.
formfields A comma-separated list of form fields to insert.