Contents > Getting Started Building ColdFusion MX Applications > Lesson 6: Adding and Updating SQL Data > Completing the Trip Maintenance application > Writing code to save new trips to the database Exercise: insert trip data using SQL INSERT and cfquery PreviousNext

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.

    Tip: To save time, you can copy this code from the tripsinsertquery.txt file (for Windows users) or from tripinsertqueryunix.txt (for UNIX users) in the solutions directory.

    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 page and test it by opening the tripedit.cfm page in your browser.
  4. In the tripedit.cfm page, fill in the fields with the values in the following figure, then click Save:
    This is a picture of the feature being described.

    After the new trip is written to the database, the following message appears: Trip is added.

  5. To verify that the save worked, open tripsearch.cfm in the my_app directory in your browser.
  6. In the Trip Search page, enter Begins With Nor in the Trip Location criterion value in the Search page as in the following figure:
    This is a picture of the feature being described.
  7. Click Search.

    The TripResults page appears:


    This is a picture of the feature being described.
  8. Click the link to the NH White Mountains to display the details of the trip you just added. Verify that all the fields were saved correctly.

    The following page appears:


    This is a picture of the feature being described.
  9. Click the Delete button to delete this record so that you can reuse steps 4-8 of this exercise in the next exercise.

Reviewing the code

The following table describes the SQL INSERT and cfquery code used to add data:

Code

Explanation

 
<cfquery name="AddTrip"
datasource="CompassTravel">

Using the datasource attribute, cfquery connects to the data source CompassTravel 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 that the data are to be inserted into the Trips table. The table column names are cited in a comma-separated list surrounded by parenthesis (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 passed from the data entry form to the action page. The variables are surrounded by pound signs; for example, #Form.baseCost#. Additionally, note that if the column data type is a string data type, then the values are surrounded by single quotation marks; for example: '#Form.TripName#'.

For more information about adding data to a database using SQL and cfquery, see Developing ColdFusion MX Applications. For more information about SQL, consult any SQL primer.
Contents > Getting Started Building ColdFusion MX Applications > Lesson 6: Adding and Updating SQL Data > Completing the Trip Maintenance application > Writing code to save new trips to the database Exercise: insert trip data using SQL INSERT and cfquery PreviousNext

ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | Bug Reporting

Version 6.1

Comments are no longer accepted for ColdFusion MX 6.1. ColdFusion 8 is the current version.