Getting Started Building ColdFusion MX Applications
Adding and Updating SQL Data

Completing the Trip Maintenance application

In , 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 , 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:

LastName	FirstName	Address	City
Tom	Jones	12 State St	Boston
Peter	Green	1 Broadway	New York

After the following SQL statement executes:

INSERT INTO Clients

VALUES ('Smith', 'Kaleigh', '14 Greenway', 'Windham')

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 SQL, you must surround any text or date values with single quotation marks but numeric values are not.

Alternatively, you can specify the columns for which you want to insert data. This approach lets you insert data to some columns while omitting others. The syntax for this approach is as follows:

INSERT INTO table_name (column1, column2,...)

VALUES (value1, value2,....)

For example, the syntax to add Kaleigh Smith of Windham, with the address unknown, you use the named column approach:

INSERT INTO Clients (LastName, FirstName, City)

VALUES ('Smith', 'Kaleigh', 'Windham')

You used the cfquery tag to execute SQL from ColdFusion. The cfquery tag passes SQL statements to your data source. As described in Chapter 4, a data source stores information about how to connect to an indicated data provider, such as a relational database management system. The data source you established in Chapter 4 stored information on how to access the Compass Travel database. The data source name was called "CompassTravel".

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 in your browser.
4. In the tripedit.cfm page, fill in the fields with the values in the following figure, then click Save:

   

   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:

   

7. Click Search.

   The TripResults page appears:

   

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:

   

9. Click the Delete button to delete this record so that you can reuse the 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 with CFML. For more information about SQL, consult any SQL primer.

Adding data using the simpler, cfinsert approach

For those who would 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. Formfields is a comma-separated list of form fields to insert. If this attribute is not specified, all fields in the form are included in the operation. The following example uses the cfinsert 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 previous 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 are inserted.
formfields	A comma-separated list of form fields to insert.

Exercise: insert trip data using cfinsert

In this exercise, you change the approach the action page uses to insert the data into the database. You will replace the SQL INSERT statement with the cfinsert tag.

To add data using cfinsert:

1. Open tripeditaction.cfm from the my_app directory in your editor and do the following:
   1. Remove the entire AddTrip cfquery that you added in the last exercise (from the beginning <cfquery name ="AddTrip" datasource="CompassTravel"> tag to the </cfquery> end tag).
   2. 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"> 
      

2. Save the page and test it by opening the tripedit.cfm page in your browser.
3. Follow steps 4 through 9 in the previous exercise to verify this approach to inserting new trips.

For more information about adding data to a database using the cfinsert tag, see Developing ColdFusion MX Applications with CFML.

Updating a SQL row using cfupdate

To update an existing SQL row, ColdFusion offers a simple approach for updating SQL rows through the use of the cfupdate tag. Like cfinsert, the cfupdate tag has datasource and tablename attributes to specify where the data is to be inserted. The tag also has a formfields attribute to identify which fields are to be inserted. Formfields is a comma-separated list of form fields to insert. If this attribute is not specified, all fields in the form are included in the operation.

All the fields of the tripedit.cfm page have corresponding columns in the Trips table, so you can omit the FormFields attribute for both the cfinsert and cfupdate tags. If the tripID form field is passed from the TripEdit page the cfupdate tag is used otherwise the cfinsert tag is executed. The following example uses the cfupdate and cfinsert without the FormFields attribute:

<cfif not isdefined("form.tripID")>

  <cfinsert datasource="CompassTravel" tablename="Trips"> 
    <cflocation url="tripdetail.cfm">
  <cfelse>
  <cfupdate datasource="CompassTravel" tablename="Trips">
    <cflocation url="tripdetail.cfm?ID=#Form.tripID#">

</cfif>

Reviewing the code

The following tables describes the cfinsert and cfupdate code:

Code

Explanation

<cfif not isdefined("form.tripID")> <cfinsert datasource="CompassTravel" tablename="Trips"> <cflocation url="tripdetail.cfm"> <cfelse> <cfupdate datasource="CompassTravel" tablename="Trips"> <cflocation url="tripdetail.cfm?ID=#Form.tripID#"> </cfif>

The ColdFusion function IsDefined determines whether the hidden field tripID was passed to the action page from tripedit.cfm. If there is a current trip, the isDefined function returns True. When there is no current trip, the cfif statement is True. When the cfif statement is True , the cfinsert tag executes and the main page displays with the updated trip. If the cfif statement evaluates to False, the cfinsert statement executes and the first trip displays in the main page.

Exercise: update trip data using cfupdate

In this exercise, you will add the code to update the trip data into the database. You will add the cfupdate tag to the tripeditaction.cfm page.

To update the database using cfupdate:

