Programming instructions

Completing the Trip Maintenance application 131
After the following SQL statement executes:
INSERT INTO Clients
VALUES ('Smith', 'Kaleigh', '14 Greenway', 'Windham')
the table contains the following rows:
Notice that the values inserted in the table were surrounded by single quotation marks.
In SQL, you must surround any text or date values with single quotation marks but
numeric values are not.
Alternatively, you can specify the columns for which you want to insert data. This
approach lets you insert data to some columns while omitting others. The syntax for this
approach is as follows:
INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)
For example, the syntax to add Kaleigh Smith of Windham, with the address unknown,
you use the named column approach:
INSERT INTO Clients (LastName, FirstName, City)
VALUES ('Smith', 'Kaleigh', 'Windham')
You used the cfquery tag to execute SQL from ColdFusion. The cfquery tag passes
SQL statements to your data source. As described in Chapter 4, a data source stores
information about how to connect to an indicated data provider, such as a relational
database management system. The data source you established in Chapter 4 stored
information on how to access the Compass Travel database. The data source name was
called “CompassTravel”.
Exercise: insert trip data using SQL INSERT and cfquery
In this exercise you will add code to pass the data entered on the Trip Maintenance
collection form and insert into the Compass Travel database. To do this, you will be
modifying the trip insert action page to use the SQL INSERT statement and the
ColdFusion
cfquery tag.
To add data using SQL INSERT and cfquery:
1 Open tripeditaction.cfm in the my_app directory in your editor.
2 Locate the
<cfif isOk EQ "Yes"> tag near the end of the file. After the <H1> Trip
Added</H1>
line, add the following code to insert the data from the Form variables
into the Trips table.
LastName FirstName Address City
Tom Jones 12 State St Boston
Peter Green 1 Broadway New York
Smith Kaleigh 14 Greenway Windham