Creating Update Action Page

The action page that you build to update data is very similar to the action page that you build to insert data. The update action page needs to include:

• Conditional logic to check for the existence of checkbox and radio button values to ensure you have all the values that you need for the update later on the page.
• A query to perform the actual data update.
• Some code to redirect users to a listing page so that they can confirm the changes that they make to the database.

You'll use the CFLOCATION tag to redirect users after the query insertion.

Building a query to update data

The CFQUERY tag supports a number of SQL statements that you use to insert, update, and delete records in a table.

For example, you'll use the UPDATE SQL statement, and SET and WHERE clauses to describe:

• The table that you want to update
• Each table column by a variable that will set the update value
• The record to update

SQL UPDATE syntax

<CFQUERY NAME="QueryName" DATASOURCE="DataSourceName">
    UPDATE Table
    SET
    ColumnName='UpdateValue',
    ColumnName='UpdateValue',
    ...
    WHERE PrimaryKey=UpdateValue
</CFQUERY>

• The UPDATE statement describes the table to update.
• The SET clause describes the values to update.
• Separate multiple UpdateValue entries with commas (,).
• Surround an UpdateValue with single quotes(') when it is a string value.
• Do not surround an UpdateValue with single quotes when it is a numeric value.
• Use the WHERE clause so that the statement updates one record rather than all records.
• There is a one-to-one correspondence among columns and UpdateValue(s).

Note:

The purpose of this guide is to get you up and running with ColdFusion Express. Allaire suggests that you purchase a book on SQL to learn how to use it efficiently.

Updating data usage example

This code updates a particular employee in the Employees table:

<CFQUERY NAME="UpdateEmployee" DATASOURCE="HRApp">
    UPDATE Employees
    SET
    FirstName='#Form.FirstName#',
    LastName='#Form.LastName#',
    Contract='#Form.Contract#'
    WHERE 
    Employee_ID=#Form.Employee_ID#
</CFQUERY>

• The update values are variables passed from a form page.
• There is a one-to-one correspondence between the columns and the values named in the UPDATE statement.

To build an update action page:

1. Create a new application page in HomeSite.
2. Save the page as UpdateAction.cfm.
3. Title the page Chapter 10 - Update Employee Action Page.
4. Set a local variable to yes if the Form.Contract variable was passed from UpdateForm.cfm and NO if it was not passed:

<CFIF IsDefined("Form.Contract") IS "Yes">
    <CFSET ContractStatus="Yes">
<CFELSE>
    <CFSET ContractStatus="No">
</CFIF>

5. Create a query named UpdateEmployee to update data in the HRExpress database:

<CFQUERY NAME="UpdateEmployee" DATASOURCE="HRExpress">
</CFQUERY>

6. Within the CFQUERY block, create a SQL statement to update data in the Employees table using the information passed from UpdateForm.cfm:

UPDATE Employees
SET  FirstName = '#Form.FirstName#',LastName = '#Form.LastName#', 
Department_ID = #Form.Department_ID#,
StartDate = #Form.StartDate#, Salary = #Form.Salary#,
Contract = '#ContractStatus#'
WHERE   Employee_ID = #Form.Employee_ID#

7. After the CFQUERY block, redirect users to EmpList.cfm.

<CFLOCATION URL="EmpList.cfm">

8. Save the page.
9. View UpdateList.cfm in a browser.
10. Select an employee to update.
11. Change information and submit the form.

UpdateAction.cfm updates the information in the database and redirects users to EmpList.cfm so that they can confirm the changes.

Click here to see how the update list, form and action page should work.

Click here to see UpdateAction.cfm's code.

You have completed the HR Manager Application. Move on to the chapter summary.

Copyright © 1999, Allaire Corporation. All rights reserved.