1. In an editor, open tripeditaction3.cfm from the solutions directory.
2. Review the code to update the database (the last 12 lines of code).
3. Verify that the correct photolocation path is specified. This path is specified in the <cfset PhotoLocation = "C:..."> tag.

   For example, depending on your web server configuration, the photolocation path might be:

   • For MS Windows systems:

       <cfset PhotoLocation
         "C:\cfusionmx\wwwroot\CFDOCS\getting_started\Photos\">
     

     or

     <cfset PhotoLocation = "C:\Inetpub\wwwroot\CFDOCS\getting_started\Photos\">
   • For Linux or Solaris systems:

       <cfset PhotoLocation = "/opt/coldfusionmx/wwwroot/cfdocs/
         getting_started/photos/">
     

     or

     <cfset PhotoLocation = "/<webserverdocroot>/cfdocs/ getting_started/photos/">
4. Save the file as tripeditaction.cfm in the my_app directory.

For more information about adding data to a database using the cfupdate tag, see Developing ColdFusion MX Applications with CFML.

Now that you have built the data entry form adding new and updating existing trips, you will add the logic to link it to the main trip page to test the update logic.

Linking the Trip Edit page to the main page

As discussed in Lesson 4, the action page for the maintenance buttons on the main page is maintenanceaction.cfm. You previously added code for the Search and Delete buttons. In the next exercise you will insert the code to call tripedit.cfm from maintenance.cfm as follows:

<!---      EDIT BUTTON       --->  

<cfelseif IsDefined("Form.btnEdit")>
  <cflocation url="tripedit.cfm?ID=#Form.RecordID#">
<!---      ADD BUTTON       --->  
<cfelseif IsDefined("Form.btnAdd")>
  <cflocation url="tripedit.cfm">

</cfif>

Notice that when the user clicks the Add button, the maintenanceaction.cfm navigates to tripedit.cfm passing no arguments. Conversely, when the user clicks the Edit button, the Trip Edit page passes the current record id. The Trip Edit page must handle both cases. When a RecordID is passed on the URL, tripedit.cfm must query the database and fill the form with the data for the corresponding trip. The following code properly initializes the trip edit form:

<cfif IsDefined("URL.ID")>

  <cfquery name="TripQuery" datasource="CompassTravel" maxrows="1">
    SELECT tripName, eventType, tripDescription, tripLocation,   
    departureDate, returnDate, price, tripLeader, photo, baseCost,
numberPeople, depositRequired, tripID 
    FROM trips        
  <cfif IsDefined("URL.ID")>
    WHERE tripID = #ID#
      </cfif>
  </cfquery>
