Exercise 2: Adding trips with SQL INSERT statements

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:

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 don’t 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."

To add data using a SQL INSERT statement and a cfquery tag:

  1. Open the tripeditaction.cfm file in the my_app directory.
  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 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>
    
  3. Save the file.

To test the modified code:

  1. Open the tripedit.cfm page in your browser.
  2. In the tripedit.cfm page, enter in the fields the values in the following table, and then click Save.

    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.

  3. To verify that the trip was saved, open the tripsearchform.cfm page in the my_app directory in your browser.
  4. In the Trip Search page, in the Trip Location drop-down list, select the Begins With option, and enter the value Nor in the text box.
  5. Click Search.

    The TripResults page appears.

  6. Click NH White Mountains to display the details of the trip you just added. Verify that all the fields were saved correctly.
  7. Click the Delete button to delete this record so that you can reuse some of the steps of this exercise in Exercise 3: Adding data using the cfinsert tag.

Reviewing the code

The following table describes the SQL INSERT and cfquery code that ColdFusion uses to add data:

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#'.

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.