Contents > Developing ColdFusion MX Applications > Updating Your Database > Updating data > Creating an action page to update data Creating an update action page with cfquery PreviousNext

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:

  1. 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>
  2. Save the page.
  3. 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.

  4. 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:

This is a picture of the feature being described.

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.
Contents > Developing ColdFusion MX Applications > Updating Your Database > Updating data > Creating an action page to update data Creating an update action page with cfquery PreviousNext

ColdFusion 9 | ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | Bug Reporting

Version 6.1

Comments are no longer accepted for ColdFusion MX 6.1. ColdFusion 8 is the current version.