Updating Data

You usually use 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 message for the end user that reports on the update completion.

Creating an update form

An update form is similar to an insert form, but there are two key differences:

An update form contains a reference to the primary key of the record that is being updated.
A primary key is a field or combination of fields in a database table that uniquely identifies each record in the table. For example, in a table of employee names and addresses, only the Emp_ID would be unique to each record.
An update form is usually populated with existing record data because the form's purpose is to update 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 new page in ColdFusion Studio.

Edit the page so that it appears as follows:

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

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

<body>

<cfoutput query="GetRecordtoUpdate">
<form action="updateaction.cfm" method="Post">
  <input type="Hidden" name="Emp_ID"
    value="#Emp_ID#"><br>
  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>
  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>
</body>
</html>

Save the page as updateform.cfm.
View updateform.cfm in a browser by specifying the page URL and an Employee ID, for example, http://localhost/myapps/updateform.cfm?Emp_ID=3.

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>
Query the CompanyInfo data source and return the records in which the employee ID matches what was entered in the URL that called this page.

<cfoutput query="GetRecordtoUpdate">
Make the results of the GetRecordtoUpdate query available as variables in the form created on the next line.

<form action="updateaction.cfm" method="Post">
Create a form whose variables will be processed on the updateaction.cfm action page.

<input type="Hidden" name="Emp_ID" value="#Emp_ID#"><br>
Use a hidden input field to pass the employee ID 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>
Populate the fields of the update form. This example does not use any ColdFusion formatting functions to "clean up" the form. As a result, the start dates look like 1985-03-12 00:00:00 and the 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 Contractor field needs special treatment because a check box displays and sets its value. Use the cfif structure to put a check mark in the check box if the Contract field value is Yes, and leave the box empty otherwise.

Code	Description
<cfquery name="GetRecordtoUpdate" datasource="CompanyInfo"> SELECT * FROM Employee WHERE Emp_ID = #URL.Emp_ID# </cfquery>	Query the CompanyInfo data source and return the records in which the employee ID matches what was entered in the URL that called this page.
<cfoutput query="GetRecordtoUpdate">	Make the results of the GetRecordtoUpdate query available as variables in the form created on the next line.
<form action="updateaction.cfm" method="Post">	Create a form whose variables will be processed on the updateaction.cfm action page.
<input type="Hidden" name="Emp_ID" value="#Emp_ID#"><br>	Use a hidden input field to pass the employee ID 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>	Populate the fields of the update form. This example does not use any ColdFusion formatting functions to "clean up" the form. As a result, the start dates look like 1985-03-12 00:00:00 and the 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 Contractor field needs special treatment because a check box displays and sets its value. Use the `cfif` structure to put a check mark in the check box if the Contract field value is Yes, and leave 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 cfupdate, you must include the field or fields that make up the primary key in your form submittal. The cfupdate tag automatically detects the primary key fields in the table that you are updating and looks for them in the submitted form fields. ColdFusion uses the primary key fields to select the record to update. (Therefore, you cannot update the primary key value itself.) It then uses the remaining form fields that are submitted 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 new application page in ColdFusion Studio.

Enter the following code:

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

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

<html>
<head>
  <title>Update Employee</title>
</head>
<body>

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

</body>
</html>

Save the page. as updateaction.cfm.
View updateform.cfm in a browser by specifying the page URL and an Employee ID, for example, http://localhost/myapps/updateform.cfm?Emp_ID=3. Enter new values in any of the fields, and click the Submit button.
The data is updated in the Employee table and the message appears.

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>
If the user clears the Contractor check box, no value gets passed to the action page. Also, the database field must have a value of Yes or No. Test the Form.contract variable and set it to No if it is not defined and Yes if it is defined.

<cfupdate datasource="CompanyInfo" tablename="Employee">
Update the record in the database that matches the primary key on the form (the Emp_ID). Update all fields in the record with names that match the names of controls on the form.

<cfoutput> You have updated the information for #Form.FirstName# #Form.LastName# in the Employees database. </cfoutput>
Inform 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>	If the user clears the Contractor check box, no value gets passed to the action page. Also, the database field must have a value of Yes or No. Test the Form.contract variable and set it to No if it is not defined and Yes if it is defined.
<cfupdate datasource="CompanyInfo" tablename="Employee">	Update the record in the database that matches the primary key on the form (the Emp_ID). Update all fields in the record with names that match the names of controls on the form.
<cfoutput> You have updated the information for #Form.FirstName# #Form.LastName# in the Employees database. </cfoutput>	Inform 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.

To create an update page with cfquery:

Open updatepage.cfm.

Replace the cfupdate tag with the highlighted cfquery code.:

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

<cfquery name="UpdateEmployee" datasource="CompanyInfo">
  UPDATE Employee
  SET FirstName = '#Form.Firstname#',
    LastName = '#Form.LastName#',
    Dept_ID = '#Form.Dept_ID#',
    StartDate = '#Form.StartDate#',
    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 Employees database.
</cfoutput>

Save the page.
View updateform.cfm in a browser by specifying the page URL and an Employee ID, for example, http://localhost/myapps/updateform.cfm?Emp_ID=3. Enter new values in any of the fields, and click Submit.
The data is updated into the Employee table and the message displays.

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 = '#Form.StartDate#', Salary = '#Form.Salary#' WHERE Emp_ID = #Form.Emp_ID# </cfquery>
Update the record in the database that matches the primary key on the form, (Emp_ID). Update all fields in the record with names that match the names of controls on the form. Because #From.Emp_ID# is numeric, you do not enclose it in quotes.

Code	Description
<cfquery name="UpdateEmployee" datasource="CompanyInfo"> UPDATE Employee SET FirstName = '#Form.Firstname#', LastName = '#Form.LastName#', Dept_ID = '#Form.Dept_ID#', StartDate = '#Form.StartDate#', Salary = '#Form.Salary#' WHERE Emp_ID = #Form.Emp_ID# </cfquery>	Update the record in the database that matches the primary key on the form, (Emp_ID). Update all fields in the record with names that match the names of controls on the form. Because #From.Emp_ID# is numeric, you do not enclose it in quotes.

Return to Firstserv.com | ColdFusion Hosting | Macromedia LiveDocs

Developing ColdFusion Applications
Updating Your Database