User Guide

118 Chapter 10: Lesson 6: Adding and Updating SQL Data
Completing the Trip Maintenance application
In Lesson 5: Implementing the Browsing and Maintenance Database Functions, you created the
tripeditaction.cfm page to contain server side edits for the trip edit data entry form. In this final
lesson, you will complete the tripeditaction.cfm page. To complete the action page, you will write
code to do these tasks:
• Add trips to the Compass Travel database using the cfquery tag and the SQL INSERT
statement.
• Add trips to the Compass Travel database using the cfinsert tag.
• Update the current trip using the cfupdate tag.
• Link the current trip to be updated or added by the tripeditaction.cfm with the tripedit.cfm
page.
In addition to completing the Trip Maintenance application, you will develop a ColdFusion page
to update all the prices in the database using the cfquery tag and the SQL UPDATE statement.
Writing code to save new trips to the database
In Lesson 5: Implementing the Browsing and Maintenance Database Functions, you built a Trip
Edit page to collect the data. Now you can modify the Trip Edit action page to insert the data into
the database. There are two approaches to inserting data into a SQL database:
• Build a SQL INSERT statement and execute it using the cfquery tag.
• Use the ColdFusion cfinsert tag. This approach eliminates the need for you to learn SQL
syntax.
Adding data using SQL INSERT with cfquery approach
In previous lessons, you used the SQL SELECT statement to retrieve data and the SQL DELETE
statement to delete data from the Trips table in the Compass Travel database. To add new trips to
the database using SQL, you must understand the syntax of the SQL INSERT statement.
The SQL INSERT statement inserts new rows into a relational table. The format of the INSERT
statement is as follows:
INSERT INTO table_name
VALUES (value1, value2,....)
The database table named Clients contains information about people in the following rows:
After the following SQL statement executes:
INSERT INTO Clients
VALUES ('Smith', 'Kaleigh', '14 Greenway', 'Windham')
LastName FirstName Address City
Tom Jones 12 State St Boston
Peter Green 1 Broadway New York