Updating data

You usually use the following two application pages to update data in a database:

An update form
An update action page

You can create an update form with cfform tags or HTML form tags. The update form calls an update action page, which can contain either a cfupdate tag or a cfquery tag with a SQL UPDATE statement. The update action page should also contain a confirmation message for the end user.

Creating an update form

The following are the key differences between an update form and an insert form:

An update form contains a reference to the primary key of the record that is being updated.
A primary key is a field(s) in a database table that uniquely identifies each record. For example, in a table of employee names and addresses, only the Emp_ID is unique to each record.
An update form is usually populated with existing record data.

The easiest way to designate the primary key in an update form is to include a hidden input field with the value of the primary key for the record you want to update. The hidden field indicates to ColdFusion which record to update.

To create an update form:

Create a ColdFusion page with the following content:

<html>
<head>
<title>Update Form</title>
</head>

<body>
<cfquery name="GetRecordtoUpdate"
  datasource="CompanyInfo">
  SELECT * FROM Employee
  WHERE Emp_ID = #URL.Emp_ID#
</cfquery>


<cfoutput query="GetRecordtoUpdate">
<table>
<form action="update_action.cfm" method="Post">
  <input type="Hidden" name="Emp_ID"
    value="#Emp_ID#"><br>
<tr>
  <td>First Name:</td> 
  <td><input type="text" name="FirstName" value="#FirstName#"></td>
</tr>
<tr>
  <td>Last Name:</td>
  <td><input type="text" name="LastName" value="#LastName#"></td>
</tr>
<tr>
  <td>Department Number:</td>
  <td><input type="text" name="Dept_ID" value="#Dept_ID#"></td>
</tr>
<tr>
  <td>Start Date:</td>
  <td><input type="text" name="StartDate" value="#StartDate#"></td>
</tr>
<tr>
  <td>Salary:</td>
  <td><input type="text" name="Salary" value="#Salary#"></td>
</tr>
<tr>
  <td>Contractor:</td>
  <td><cfif #Contract# IS "Yes">
    <input type="checkbox" name="Contract" checked>Yes
  <cfelse>
    <input type="checkbox" name="Contract">Yes
  </cfif></td>
</tr>
<tr>
  <td>&nbsp;</td>
  <td><input type="Submit" value="Update Information"></td>
</tr>
</form>
</table>
</cfoutput>

</body>
</html>

Save the file as update_form.cfm.
View update_form.cfm in your web browser by specifying the page URL and an Employee ID; for example, enter the following:
http://localhost/myapps/update_form.cfm?Emp_ID=3
Note: Although you can view an employee's information, you must code an action page before you can update the database. For more information, see "Creating an action page to update data".

Reviewing the code

The following table describes the code and its function:

Code

Description

<cfquery name="GetRecordtoUpdate" datasource="CompanyInfo"> SELECT * FROM Employee WHERE Emp_ID = #URL.Emp_ID# </cfquery>

Queries the CompanyInfo data source and returns records in which the employee ID matches what was entered in the URL that called this page.

<cfoutput query="GetRecordtoUpdate"> ... </cfoutput>

Makes available as variables the results of the GetRecordtoUpdate query in the form created in subsequent lines.

<form action="update_action.cfm" method="Post"> ... </form>

Creates a form whose variables will be processed on the update_action.cfm action page.

<input type="Hidden" name="Emp_ID" value="#Emp_ID#"><br>

Uses a hidden input field to pass the Emp_ID (primary key) value to the action page.

First Name: <input type="text" name="FirstName" value="#FirstName#"><br> Last Name: <input type="text" name="LastName" value="#LastName#"><br> Department Number: <input type="text" name="Dept_ID" value="#Dept_ID#"><br> Start Date: <input type="text" name="StartDate" value="#StartDate#"><br> Salary: <input type="text" name="Salary" value="#Salary#"><br>

Populates the fields of the update form. This example does not use ColdFusion formatting functions. As a result, start dates look like 1985-03-12 00:00:00 and salaries do not have dollar signs or commas. The user can replace the information in any field using any valid input format for the data.

