426
DEVELOPING
COLDFUSION 9 APPLICATIONS
Accessing and Using Data
Las
t
upda
te
d 8/5/2010
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
” on page 424.
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:
Deleting data
You use a
cfquery
tag with a SQL DELETE statement to delete data from a database. ColdFusion has no
cfdelete
tag.