User Guide

110 Chapter 7 Updating Your Database
Reviewing the code
The following table describes the code and its function:
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:
1Open updatepage.cfm.
2Replace 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>
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.