Contractor: <cfif #Contract# IS "Yes"> <input type="checkbox" name="Contract" checked>Yes<br> <cfelse> <input type="checkbox" name="Contract"> Yes <br> </cfif> <br> <input type="Submit" value="Update Information"> </form> </cfoutput>

The Contract field requires special treatment because a check box displays and sets its value. The cfif structure puts a check mark in the check box if the Contract field value is Yes, and leaves the box empty otherwise.

Code	Description
<cfquery name="GetRecordtoUpdate" datasource="CompanyInfo"> SELECT * FROM Employee WHERE Emp_ID = #URL.Emp_ID# </cfquery>	Queries the CompanyInfo data source and returns records in which the employee ID matches what was entered in the URL that called this page.
<cfoutput query="GetRecordtoUpdate"> ... </cfoutput>	Makes available as variables the results of the GetRecordtoUpdate query in the form created in subsequent lines.
<form action="update_action.cfm" method="Post"> ... </form>	Creates a form whose variables will be processed on the update_action.cfm action page.
<input type="Hidden" name="Emp_ID" value="#Emp_ID#"><br>	Uses a hidden input field to pass the Emp_ID (primary key) value to the action page.
First Name: <input type="text" name="FirstName" value="#FirstName#"><br> Last Name: <input type="text" name="LastName" value="#LastName#"><br> Department Number: <input type="text" name="Dept_ID" value="#Dept_ID#"><br> Start Date: <input type="text" name="StartDate" value="#StartDate#"><br> Salary: <input type="text" name="Salary" value="#Salary#"><br>	Populates the fields of the update form. This example does not use ColdFusion formatting functions. As a result, start dates look like 1985-03-12 00:00:00 and salaries do not have dollar signs or commas. The user can replace the information in any field using any valid input format for the data.
Contractor: <cfif #Contract# IS "Yes"> <input type="checkbox" name="Contract" checked>Yes<br> <cfelse> <input type="checkbox" name="Contract"> Yes <br> </cfif> <br> <input type="Submit" value="Update Information"> </form> </cfoutput>	The Contract field requires special treatment because a check box displays and sets its value. The `cfif` structure puts a check mark in the check box if the Contract field value is Yes, and leaves the box empty otherwise.

Creating an action page to update data

You can create an action page to update data with either the cfupdate tag or cfquery with the UPDATE statement.

Creating an update action page with cfupdate

The cfupdate tag is the easiest way to handle simple updates from a front-end form. The cfupdate tag has an almost identical syntax to the cfinsert tag.

To use the cfupdate tag, you must include the primary key field(s) in your form submittal. The cfupdate tag automatically detects the primary key field(s) in the table that you are updating and looks for them in the submitted form fields. ColdFusion uses the primary key field(s) to select the record to update (therefore, you cannot update the primary key value itself). It then uses the remaining form fields that you submit to update the corresponding fields in the record. Your form only needs to have fields for the database fields that you want to change.

To create an update page with cfupdate:

Create a ColdFusion page with the following content:

<html>
<head>
  <title>Update Employee</title>
</head>
<body>
<cfif not isdefined("Form.Contract")>
  <cfset form.contract = "No">
<cfelse>
  <cfset form.contract = "Yes">
</cfif>

<cfupdate datasource="CompanyInfo"
  tablename="Employee">

<h1>Employee Updated</h1>
<cfoutput>
You have updated the information for #Form.FirstName#
#Form.LastName# in the employee database.
</cfoutput>

</body>
</html>

Save the page as update_action.cfm.
View update_form.cfm in your web browser by specifying the page URL and an Employee ID; for example, enter the following:
http://localhost/myapps/update_form.cfm?Emp_ID=3
The current information for that record appears:
Enter new values in any of the fields, and click Update Information.
ColdFusion updates the record in the Employee table with your new values and displays a confirmation message.

Reviewing the code

The following table describes the code and its function:

Code

Description

<cfif not isdefined("Form.Contract")> <cfset Form.contract = "No"> <cfelse> <cfset form.contract = "Yes"> </cfif>

Sets the value of Form.Contract to No if it is not defined, or to Yes if it is defined. If the Contractor check box is unchecked, no value is passed to the action page; however, the database field must have some value.

<cfupdate datasource="CompanyInfo" tablename="Employee">

Updates the record in the database that matches the primary key on the form (Emp_ID). Updates all fields in the record with names that match the names of form controls.

<cfoutput> You have updated the information for #Form.FirstName# #Form.LastName# in the employee database. </cfoutput>

Informs the user that the change was made successfully.

Code	Description
<cfif not isdefined("Form.Contract")> <cfset Form.contract = "No"> <cfelse> <cfset form.contract = "Yes"> </cfif>	Sets the value of Form.Contract to No if it is not defined, or to Yes if it is defined. If the Contractor check box is unchecked, no value is passed to the action page; however, the database field must have some value.
<cfupdate datasource="CompanyInfo" tablename="Employee">	Updates the record in the database that matches the primary key on the form (Emp_ID). Updates all fields in the record with names that match the names of form controls.
<cfoutput> You have updated the information for #Form.FirstName# #Form.LastName# in the employee database. </cfoutput>	Informs the user that the change was made successfully.

Creating an update action page with cfquery

For more complicated updates, you can use a SQL UPDATE statement in a cfquery tag instead of a cfupdate tag. The SQL UPDATE statement is more flexible for complicated updates.

The following procedure assumes that you have created the update_action.cfm page as described in "Creating an update action page with cfupdate".

To create an update page with cfquery:

In update_action.cfm, replace the cfupdate tag with the following highlighted cfquery code:

<html>
<head>
  <title>Update Employee</title>
</head>
<body>
<cfif not isdefined("Form.Contract")>
  <cfset form.contract = "No">
<cfelse>
  <cfset form.contract = "Yes">
</cfif>

<!--- cfquery requires date formatting when retrieving from 
Access. Use the left function when setting StartDate to trim
the ".0" from the date when it first appears from the 
Access database --->
 <cfquery name="UpdateEmployee" datasource="CompanyInfo">
  UPDATE Employee
  SET FirstName = '#Form.Firstname#',
    LastName = '#Form.LastName#',
    Dept_ID = #Form.Dept_ID#,
    StartDate = '#left(Form.StartDate,19)#',
    Salary = #Form.Salary#
  WHERE Emp_ID = #Form.Emp_ID#
</cfquery>

<h1>Employee Updated</h1>
<cfoutput>
You have updated the information for 
#Form.FirstName# #Form.LastName# 
in the employee database.
</cfoutput>
</body>
</html>

Save the page.
View update_form.cfm in your web browser by specifying the page URL and an Employee ID; for example, type the following:
http://localhost/myapps/update_form.cfm?Emp_ID=3.
Enter new values in any of the fields, and click Update Information.
ColdFusion updates the record in the Employee table with your new values and displays a confirmation message.

When the cfquery tag retrieves date information from a Microsoft Access database, it displays the date with tenths of seconds, as follows:

Date information including tenths of seconds.

This example uses the left function to trim the two final characters. The CompanyInfo data source connects to company.mdb.

Reviewing the code

The following table describes the highlighted code and its function:

Code

Description

<cfquery name="UpdateEmployee" datasource="CompanyInfo"> UPDATE Employee SET FirstName = '#Form.Firstname#', LastName = '#Form.LastName#', Dept_ID = #Form.Dept_ID#, StartDate = '#left(Form.StartDate,19)#', Salary = #Form.Salary# WHERE Emp_ID = #Form.Emp_ID# </cfquery>

Updates the specified columns in the record in the Employee table of the CompanyInfo database that matches the primary key (Emp_ID).

Because #Form.Dept_ID#, #Form.Salary#, and #Form.Emp_ID# are numeric, they do not need to be enclosed in quotation marks.

Because of the way cfquery gets and displays dates from Access databases, you use the left function to trim the returned value.

Developing ColdFusion MX Applications with CFML
Updating Your Database

Updating data

Creating an update form

To create an update form:

Reviewing the code

Creating an action page to update data

Creating an update action page with cfupdate

To create an update page with cfupdate:

Reviewing the code

Creating an update action page with cfquery

To create an update page with cfquery:

Reviewing the code

Comments