Exercise 3: Adding data using the cfinsert tag

For developers who prefer not to have to remember SQL syntax to add information to SQL databases, ColdFusion simplifies the coding for inserting SQL rows through the use of the cfinsert tag. As you might expect, the cfinsert tag has datasource and tablename attributes to specify where the data is inserted. The tag also has a formfields attribute to identify which fields to insert. The formfields attribute specifies a comma-separated list of form fields to insert. If this attribute is not specified, all the fields in the form are included in the operation. The following example uses the cfinsert tag with these attributes:

<cfinsert datasource="CompassTravel" tablename="Trips" 
	formfields="tripName, eventType, tripDescription, tripLocation,
	departureDate, returnDate, price, tripLeader, photo, baseCost, 
	numberPeople, depositRequired">

The cfinsert tag used in the preceding code snippet uses the following attributes:

Attribute

Description

datasource

The data source name associated with the database where the data is inserted.

tablename

The name of the SQL table within the database where the data is inserted.

formfields

A comma-separated list of form fields to insert.

To add data using cfinsert:

  1. Open the tripeditaction.cfm file from the my_app directory in your editor.
  2. Remove the entire AddTrip cfquery code block that you added in Exercise 2: Adding trips with SQL INSERT statements:
    	<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>
    
  3. Add the following cfinsert tag to insert data into the trips table in the same location as the code that you just deleted:
    <cfinsert datasource="CompassTravel" tablename="trips"> 
    
  4. 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.

For more information about adding data to a database using the cfinsert tag, see ColdFusion MX Developer’s Guide.