Exercise 6: Inserting values in the edit form

Notice that when the user clicks the Add button, the maintenanceaction.cfm page navigates to the tripedit.cfm page without passing any 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, the tripedit.cfm page must query the database and fill the form with the data for the corresponding trip.

To insert values in the edit form:

  1. Open the tripedit.cfm file in your editor.
  2. Enter or copy and paste the following code after the cfquery block that appears at the top of the file:
    <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 = #url.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#>		
    	<cfif isDefined("TripQuery.depositRequired")>
    		<cfset depositRequired = 'Yes'>
    		<cfelse>
    		<cfset depositRequired = 'No'>
    	</cfif>
    <cfelse>
    	<cfset tripName = ''>		
    	<cfset tripDescription = ''>		
    	<cfset eventTypeIdentifier = #GetEvents.eventType#>
    	<cfset tripLocation = ''>		
    	<cfset departureDate = ''>		
    	<cfset returnDate = ''>		
    	<cfset price = ''>		
    	<cfset tripLeader = ''>		
    	<cfset photo = ''>		
    	<cfset baseCost = ''>		
    	<cfset numberPeople = ''>		
    	<cfset depositRequired = 'Yes'>
    </cfif>
    
  3. To make the appropriate values appear in the form, insert the code that is highlighted in the following table:

    Note: If the user clicks the Add button, the values from the currently displayed trip detail appear in the form; otherwise, all values, except those in the event type field, are blank.

  4. Save the file.

    Code

    Explanation

    <TD><cfinput name= "tripName" 
    	maxlength = "50" 
    	size = "50" 
    	required = "Yes" 
    	value="tripName"
    	message = "Trip name must not be blank">
    </TD>
    

    Set the value of the Trip Name to be blank if the ID is undefined; otherwise, set the value equal to the Trip Name that appeared on the Trip Detail page.

    <TD><cftextarea name="tripDescription" 
    	required="Yes" 
    	message="Trip description must not be blank.">
    	value="#tripDescription#"
    	</cftextarea>
    </TD>
    

    Set the value of the Trip Description to be blank if the ID is undefined; otherwise, set the value equal to the Trip Description that appeared on the Trip Detail page.

    <td><cfselect size="1" name="eventType" required="Yes" 
    	message="Type of event must be selected.">
    	<cfoutput query="GetEvents">
    		<option value= "#GetEvents.eventTypeID#">#GetEvents.eventType#
    	</option>
    	</cfoutput>
    </cfselect>
    </td>
    
    

    Set the value of the Event Type to be the list of events.

    <TD><cfinput size="50" name="tripLocation" 
    	required="Yes"
    	value="#tripLocation#"
    	message="Trip location must not be blank.">
    </TD>
    

    Set the value of the Trip Location to be blank if the ID is undefined; otherwise, set the value equal to the Trip Location that appeared on the Trip Detail page.

    <TD><cfinput name="departureDate" 
    	size="10"
    	required="Yes" 
    	validate="date" 
    	value="#departureDate#"
    	message="Departure date must be a valid date.">
    </TD>
    

    Set the value of the Departure Date to be blank if the ID is undefined; otherwise, set the value equal to the Departure Date that appeared on the Trip Detail page.

    <TD><cfinput name="returnDate"
    	size="10" 
    	required="Yes" 
    	validate="date" 
    	value="#returnDate#"
    	message="Return date must be a valid date.">
    </TD>
    

    Set the value of the Return Date to be blank if the ID is undefined; otherwise, set the value equal to the Return Date that appeared on the Trip Detail page.

    <TD><cfinput name="numberPeople" 
    	size="6"
    	required="Yes" 
    	validate="integer" 
    	value="#numberPeople#"
    	message="The number of people field must be a number and cannot be blank.">
    </TD>
    

    Set the value of the Number of People to be blank if the ID is undefined; otherwise, set the value equal to the Number of People that appeared on the Trip Detail page.

    <TD><cfinput name="price"
    	size="10" 
    	required="Yes" 
    	validate="integer" 
    	value="#price#"
    	message="Price is required and must be numeric.">
    </TD>
    

    Set the value of the Price to be blank if the ID is undefined; otherwise, set the value equal to the Price that appeared on the Trip Detail page.

    <TD><cfinput name="baseCost"
    	size="10" 
    	required="Yes" 
    	validate="integer" 
    	value="#baseCost#"
    	message="Base cost is required and must be numeric.">
    </TD>
    

    Set the value of the Base Cost to be blank if the ID is undefined; otherwise, set the value equal to the Base Cost that appeared on the Trip Detail page.

    <TD><cfinput name="depositRequired"
    	type="checkbox" 
    	value="#depositRequired#" >
    </TD>
    

    Set the value of the Deposit Required to be "Yes" if the ID is undefined; otherwise, set the value equal to the Deposit Required that appeared on the Trip Detail page.

    <TD><cfinput name="tripLeader"
    	maxlength="50" size="50" 
    	required="Yes" 
    	value="#tripLeader#"
    	message="A trip leader must be specified."></TD>
    

    Set the value of the Trip Leader to be blank if the ID is undefined; otherwise, set the value equal to the Trip Leader that appeared on the Trip Detail page.

    <TD><cfinput name="photo"
    	maxlength="50" size="50" 
    	required="Yes" 
    	value="#photo#"
    	message="Valid photo file name must be specified.">
    </TD>
    

    Set the value of the Photo to be blank if the ID is undefined; otherwise, set the value equal to the Photo that appeared on the Trip Detail page.

Reviewing the code

The following table describes the code that ColdFusion uses 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.

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 application then instantiates local variables from the results of the SQL query. The

ColdFusion DateFormat function formats the date fields.

 

 

If the user clicks the Add button to add a new trip, there is no ID passed as a URL argument. In this case, the local variables are instantiated to blank.

To test the modified code:

  1. Open the tripdetail.cfm page in your browser.
  2. Do the following tasks:
    1. Click the Edit button.
    2. Double the price of the current trip.
    3. Click Save.

Summary

In this lesson you used the SQL DELETE statement, and the cfinsert and cfupdate tags to delete, add, and update data to a table.