![]() ![]() ![]() |
||
|
In Lesson 8: Implementing Browsing and Searching, 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:
cfquery
tag. cfinsert
tag. This approach eliminates the need for you to learn SQL syntax.In this exercise, you use a SQL INSERT statement.
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 SQL INSERT statement has the following format:
INSERT INTO table_name VALUES (value1, value2,....)
For example, the database table named Clients contains information about people in the following rows:
LastName |
FirstName |
Address |
City |
---|---|---|---|
Tom |
Jones |
12 State St |
Boston |
Peter |
Green |
1 Broadway |
New York |
To add a record to the table, use the following statement:
INSERT INTO Clients VALUES ('Smith', 'Kaleigh', '14 Greenway', 'Windham')
After the database management system processes the preceding statement, the table contains the following rows:
LastName |
FirstName |
Address |
City |
---|---|---|---|
Tom |
Jones |
12 State St |
Boston |
Peter |
Green |
1 Broadway |
New York |
Smith |
Kaleigh |
14 Greenway |
Windham |
Notice that the values inserted in the table were surrounded by single-quotation marks in the statement. In SQL, you must surround any text or date values with single-quotation marks; however, you dont use single-quotation marks with numeric values.
Alternatively, you can specify the columns for which you want to insert data. This approach lets you insert data into some columns while omitting others. For this approach, you use the following syntax:
INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)
For example, to add Kaleigh Smith of Windham, with an unknown address, you use the named column approach:
INSERT INTO Clients (LastName, FirstName, City) VALUES ('Smith', 'Kaleigh', 'Windham')
You use the cfquery
tag to execute SQL from ColdFusion. The cfquery
tag passes SQL statements to your data source. As described in Lesson 2: Configuring Your Development Environment, a data source stores information about how to connect to an indicated data provider, such as a relational database management system. The data source that you established in that chapter stored information on how to access the Compass Travel database. The data source name was "CompassTravel."
<cfif isOk EQ "Yes">
tag near the end of the file. After the <H1>Trip Added
</H1>
line, add the following code in the following table to insert the data from the Form variables into the trips table:
For |
Code |
---|---|
Windows users, using MS Access |
<!--- Insert the new trip record into the Compass Travel database. ---> <cfquery name="AddTrip" datasource="compasstravel"> 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#') </cfquery> |
UNIX users, using PointBase |
<!--- Insert the new trip record into the Compass Travel database. ---> <!--- Use local variables to convert dates to JDBC format (yyyy-mm-dd) from input format (mm/dd/yyyy). ---> <cfset JDBCdepartureDate = #Right(Form.departureDate,4)# & "-" & #Left(Form.departureDate,2)# & "-" & #Mid(Form.departureDate,4,2)#> <cfset JDBCreturnDate = #Right(Form.returnDate,4)# & "-" & #Left(Form.returnDate,2)# & "-" & #Mid(Form.returnDate,4,2)#> <cfquery name="AddTrip" datasource="CompassTravel"> INSERT INTO Trips (tripName, eventType, tripDescription, tripLocation, departureDate, returnDate, price, tripLeader, photo, baseCost, numberPeople, depositRequired) VALUES ('#Form.tripName#', #Form.eventType#, '#Form.tripDescription#', '#Form.tripLocation#', Date'#JDBCdepartureDate#', Date'#JDBCreturnDate#', #Form.price#,'#Form.tripLeader#', '#Form.photo#', #Form.baseCost#, #Form.numberPeople#, '#Form.depositRequired#') </cfquery> |
Field |
Value |
---|---|
Trip Name |
NH White Mountains |
Event Type |
Mountain Climbing |
Trip Description |
Climb the 5 highest peaks in the New Hampshire White Mountains. |
Trip Location |
Northeastern New Hampshire |
Departs |
05/01/2005 |
Returns |
05/10/2005 |
Number of People |
15 |
Price |
1200 |
Base Cost |
600 |
Deposit Required |
Yes |
Trip Leader |
Tom Finn |
Photo File Name |
whitemountains.jpg |
After the new trip is written to the database, the following message appears: You have added NH White Mountains to the trips database.
The TripResults page appears.
The following table describes the SQL INSERT and cfquery
code that ColdFusion uses to add data:
Code |
Explanation |
---|---|
<cfquery name="AddTrip" |
Using the |
INSERT INTO Trips (TripName, |
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 The 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, |
For more information about adding data to a database using SQL and the cfquery
tag, see ColdFusion MX Developers Guide. For more information about SQL, consult any SQL primer.
|
||
![]() ![]() ![]() |