MACROMEDIA COLFUSION MX 7-GETTING STARTED BUILDING COLDFUSION..., Getting Started

Page: 135 / 152

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.