Exercise 1: Deleting the current trip record shown on the Trip Detail page

In Lesson 8: Implementing Browsing and Searching, you created the tripeditaction.cfm page to contain server side edits for the trip edit data entry form. In this lesson, you complete the tripeditaction.cfm page.

Before you can write the code to delete a trip, you must understand the underlying SQL statement to delete rows from the trips table.

The SQL DELETE statement removes existing rows in a relational table. The DELETE statement has the following format:

DELETE FROM table_name WHERE column_name = some_value

For example, the database table named Clients contains holds information about people, in the following rows:

LastName

FirstName

Address

City

Jones

Tom

50 Main St

New York

Adamson

Anita

521 Beacon St

Boston

Green

Peter

1 Broadway

New York

To delete everyone from New York from the table, use the following statement:

DELETE FROM Clients WHERE City = 'New York'

After the database management system processes the preceding statement, the table contains the following row only:

LastName

FirstName

Address

City

Adamson

Anita

521 Beacon St

Boston

To ensure that the Trip Maintenance application deletes only the proper trip, you must use the unique tripID key when issuing the SQL DELETE statement. The RecordID field holds the tripID. Using the hidden RecordID input tag from the Trip Detail page, the following SQL statement deletes a row from the Trips table:

DELETE FROM trips WHERE tripID = #Form.RecordID#

To enable users to delete trips:

  1. Open the maintenanceaction.cfm file in the my_app directory in your editor.
  2. Add the highlighted code in the file.
    <cfif IsDefined("Form.btnSearch")>
    	<!--- Code to execute if the user clicked Search. --->
    	<cflocation url="tripsearchform.cfm">
    <cfelseif IsDefined("Form.btnDelete")>
    	<!--- Code to execute if the user clicked Delete. --->
    	<cfquery name="DeleteRecord" datasource="CompassTravel">
    		DELETE FROM trips WHERE tripID = #Form.RecordID#
    	</cfquery>
    <cflocation url="tripdetail.cfm">
    <cfelseif IsDefined("Form.btnEdit")>
    	<!--- Code to execute if the user clicked Edit. --->
    <cfelseif IsDefined("Form.btnAdd")>
    	<!--- Code to execute if the user clicked Add. --->
    </cfif>
    
  3. Save the page.

To test the delete capability:

  1. View the tripdetail.cfm page in a browser.

    The current trip is Nepal. Notice that the destination for the Nepal Summit Challenge trip is Imji Himal, Nepal.

  2. Click Search.

    The Trip Search page appears.

  3. In the Trip Search page, select Begins With in the selection box for Trip Location.
  4. Enter Imji in the Trip Location field.
  5. Click Search.
  6. Verify that only one trip is found whose location begins with Imji.
  7. To return to the Trip Detail page for this trip, click Nepal Summit Challenge
  8. In the Trip Detail page, click Delete.
  9. Click Search.
  10. Select Beings With in the selection box for Trip Location.
  11. Enter Imji in the Trip Location field.
  12. Click Search.

    There should be no records, because you deleted the trip.