<!-- Set the local variables -->    
  <cfset tripName = '#TripQuery.tripName#'>    
  <cfset eventType = #TripQuery.eventType#>    
  <cfset tripDescription = '#TripQuery.tripDescription#'>    
  <cfset tripLocation = '#TripQuery.tripLocation#'>    
  <cfset departureDate = DateFormat(#TripQuery.departureDate#,"mm/dd/yyyy")>    
  <cfset returnDate = DateFormat(#TripQuery.returnDate#,"mm/dd/yyyy")>    
  <cfset price = #TripQuery.price#>    
  <cfset tripLeader = '#TripQuery.tripLeader#'>    
  <cfset photo = '#TripQuery.photo#'>    
  <cfset baseCost = #TripQuery.baseCost#>    
  <cfset numberPeople = #TripQuery.numberPeople#>    
  <cfset depositRequired = '#TripQuery.depositRequired#'>
  <cfelse>
  <cfset tripName = ''>    
  <cfset eventType = ''>    
  <cfset tripDescription = ''>    
  <cfset eventTypeIdentifier = #TripQuery.eventType#>
  <cfset tripLocation = ''>    
  <cfset departureDate =  ''>    
  <cfset returnDate = ''>    
  <cfset price = ''>    
  <cfset tripLeader = ''>    
  <cfset photo = ''>    
  <cfset baseCost = ''>    
  <cfset numberPeople = ''>    
  <cfset depositRequired = ''>

</cfif>

Reviewing the code

The following table describes the code used to properly initialize the trip edit form:

Code

Explanation

<cfif IsDefined("URL.ID")> <cfquery name="TripQuery" datasource="CompassTravel" maxrows="1"> SELECT tripName, eventType, tripDescription, tripLocation, departureDate, returnDate, price, tripLeader, photo, baseCost, numberPeople, depositRequired, tripID FROM trips <cfif IsDefined("URL.ID")> WHERE tripID = #ID# </cfif> </cfquery> <!-- Set the local variables --> <cfset tripName = '#TripQuery.tripName#'> <cfset eventType = #TripQuery.eventType#> <cfset tripDescription = TripQuery.tripDescription#'> <cfset tripLocation = '#TripQuery.tripLocation#'> <cfset departureDate = DateFormat(#TripQuery.departureDate#,"mm/dd/yyyy")> <cfset returnDate = DateFormat(#TripQuery.returnDate#,"mm/dd/yyyy")> ... <cfelse> <cfset tripName = ''> <cfset eventType = ''> ... </cfif>

The ColdFusion function IsDefined determines whether an ID argument was passed as part of the invoking URL. The ID argument is passed when TripEdit is invoked when the Edit button is clicked on the main page. When an ID is passed, it is used in the WHERE clause of the SQL SELECT statement to retrieve the information about the current trip. The program then instantiates local variables from the results of the SQL query. ColdFusion DateFormat function formats the date fields. If TripEdit is called to add a new trip, then there is no ID passed as a URL argument. In this case, the local variables are instantiated to blank.

Exercise: linking the Add and Edit buttons

In this exercise you will link the Add and Edit buttons on the Trip Detail page with the Trip Edit page.

To link the add and update buttons on the Trip Detail page:

1. Open maintenanceaction.cfm in the my_app directory in your editor.
2. Locate the </cfif> tag at the end of the file.
3. Insert the following code just before the last line:

   <!---      EDIT BUTTON       --->  
     <cfelseif IsDefined("Form.btnEdit")>
       <cflocation url="tripedit.cfm?ID=#Form.RecordID#">
   <!---      ADD BUTTON       --->  
     <cfelseif IsDefined("Form.btnAdd")>
       <cflocation url="tripedit.cfm">
     </cfif>
   

4. Save maintenanceaction.cfm.
5. Open tripedit4.cfm in the solutions directory in your editor.
6. Open the file initvariables.txt from the solutions directory.
7. Copy the contents of the initvariables.txt and paste it before the <HTML> tag in the tripedit4.cfm page.
8. Save the file as tripedit.cfm in the my_app directory.
9. Test update logic by opening the tripdetail.cfm page in your browser and doing the following tasks:
   1. Click the Edit button.
   2. Double the price of the current trip.
   3. Click Save.

The ColdFusion cfupdate works well for updating a single record. To update several records in a single query, you must use the SQL UPDATE statement in conjuction with cfquery.

SQL Update

The SQL UPDATE statement updates or changes rows in a relational table. The syntax of the UPDATE statement is as follows:

UPDATE table_name SET column_name = new_value

WHERE column_name = some_value

Consider a database table named Clients that contains information about people in the following rows:

PersonID	LastName	FirstName	Age
1	Green	Tom	12
2	Wall	Peter	42
3	Madigan	Jess	20

After the following SQL statement executes:

UPDATE Clients SET LastName = 'Pitt'

WHERE ID = 3

the table contains the following rows:

PersonID	LastName	FirstName	Age
1	Green	Tom	12
2	Wall	Peter	42
3	Pitt	Jess	20

Update several rows

The UPDATE statement updates all rows that meet the criteria found in the WHERE clause. If there is no WHERE clause, every row of the table is updated. After the following SQL statement executes:

UPDATE Clients SET Age = Age + 1

WHERE ID = 3

the table contains the following rows:

PersonID	LastName	FirstName	Age
1	Tom	Green	12
2	Peter	Green	42
3	Pitt	Jess	21

Updating multiple records

The cfupdate statement works well when you want to update the current record within a cfquery. Alternatively, you can update several rows within a table by issuing a single query using cfquery and the SQL UPDATE statement. For example, if the base cost of all trips increased by 5%, you could issue the following query:

<!-- Routine to increase trip base Cost by 5% -->

<cfquery name="TripQuery" dataSource="CompassTravel">
    UPDATE Trips SET baseCost = baseCost * 1.05

</cfquery>

Exercise: using SQL UPDATE with cfquery

In this exercise, you will develop a page to increase the price of every trip by 10%. This page runs only once and is not part of the Trips Maintenance application. This exercise shows how to update many database rows using a single SQL UPDATE statement and the ColdFusion cfquery tag.

To update multiple database rows using SQL UPDATE with cfquery:

1. In an editor, open a new page and save it as priceincrease.cfm in the my_app directory.
2. Remove any lines of code that your editor added.
3. Add the following code:

   <!---Routine to increase trip price by 10% --->
   <cfquery name="TripQuery" dataSource="CompassTravel">
       UPDATE trips SET price = price * 1.1
   </cfquery>
   <cfoutput> New prices are now in effect.</cfoutput>
   

4. Save the page then test the page by doing the following tasks:
   1. Use the Trip Maintenance application to take note of the price of any trip by viewing tripdetail.cfm in a browser.
   2. Test by opening the priceincrease.cfmpage in your browser. This page automatically updates the prices in the trips table.
   3. Use the Trip Maintenance application to verify that the query successfully increased the price of the trip by 10%. To do this, navigate to the tripdetail.cfm and locate the trip you noted in step 4a. The price of this trip is now 10% higher.

Summary

In this lesson you used the cfinsert and cfupdate tags to add and update data to a SQL table. You also have used the SQL UPDATE statement in conjuction with the cfquery tag to effect a trip price increase for all rows in the Trips table.

You have completed the Getting Started tutorial. You should understand how you can combine CFML and SQL to develop powerful applications. When compared with traditional development methods, ColdFusion helps speed the development in hand crafting a database solution like the one in this tutorial. Remarkably, however, depending on the editor or IDE that you use to develop applications, much of the work in this tutorial can be autogenerated using built-in wizards, which simplifies the development process even more.

Copyright © 2002, Macromedia Inc. All rights reserved.

Comments