Adobe ColdFusion 8

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.

  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="cfdocexamples">
    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, enter 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 and time with tenths of seconds, as follows: