Managing Data

In this lesson of the ColdFusion tutorial, you will learn...
  1. To display data retrieved from a database.
  2. To insert data into a database.
  3. To update existing data in a database.
  4. To delete data from a database.
  5. To manage site permissions through assigning users to roles.

Creating a User Administration Page

In this section, you will learn how to create a user administration page that will display all of the users' information within a table structure. The page will consist of multiple forms that allow you to edit user information, delete users and add new users. The page will look like this:

Displaying Users

The page is divided into two parts:

  1. A section for adding a new user.
  2. A section for displaying, editing and deleting existing users.

This first demo shows how to perform the query and display the results as form fields. Note the use of the query attribute in the <cfoutput> tag. This instructs ColdFusion to treat the <cfoutput> like a <cfloop>, looping through each record returned by the query.

Code Sample: ManagingData/Demos/Admin.cfm

<cfquery name="getUsers" datasource="#APPLICATION.datasource#">
 SELECT userid, firstname, lastname, email, role
 FROM Users
</cfquery>

<html>
<head>
<title>Admin Page</title>
<style>
 .admin {color:#990000}
 .user {color:#000000}
</style>
</head>

<body>
<cfoutput>
<form method="post" action="#CGI.SCRIPT_NAME#">
</cfoutput>
<table cellpadding="3" cellspacing="0" id="maintable" width="700">
<tr>
 <th>First Name</th>
 <th>Last Name</th>
 <th>Email</th>
 <th>Role</th>
 <th colspan="2">Action</th>
</tr>
<tr>
 <td><input type="text" name="firstname" size="15"></td>
 <td><input type="text" name="lastname" size="15"></td>
 <td><input type="text" name="email" size="30"></td>
 <td>
  <select name="role">
   <option value="admin" class="admin">Admin</option>
   <option value="user" selected class="user">User</option>
  </select>
 </td>
 <td colspan="2" align="center" width="120">
  <input name="add" type="submit" value="Add User" style="font-size:xx-small">
 </td>
</tr>
<tr><td colspan="6"><hr></td></tr>
</table>
</form>
<cfoutput query="getUsers">
<form method="post" style="margin:0px" action="#CGI.SCRIPT_NAME#">
 <table width="700">
  <tr>
  <td>
  <input type="text" name="firstname" value="#firstname#" size="15" class="#role#">
  </td>
  <td>
  <input type="text" name="lastname" value="#lastname#" size="15" class="#role#">
  </td>
  <td>
  <input type="text" name="email" value="#email#" size="30" class="#role#">
  </td>
  <td>
   <cfif role EQ "admin">
    <cfset adminselected =  "selected">
    <cfset userselected = "">
   <cfelse>
    <cfset userselected = "selected">
    <cfset adminselected =  "">
   </cfif>
   <select name="role">
    <option value="admin" #adminselected# class="admin">Admin</option>
    <option value="user" #userselected# class="user">User</option>
   </select>
  </td>
  <td width="120">
  <input type="submit" value="EDIT" style="font-size:xx-small" class="#role#"> 
  <input type="submit" value="DELETE" style="font-size:xx-small" class="#role#">
  </td>
  </tr>
 </table>
</form>
</cfoutput>
<cfinclude template="Includes/Footer.cfm">
</body>
</html>

Adding Users

We will now add code to try to insert users when the Add User button is pressed. As we'll be processing edits and deletes with this same page, we must detect not only that a form has been submitted, but which form it was.

We will want to email the new user an auto-generated password, so we will include Includes/Functions.cfm, which contains generatePassword() and sendPassword() functions.

Code Sample: ManagingData/Demos/Admin2.cfm

<cfinclude template="Includes/Functions.cfm">

<cfif isDefined("FORM.inserting")>
 <cfset password=GeneratePassword(10)>
 <cfset sendPassword(FORM.email,password)>
  
 <cfquery datasource="#APPLICATION.datasource#">
  INSERT INTO Users
  (email, password, firstname, lastname, role)
  VALUES ('#FORM.email#', '#password#','#FORM.firstname#','#FORM.lastname#','#FORM.role#')
 </cfquery>
</cfif>
 ---- Code Omitted ----

Editing and Deleting Users

Now we will add the code to edit and delete users. Note that we need to be able to determine which submit button was pushed. We do this by giving the submit buttons names. The name-value pair of the button that is pressed will be sent to the server.

Code Sample: ManagingData/Demos/Admin3.cfm

<cfinclude template="Includes/Functions.cfm">

<cfif isDefined("FORM.inserting")>
 <cfset password=GeneratePassword(10)>
 <cfset sendPassword(FORM.email,password)>
 <cfquery datasource="#APPLICATION.datasource#">
  INSERT INTO Users
  (email, password, firstname, lastname, role)
  VALUES ('#FORM.email#', '#password#','#FORM.firstname#','#FORM.lastname#','#FORM.role#')
 </cfquery>
<cfelseif isDefined("FORM.editing")>
 <cfquery datasource="#APPLICATION.datasource#">
  UPDATE Users
  SET firstname='#FORM.firstname#',
   lastname='#FORM.lastname#',
   email='#FORM.email#',
   role='#FORM.role#'
  WHERE userid=#FORM.userid#
 </cfquery>
<cfelseif isDefined("FORM.deleting")>
 <cfquery datasource="#APPLICATION.datasource#">
  DELETE FROM Users
  WHERE userid=#FORM.userid#
 </cfquery>
</cfif>
 ---- Code Omitted ----

Controlling Access to the Page

As we only want users who are admins to be able to view this page, we need to check the user's role before displaying the page. We do this by checking SESSION.role.

Note that we also had to add code to Login.cfm to set the SESSION.role variable as shown in the files below.

Code Sample: ManagingData/Demos/Login.cfm

<cfif isDefined("FORM.submitted")>
 <cfquery name="logincheck" datasource="#APPLICATION.datasource#">
  SELECT firstname, lastname, userid, role
  FROM Users
  WHERE email='#FORM.email#'
    AND password='#FORM.password#'
 </cfquery>
 <cfif logincheck.RecordCount EQ 1>
  <cfset SESSION.firstname = logincheck.firstname>
  <cfset SESSION.lastname = logincheck.lastname>
  <cfset SESSION.userid = logincheck.userid>
  <cfset SESSION.role = logincheck.role>
  <cfif isDefined("FORM.rememberme")>
   <cfcookie name="loggedin" value="#logincheck.userid#" expires="7">
  </cfif>
  <cflocation url="index.cfm" addtoken="no">
 <cfelse>
  <cfset badlogin=true>
 </cfif>
</cfif>
<cfparam name="FORM.email" default="">
<html>
<head>
<title>Login Page</title>
</head>
<body>
 <h2>Log in</h2>
 <cfif isDefined("badlogin")>
  <p class="errors"><b>That is not the
   correct email and password. Please
   <a href="Login.cfm">try again</a>.</p>
 </cfif>
 <cfoutput>
 <form method="post" action="#CGI.SCRIPT_NAME#">
 </cfoutput>
 <input type="hidden" name="submitted" value="true">
  <table>
  <tr>
   <td>Email:</td>
   <td><input type="text" name="email" value="<cfoutput>#FORM.email#</cfoutput>" size="40"></td>
  </tr>
  <tr>
   <td>Password:</td>
   <td>
   <input type="password" name="password" size="14">
   </td>
  </tr>
  <tr>
   <td colspan="2">
   <input type="checkbox" name="rememberme"> Remember Me
   </td>
  </tr>
  <tr>
   <td align="right" colspan="2">
   <input type="submit" value="Log in">
   </td>
  </tr>
  </table>
 </form>
<cfinclude template="Includes/Footer.cfm">

</body>
</html>

Aborting the Page for Unpermitted Visitors

We can abort the execution of a page when users who are not permitted try to access it with the <cfabort> tag.

Code Sample: ManagingData/Demos/Admin4.cfm

<cfif NOT isDefined("SESSION.role") or SESSION.role NEQ "admin">
 You do not have permissions to view this page.
 <cfabort>
</cfif>
 ---- Code Omitted ----

Providing Convenient Access to the Admin Page

To make it convenient to access the admin page, we added a link to the footer that only appears when an "admin" user is logged in.

Code Sample: ManagingData/Demos/Includes/Footer.cfm

<br><hr width="350" align="right">
<div align="right" id="copyright">&copy; 2007 Runners Home.
All rights reserved.
<a href="index.cfm">Home</a> |
<a href="Races.cfm">Races</a> |
<a href="Resources.cfm">Resources</a> |
<cfif isDefined("SESSION.userid")>
 <a href="Logout.cfm">Log out</a>
<cfelse>
 <a href="Login.cfm">Log in</a>
</cfif>
<cfif isDefined("SESSION.role") AND SESSION.role EQ "admin">
  | <a href="Admin4.cfm">Admin</a>
</cfif>
</div>

To see how the applicatoin now works:

  • Open ManagingData/Demos/index.cfm and log in as George Washington (gwashington@whitehouse.gov / password). You should have a link on the footer to the Admin page. Click the link. You should be allowed access.
  • Click the Logout link on the footer. You should be logged out and returned to the home page.
  • Log in again as John Quincy Adams (jqadams@whitehouse.gov / password). The Admin link should not be present.

Important Note

The admin page has a very serious problem. It allows an admin to delete himself or to change his status from admin to user. This could create a situation in which there were no admins left to administer the users. In production code, you would want to take measures to prevent this from happening.

Allowing Users to Add and Edit Data

Displaying Data

In this section, you will learn how to display records from a database and control read/write access to individual records based on a user’s role and his relationship to the record. The following rules will apply:

  1. All users will be able to see active races.
  2. All logged-in users will be able to add races.
  3. "User" users will only be able to edit and delete races that they added themselves.
  4. "Admin" users will be able to see, edit, and delete all races.

The interface will look like this when a "User" user logs in:

This first demo displays all the races, but they are not yet sortable. It also allows all users to edit all data.

Code Sample: ManagingData/Demos/Races.cfm

<cfquery name="getraces" datasource="#APPLICATION.datasource#">
 SELECT raceid, racename, racetime, distance, city,
   state, active, u.userid, firstname, lastname, email
 FROM Users u, Races r
 WHERE u.userid = r.userid
  AND active=1
</cfquery>

<html>
<head>
<title>Running Races</title>
</head>
<body>
<table border="1" cellpadding="3" cellspacing="0" id="maintable">
<tr>
 <th>Race</th>
 <th>Location</th>
 <th>Distance</th>
 <th>Date & Time</th>
 <th>Added by</th>
 <th>Edit/Delete</th>
</tr>
<cfoutput query="getraces">
<tr>
 <td>#racename#</td>
 <td>#city#, #state#</td>
 <td>#distance#</td>
 <td>#DateFormat(racetime, "ddd, mmm d, yyyy")# at #TimeFormat(racetime,"h:mm tt")#</td>
 <td><a href="mailto:#email#">#firstname# #lastname#</a></td>
 <td>
 <form method="post" action="RaceEdit.cfm" style="margin-bottom:0px">
  <input type="hidden" name="raceid" value="#raceid#">
  <input type="submit" value="EDIT" style="font-size:xx-small">
  <input type="submit" name="deleting" value="DELETE" style="font-size:xx-small">
 </form>
 </td>
</tr>
</cfoutput>
</table>
<cfinclude template="Includes/Footer.cfm">
</body>
</html>

Controlling Access to Individual Records

We will add code that prevents users from editing other people's records.

We will also add code to allow any user who is logged in to add a race.

Code Sample: ManagingData/Demos/Races2.cfm

<cfif isDefined("SESSION.userid")>
 <cfset userid = SESSION.userid>
<cfelse>
 <cfset userid =0>
</cfif>

<cfquery name="getraces" datasource="#APPLICATION.datasource#">
 SELECT raceid, racename, racetime, distance, city,
   state, active, u.userid, firstname, lastname, email
 FROM Users u, Races r
 WHERE u.userid = r.userid
  AND active=1
</cfquery>

<html>
<head>
<title>Running Races</title>
</head>
<body>
<cfif userid NEQ 0>
 <a href="RaceAdd.cfm">Add a race</a>
</cfif>
<table border="1" cellpadding="3" cellspacing="0" id="maintable">
<tr>
 <th>Race</th>
 <th>Location</th>
 <th>Distance</th>
 <th>Date & Time</th>
 <th>Added by</th>
 <th>Edit/Delete</th>
</tr>
<cfoutput query="getraces">
<tr>
 <td>#racename#</td>
 <td>#city#, #state#</td>
 <td>#distance#</td>
 <td>#DateFormat(racetime, "ddd, mmm d, yyyy")# at #TimeFormat(racetime,"h:mm tt")#</td>
 <td><a href="mailto:#email#">#firstname# #lastname#</a></td>
 
<cfif VARIABLES.userid EQ getraces.userid OR (isDefined("SESSION.role") AND SESSION.role EQ "admin")>
 <td>
 <form method="post" action="RaceEdit.cfm" style="margin-bottom:0px">
  <input type="hidden" name="raceid" value="#raceid#">
  <input type="submit" value="EDIT" style="font-size:xx-small">
  <input type="submit" name="deleting" value="DELETE" style="font-size:xx-small">
 </form>
 </td>
<cfelse>
 <td>PROTECTED</td>
</cfif>

</tr>
</cfoutput>
</table>
<cfinclude template="Includes/Footer.cfm">
</body>
</html>
Code Explanation

"User" users now get a "PROTECTED" cell for records that they don't own. Admins are able to edit all records.

Sorting Data

We will now make the table headings into links that, when clicked on, will change the order in which the records appear.

Code Sample: ManagingData/Demos/Races3.cfm

 ---- Code Omitted ---- 
<table border="1" cellpadding="3" cellspacing="0" id="maintable">
<cfoutput>
<tr>
 <th>
  <a href="#CGI.SCRIPT_NAME#?orderfield=racename">Race</a>
 </th>
 <th>
 <a href="#CGI.SCRIPT_NAME#?orderfield=city">Location</a>
 </th>
 <th>
 <a href="#CGI.SCRIPT_NAME#?orderfield=distance">Distance</a>
 </th>
 <th>
 <a href="#CGI.SCRIPT_NAME#?orderfield=racetime">Date & Time</a>
 </th>
 <th>
 <a href="#CGI.SCRIPT_NAME#?orderfield=firstname">Added by</a>
 </th>
 <th>Edit/Delete</th>
</tr>
</cfoutput>
 ---- Code Omitted ----

That completes the page for displaying data. In the next sections, we will learn how to add, edit and delete races.

Adding Data using Stored Procedures

A stored procedure is a database object that holds a callable query, allowing parameters to be passed in.

Benefits of Stored Procedures

The major benefits of stored procedures.

  • Performance. Because their execution plan does not have to be determined at runtime, stored procedures typically run faster than straight queries.
  • Security. Stored procedures provide a means for accomplishing a specific task and do not provide the same kind of direct access to tables as straight queries do.
  • Simplicity and Reusability. Complex queries can be saved as stored procedures, so developers do not have to write the same difficult query over and over.

Calling Stored Procedures with ColdFusion

In ColdFusion, stored procedures can be called directly within a <cfquery> tag using the database-specific syntax for executing them. They can also be called using the <cfstoredproc> tag.

<cfstoredproc> and <cfprocparam>

The <cfstoredproc> tag works much like the <cfquery> tag, except that, rather than a straight query, it contains (or can contain) one or more <cfprocparam> tags for passing parameters to the stored procedure.

<cfstoredproc> Attributes
AttributeDescription
procedureRequired. The name of the stored procedure.
dataSourceRequired. The name of the data source.
usernameOverrides username set up in ColdFusion Administrator.
passwordOverrides password set up in ColdFusion Administrator.
blockfactorMaximum rows to get at a time from server.
debugTurns debugging display on or off.
returnCodeSets cfstoredproc.statusCode to the status code returned by the stored procedure.
resultThe name of the structure to hold the variables returned by the stored procedure. By default, the structure is stored in cfstoredproc.

Using <cfprocparam>, parameters are passed into a stored procedure in the same order as they are set in the stored procedure itself.

<cfprocparam> Attributes
AttributeDescription
typePossible values are in, out, or inout depending on whether a value is being sent to and/or received from the stored procedure.
variableRequired if type is in or inout. The variable name that will hold the data returned by the stored procedure.
valueRequired if type is in and optional if type is inout. Value passed to stored procedure.
CFSQLTypeRequired. The SQL data type (e.g, cf_sql_integer, cf_sql_varchar, etc.)
maxLengthThe maximum number of characters of an in or inout value. "0" means no limit.
scaleThe number of digits after the decimal of a numeric parameter. "0" means no limit.
nullThe in or inout parameter passes a null value. The value attribute is ignored.

The following three examples show different methods for inserting the race:

  1. Using <cfquery> with a standard INSERT statement.
  2. Using <cfquery> with a call to a stored procedure.
  3. Using <cfstoredproc>.

Code Sample: ManagingData/Demos/RaceAdd-query.cfm

<cfif NOT isDefined("SESSION.userid")>
 <cflocation url="index.cfm" addtoken="no">
</cfif>

<cfparam name="FORM.racehour" default="">
<cfparam name="FORM.raceminute" default="">
<cfparam name="FORM.raceday" default="">
<cfparam name="FORM.racemonth" default="">
<cfparam name="FORM.raceyear" default="">
<cfparam name="FORM.raceampm" default="am">
<cfparam name="FORM.racename" default="">
<cfparam name="FORM.distance" default="">
<cfparam name="FORM.city" default="">
<cfparam name="FORM.state" default="">
<cfparam name="FORM.description" default=""> 
<html>
<head>
<title>Add a Race</title>
</head>

<body>
<cfif isDefined("FORM.submitted")>

  <cfif FORM.raceampm EQ "pm">
   <cfset racehour = FORM.racehour+12>
  <cfelse>
   <cfset racehour = FORM.racehour>
  </cfif>
   
  <cfset racetime = CreateDateTime(FORM.raceyear, FORM.racemonth, FORM.raceday, racehour, FORM.raceminute, 0)>
  <cfset racetime = CreateODBCDateTime(racetime)>
  
  <!---Use <cfquery> with a standard insert statement--->
  <cfquery datasource="#APPLICATION.datasource#">
   INSERT INTO Races
   (racename, racetime, distance, city,
   state, active, userid, description)
   VALUES('#FORM.racename#',#racetime#,'#FORM.distance#','#FORM.city#',
   '#FORM.state#',1,#SESSION.userid#,'#FORM.description#')
  </cfquery>
  
  <p class="success">Race Added.<br>
  <a href="Races.cfm">Return to Race List</a>.</p>
  <h1>Add Another Race</h1>
  <!---Clean up variables--->
  
  <cfset FORM.racehour = "">
  <cfset FORM.raceminute = "">
  <cfset FORM.raceday = "">
  <cfset FORM.racemonth = "">
  <cfset FORM.raceyear = "">
  <cfset FORM.raceampm = "">
  <cfset FORM.racename = "">
  <cfset FORM.distance = "">
  <cfset FORM.city = "">
  <cfset FORM.state = "">
  <cfset FORM.description = ""> 

<cfelse>
 <h1>Add Race</h1>
</cfif>
<cfoutput>
<form method="post" action="#CGI.SCRIPT_NAME#">
<input type="hidden" name="submitted" value="true">
<table>
<tr>
 <td>Race Name:</td>
 <td><input name="racename" value="#FORM.racename#" type="text" size="30" maxlength="50"></td>
</tr>
<tr>
 <td>City:</td>
 <td><input name="city" value="#FORM.city#" type="text" size="30" maxlength="50"></td>
</tr>
<tr>
 <td>State:</td>
 <td><input name="state" value="#FORM.state#" type="text" size="3" maxlength="2"></td>
</tr>
<tr valign="top">
 <td>Distance (in miles):</td>
 <td><input name="distance" value="#FORM.distance#" type="text" size="10" maxlength="20"></td>
</tr>
<tr valign="top">
 <td>Time:</td>
 <td>
 <select name="racehour">
 <cfloop index="hour" from="1" to="12" step="1">
  <option value="#hour#"<cfif hour EQ FORM.racehour> selected</cfif>>#hour#</option>
 </cfloop>
 </select> :
 <select name="raceminute">
 <cfloop index="minute" from="0" to="59" step="15">
  <option value="#minute#"<cfif minute EQ FORM.raceminute> selected</cfif>>#NumberFormat(minute,"00")#</option>
 </cfloop>
 </select>
 <input name="raceampm" type="radio" value="am"<cfif FORM.raceampm EQ "am"> checked</cfif>> AM
 <input name="raceampm" type="radio" value="pm"<cfif FORM.raceampm EQ "pm"> checked</cfif>> PM</td>
</tr>
<tr valign="top">
 <td>Date:</td>
 <td>
 <select name="racemonth">
 <cfloop index="month" from="1" to="12">
  <option value="#month#"<cfif month EQ FORM.racemonth> selected</cfif>>#MonthAsString(month)#</option>
 </cfloop>
 </select>
 <select name="raceday">
 <cfloop index="day" from="1" to="31">
  <option value="#day#"<cfif day EQ FORM.raceday> selected</cfif>>#day#</option>
 </cfloop>
 </select>
 <select name="raceyear">
 <cfloop index="year" from="#Year(Now())#" to="#Year(Now())+3#">
  <option value="#year#"<cfif year EQ FORM.raceyear> selected</cfif>>#year#</option>
 </cfloop>
 </select>
  
 </td>
</tr>
<tr valign="top">
 <td>Description:</td>
 <td>
  <textarea name="description" rows="5" cols="40"
     wrap="soft">#FORM.description#</textarea>
 </td>
</tr>
<tr>
 <td colspan="2" align="right"><input type="submit"
     value="Add Race"></td>
</tr>
</table>
</form>
</cfoutput>
<cfinclude template="Includes/Footer.cfm">
</body>
</html>

Code Sample: ManagingData/Demos/RaceAdd-sp.cfm

 ---- Code Omitted ---- 
  <!---Use <cfquery> with a call to the spInsertRace stored procedure--->
  <cfquery datasource="#APPLICATION.datasource#">
   exec spInsertRace '#FORM.racename#','#FORM.description#','#FORM.distance#',#racetime#,'#FORM.city#',
   '#FORM.state#',1,#SESSION.userid#
  </cfquery>
 ---- Code Omitted ----

Code Sample: ManagingData/Demos/RaceAdd-sp2.cfm

 ---- Code Omitted ---- 
  <!---Use <cfstoredproc> with nested <cfparam>s--->
  <cfstoredproc procedure="spInsertRace" datasource="#APPLICATION.datasource#">
   <cfprocparam type="In" cfsqltype="cf_sql_varchar" value="#FORM.racename#">
   <cfprocparam type="In" cfsqltype="cf_sql_varchar" value="#FORM.description#">
   <cfprocparam type="In" cfsqltype="cf_sql_varchar" value="#FORM.distance#">
   <cfprocparam type="In" cfsqltype="cf_sql_date" value="#racetime#">
   <cfprocparam type="In" cfsqltype="cf_sql_varchar" value="#FORM.city#">
   <cfprocparam type="In" cfsqltype="cf_sql_varchar" value="#FORM.state#">
   <cfprocparam type="In" cfsqltype="cf_sql_bit" value="1">
   <cfprocparam type="In" cfsqltype="cf_sql_tinyint" value="1">
  </cfstoredproc>
 ---- Code Omitted ----

<cfprocresult>

Stored procedures can also return one or more recordsets. The <cfprocresult> tag is used to store these recordsets as ColdFusion queries. It works much like the name attribute of the <cfquery> tag.

<cfprocresult> Attributes
AttributeDescription
nameRequired. The name of the recordset.
resultSetA number indicating which recordset to store (only used if multiple recordsets are returned by the stored procedure).
maxRowsThe maximum number of records to retrieve for that recordset.

Editing and Deleting Data

The page for editing and deleting data is shown below.

Code Sample: ManagingData/Demos/RaceEdit.cfm

<cfif NOT isDefined("SESSION.userid") OR NOT isDefined("FORM.raceid")>
 <cflocation url="index.cfm" addtoken="no">
</cfif>
<html>
<head>
<title>Edit Race</title>
</head>
<body>
<cfif isDefined("FORM.deleting")>
 <cfquery datasource="#APPLICATION.datasource#">
  DELETE FROM Races
  WHERE raceid=<cfqueryparam value="#FORM.raceid#" cfsqltype="cf_sql_integer">
 </cfquery>
 
 <cflocation url="Races3.cfm" addtoken="no">
<cfelseif isDefined("FORM.editing")>
 <cfif FORM.raceampm EQ "pm">
  <cfset racehour = FORM.racehour+12>
 <cfelse>
  <cfset racehour = FORM.racehour>
 </cfif>
  
 <cfset racetime = CreateDateTime(FORM.raceyear, FORM.racemonth, FORM.raceday, FORM.racehour, FORM.raceminute, 0)>
 <cfset racetime = CreateODBCDateTime(racetime)>
 
 <cfquery datasource="#APPLICATION.datasource#">
  UPDATE Races
  SET racename = '#FORM.racename#',
   racetime = #racetime#,
   distance = '#FORM.distance#',
   city = '#FORM.city#',
   state = '#FORM.state#',
   description = '#FORM.description#',
   active = #FORM.active#
  WHERE raceid = <cfqueryparam value="#FORM.raceid#" cfsqltype="cf_sql_integer">
 </cfquery>

 <p class="success">Race Updated.<br>
 <a href="Races3.cfm">Return to Race List</a>.</p>
 <h1>Edit Race</h1>

<cfelse>
 <h1>Edit Race</h1>
</cfif>

<cfquery name="getraceinfo" datasource="#APPLICATION.datasource#">
 SELECT racename, racetime, distance,
 city, state, description, active
 FROM Races
 WHERE raceid=<cfqueryparam value="#FORM.raceid#" cfsqltype="cf_sql_integer">
</cfquery>

<cfoutput query="getraceinfo">
<form method="post" action="#CGI.SCRIPT_NAME#">
<input type="hidden" name="editing" value="true">
<input type="hidden" name="raceid" value="#FORM.raceid#">
<table>
<tr>
 <td>Race Name:</td>
 <td><input name="racename" value="#racename#" type="text" size="30" maxlength="50"></td>
</tr>
<tr>
 <td>City:</td>
 <td><input name="city" value="#city#" type="text" size="30" maxlength="50"></td>
</tr>
<tr>
 <td>State:</td>
 <td><input name="state" value="#state#" type="text" size="3" maxlength="2"></td>
</tr>
<tr valign="top">
 <td>Distance (in miles):</td>
 <td><input name="distance" value="#distance#" type="text" size="10" maxlength="20"></td>
</tr>
<tr valign="top">
 <td>Time:</td>
 <td>
 <select name="racehour">
 <cfloop index="hour" from="1" to="12">
  <option value="#hour#"<cfif hour EQ TimeFormat(racetime,"h")> selected</cfif>>#hour#</option>
 </cfloop>
 </select> :
 <select name="raceminute">
 <cfloop index="minute" from="0" to="59" step="15">
  <option value="#minute#"<cfif minute EQ TimeFormat(racetime,"mm")> selected</cfif>>#NumberFormat(minute,"00")#</option>
 </cfloop>
 </select>
 <input name="raceampm" type="radio"
   value="am"<cfif TimeFormat(racetime,"tt") EQ "am"> checked</cfif>> AM
 <input name="raceampm" type="radio"
   value="pm"<cfif TimeFormat(racetime,"tt") EQ "pm"> checked</cfif>> PM</td>
</tr>
<tr valign="top">
 <td>Date:</td>
 <td>
 <select name="racemonth">
 <cfloop index="month" from="1" to="12">
  <option value="#month#"<cfif month EQ DateFormat(racetime,"m")> selected</cfif>>#MonthAsString(month)#</option>
 </cfloop>
 </select>
 <select name="raceday">
 <cfloop index="day" from="1" to="31">
  <option value="#day#"<cfif day EQ DateFormat(racetime,"d")> selected</cfif>>#day#</option>
 </cfloop>
 </select>
 <select name="raceyear">
 <cfloop index="year" from="#Year(Now())#" to="#Year(Now())+3#">
  <option value="#year#"<cfif year EQ DateFormat(racetime,"yyyy")> selected</cfif>>#year#</option>
 </cfloop>
 </select>
 </td>
</tr>
<tr valign="top">
 <td>Active:</td>
 <td>
 <input name="active" type="radio" value="1"<cfif active EQ 1> checked</cfif>> Yes
 <input name="active" type="radio" value="0"<cfif active EQ 0> checked</cfif>> No</td>
</tr>
<tr valign="top">
 <td>Description:</td>
 <td>
  <textarea name="description" rows="5" cols="40" wrap="soft">#description#</textarea>
 </td>
</tr>
<tr>
 <td colspan="2" align="right"><input type="submit" value="Edit Race"></td>
</tr>
</table>
</form>
</cfoutput>
</body>
</html>
Code Explanation

This is the processing page that is executed when the Edit or Delete button is pressed in the Races table.

The code at the top of the page prevents access to this page for people who are not logged in. It also prevents direct access to this page by checking to make sure that raceid exists in the FORM scope.

<cfif NOT isDefined("SESSION.userid") OR NOT isDefined("FORM.raceid")>
 <cflocation url="index.cfm" addtoken="no">
</cfif>

The code then checks to see if the Delete button was clicked. If it was, then it deletes that record from the table and returns to the Races page:

<cfif isDefined("FORM.deleting")>
 <cfquery datasource="#APPLICATION.datasource#">
  DELETE FROM Races
  WHERE raceid=<cfqueryparam value="#FORM.raceid#" cfsqltype="cf_sql_integer">
 </cfquery>
 
 <cflocation url="Races3.cfm" addtoken="no">

The code then checks if the Edit button on this page was clicked. 

<cfelseif isDefined("FORM.editing")>
 <cfif FORM.raceampm EQ "pm">
  <cfset racehour = FORM.racehour+12>
 <cfelse>
  <cfset racehour = FORM.racehour>
 </cfif>
  
 <cfset racetime = CreateDateTime(FORM.raceyear, FORM.racemonth, FORM.raceday, FORM.racehour, FORM.raceminute, 0)>
 <cfset racetime = CreateODBCDateTime(racetime)>
 
 <cfquery datasource="#APPLICATION.datasource#">
  UPDATE Races
  SET racename = '#FORM.racename#',
   racetime = #racetime#,
   distance = '#FORM.distance#',
   city = '#FORM.city#',
   state = '#FORM.state#',
   description = '#FORM.description#',
   active = #FORM.active#
  WHERE raceid = <cfqueryparam value="#FORM.raceid#" cfsqltype="cf_sql_integer">
 </cfquery>

 <p class="success">Race Updated.<br>
 <a href="Races3.cfm">Return to Race List</a>.</p>
 <h1>Edit Race</h1>

Note that the Edit button on the Races page is not named, so it will not trigger the code above to run. It is triggered by the hidden field in the form on this same page (shown below).

<form method="post" action="#CGI.SCRIPT_NAME#">
<input type="hidden" name="editing" value="true">
<input type="hidden" name="raceid" value="#FORM.raceid#">

This example uses the <cfqueryparam> tag when deleting and editing to verify the data type of FORM.raceid. Using <cfqueryparam> in this way is highly recommended as it helps to protect your database from malicious attacks.

Managing Data Conclusion

In this lesson of the ColdFusion tutorial, you have learned to create data management pages and to control data access based on user roles.

To continue to learn ColdFusion go to the top of this page and click on the next lesson in this ColdFusion Tutorial's Table of Contents.

Use of this website implies agreement to the following:

Copyright Information

All pages and graphics on this Web site are the property of Webucator, Inc. unless otherwise specified.

None of the content on this website may be redistributed or reproduced in any way, shape, or form without written permission from Webucator, Inc.

No Printing or saving of web pages

This content may not be printed or saved. It is for online use only.

Linking to this website

You may link to any of the pages on this website; however, you may not include the content in a frame or iframe without written permission from Webucator, Inc.

Warranties

This website is provided without warranty of any kind. There are no guarantees that use of the site will not be subject to interruptions. All direct or indirect risk related to use of the site is borne entirely by the user. All code and explanations provided on this site are provided without warranties to correctness, performance, fitness, merchantability, and/or any other warranty (whether expressed or implied).

For individual private use only

You agree not to use this online manual to deliver or receive training. If you are delivering or attending a class that is making use of this online manual, you are in violation of our terms of service. Please report any abuse to courseware@webucator.com. If you would like to deliver or receive training using this manual, please fill out the form at http://www.webucator.com/Contact.cfm.