Tutorial: Managing Data | ColdFusion MX Tutorial

Welcome to our free ColdFusion MX tutorial. This tutorial is based on Webucator's Comprehensive ColdFusion Training course.

Lesson Goals

To display data retrieved from a database.
To insert data into a database.
To update existing data in a database.
To delete data from a database.
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:

A section for adding a new user.
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>
---- C O D E   O M I T T E D ----

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>
---- C O D E   O M I T T E D ----

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>
---- C O D E   O M I T T E D ----

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:

All users will be able to see active races.
All logged-in users will be able to add races.
"User" users will only be able to edit and delete races that they added themselves.
"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>

"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

---- C O D E   O M I T T E D ----
<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>
---- C O D E   O M I T T E D ----

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
Attribute	Description
procedure	Required. The name of the stored procedure.
dataSource	Required. The name of the data source.
username	Overrides username set up in ColdFusion Administrator.
password	Overrides password set up in ColdFusion Administrator.
blockfactor	Maximum rows to get at a time from server.
debug	Turns debugging display on or off.
returnCode	Sets cfstoredproc.statusCode to the status code returned by the stored procedure.
result	The 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
Attribute	Description
type	Possible values are in, out, or inout depending on whether a value is being sent to and/or received from the stored procedure.
variable	Required if type is in or inout. The variable name that will hold the data returned by the stored procedure.
value	Required if type is in and optional if type is inout. Value passed to stored procedure.
CFSQLType	Required. The SQL data type (e.g, cf_sql_integer, cf_sql_varchar, etc.)
maxLength	The maximum number of characters of an in or inout value. "0" means no limit.
scale	The number of digits after the decimal of a numeric parameter. "0" means no limit.
null	The in or inout parameter passes a null value. The value attribute is ignored.

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

Using <cfquery> with a standard INSERT statement.
Using <cfquery> with a call to a stored procedure.
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

---- C O D E   O M I T T E D ----
		<!---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>
---- C O D E   O M I T T E D ----

Code Sample:

ManagingData/Demos/RaceAdd-sp2.cfm

---- C O D E   O M I T T E D ----
		<!---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>
---- C O D E   O M I T T E D ----

<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
Attribute	Description
name	Required. The name of the recordset.
resultSet	A number indicating which recordset to store (only used if multiple recordsets are returned by the stored procedure).
maxRows	The 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>

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 the Resources Table

Duration: 30 to 45 minutes.

This exercise involves making changes to three pages: ManagingData/Exercises/Resources.cfm, ManagingData/Exercises/ResourceAdd.cfm, and ManagingData/Exercises/ResourceEdit.cfm. The files have comments showing where to edit or add code.

Open ManagingData/Exercises/Resources.cfm in your editor.
If the user is not logged in, set userid to 0. Otherwise set userid to the user's userid as contained in the Session.
Make the table headers into links that, when clicked, sort the table on that field.
Open ManagingData/Exercises/ResourceAdd.cfm in your editor.
Write a <cfquery> to insert the resource entry into the database. The database fields are resourcename, link, active, userid, and description.
Open ManagingData/Exercises/ResourceEdit.cfm in your editor.
Write a condition to check if the resource should be deleted or updated and write to queries to handle the appropriate operation.

Code Sample:

ManagingData/Exercises/Resources.cfm

               <!---
	If the user is not logged in, set userid to 0.
	Otherwise set userid to the user's userid as contained in the SESSION.
--->
<cfparam name="URL.orderfield" default="resourceid">

<cfset orderfields = "resourcename, description, firstname">

<cfif NOT ListContains(orderfields,URL.orderfield)>
	<cfset URL.orderfield = "resourceid">
</cfif>
<cfquery name="getresources" datasource="#APPLICATION.datasource#">
	SELECT resourceid, resourcename, link, active, description, u.userid, firstname, lastname, email
	FROM Resources r, Users u
	WHERE u.userid = r.userid
	ORDER BY #URL.orderfield#
</cfquery>
<html>
<head>
<title>Running Resources</title>
</head>

<body>
<h1>Running Resources</h1>
<cfif userid NEQ 0>
	<a href="ResourceAdd.cfm">Add a resource</a>
</cfif>
<table border="1" cellpadding="3" cellspacing="0" id="maintable">
<!---
	Make the table headers into links that, when clicked, sort the table on that field
--->
<tr>
	<th>Resource</th>
	<th>Description</th>
	<th>Added by</th>
	<th>Edit/Delete</th>
</tr>
<cfoutput query="getresources">
	<tr valign="top">
		<td>
			<a href="#link#">#resourcename#</a>
		</td>
		<td>#description#</td>
		<td>
			<a href="mailto:#email#">#firstname# #lastname#</a>
		</td>
		<cfif Variables.userid EQ getresources.userid OR  (isDefined("SESSION.role") AND SESSION.role EQ "admin")>
			<td>
			<form method="post" action="ResourceEdit.cfm" style="margin-bottom:0px">
				<input type="hidden" name="resourceid" value="#resourceid#">
				<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 Sample:

ManagingData/Exercises/ResourceAdd.cfm

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

<cfparam name="FORM.resourcename" default="">
<cfparam name="FORM.link" default="">
<cfparam name="FORM.description" default=""> 

<html>
<head>
<title>Add a Resource</title>
</head>

<body>
<cfif isDefined("FORM.submitted")>
<!---
	Write a <cfquery> to insert the resource entry into the database.
	The database fields are resourcename, link, active, userid, and description.
--->
	<p class="success">Resource Added.<br>
	<a href="resources.cfm">Return to Resource List</a>.</p>
	<h1>Add Another Resource</h1>
<cfelse>
	<h1>Add Resource</h1>
</cfif>

<cfoutput>
<form method="post" action="#CGI.SCRIPT_NAME#">
</cfoutput>
<input type="hidden" name="submitted" value="true">
<cfoutput><input type="hidden" name="userid" value="#SESSION.userID#"></cfoutput>
<table>
<tr>
	<td>Resource Name:</td>
	<td><input name="resourcename" type="text" size="30"maxlength="50"></td>
</tr>
<tr>
	<td>URL:</td>
	<td><input name="link" type="text" size="30" maxlength="50"></td>
</tr>
<tr valign="top">
	<td>Description:</td>
	<td>
		<textarea name="description" rows="5" cols="40" wrap="soft"></textarea>
	</td>
</tr>
<tr>
	<td colspan="2" align="right"><input type="submit" value="Add Resource"></td>
</tr>
</table>
</form>
<cfinclude template="Includes/Footer.cfm">
</body>
</html>

Code Sample:

ManagingData/Exercises/ResourceEdit.cfm

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

<html>
<head>
<title>Edit Resource</title>
</head>

<body>
<cfif WRITE CONDITION TO CHECK IF RESOURCE SHOULD BE DELETED>
<!---
	Write a <cfquery> to update the resource entry in the database.
	The database fields are resourcename, link, active, description, and resourceid.
--->
	<cflocation url="Resources.cfm" addtoken="no">
<cfelseif WRITE CONDITION TO CHECK IF RESOURCE SHOULD BE EDITED>
<!---
	Write a <cfquery> to update the resource entry in the database.
	The database fields are resourcename, link, active, description, and resourceid.
--->
	<p class="success">Resource Updated.<br>
	<a href="resources.cfm">Return to Resource List</a>.</p>
<cfelse>
	<h1>Update Resource</h1>
</cfif>

<cfquery name="getresourceinfo" datasource="#APPLICATION.datasource#">
	SELECT userid, resourcename, description, link, active
	FROM Resources
	WHERE resourceid = <cfqueryparam value="#FORM.resourceid#" cfsqltype="cf_sql_integer">
</cfquery>

<cfoutput>
<form method="post" action="#CGI.SCRIPT_NAME#">
<input type="hidden" name="editing" value="true">
<input type="hidden" name="resourceid" value="#FORM.resourceid#">
<table>
<tr>
	<td>Resource Name:</td>
	<td><input name="resourcename" value="#getresourceinfo.resourcename#" type="text" size="30" maxlength="50"></td>
</tr>
<tr>
	<td>URL:</td>
	<td><input name="link" value="#getresourceinfo.link#" type="text" size="30"  maxlength="50"></td>
</tr>
<tr>
	<td>Active:</td>
	<td>
		<input name="active" value="1" type="radio"<cfif getresourceinfo.active EQ 1> checked</cfif>>Yes
		<input name="active" value="0" type="radio"<cfif getresourceinfo.active EQ 0> checked</cfif>>No
	</td>
</tr>
<tr valign="top">
	<td>Description:</td>
	<td>
		<textarea name="description" rows="5" cols="40" wrap="soft">#getResourceInfo.description#</textarea>
	</td>
</tr>
<tr>
	<td colspan="2" align="right"><input type="submit" value="Update Resource"></td>
</tr>
</table>
</form>
</cfoutput>
<cfinclude template="Includes/Footer.cfm">
</body>
</html>

Solution:

ManagingData/Solutions/Resources.cfm

               <cfif isDefined("SESSION.userid")>
	<cfset userid = SESSION.userid>
<cfelse>
	<cfset userid = 0>
</cfif>
---- C O D E   O M I T T E D ----
<tr>
	<th><a href="resources.cfm?orderfield=resourcename">Resource</a></th>
	<th><a href="resources.cfm?orderfield=description">Description</a></th>
	<th><a href="resources.cfm?orderfield=firstname">Added by</a></th>
	<th>Edit/Delete</th>
</tr>
---- C O D E   O M I T T E D ----

Solution:

ManagingData/Solutions/ResourceAdd.cfm

---- C O D E   O M I T T E D ----
<cfif isDefined("FORM.submitted")>
		<cfquery datasource="#APPLICATION.datasource#">
			INSERT INTO Resources
			(resourcename, link, description, active, userid)
			VALUES('#FORM.resourcename#','#FORM.link#','#FORM.description#',1,#SESSION.userid#)
		</cfquery>
	<p class="success">Resource Added.<br>
	<a href="resources.cfm">Return to Resource List</a>.</p>
	<h1>Add Another Resource</h1>
<cfelse>
---- C O D E   O M I T T E D ----

Solution:

ManagingData/Solutions/ResourceEdit.cfm

---- C O D E   O M I T T E D ----
<cfif isDefined("FORM.deleting")>
	<cfquery datasource="#APPLICATION.datasource#">
		DELETE FROM Resources
		WHERE resourceid = <cfqueryparam value="#FORM.resourceid#" cfsqltype="cf_sql_integer">
	</cfquery>
	
	<cflocation url="Resources.cfm" addtoken="no">
<cfelseif isDefined("FORM.editing")>
	<cfquery datasource="#APPLICATION.datasource#">
		UPDATE Resources
		SET resourcename = '#FORM.resourcename#',
			description = '#FORM.description#',
			link = '#FORM.link#',
			active = #FORM.active#
		WHERE resourceid = <cfqueryparam value="#FORM.resourceid#" cfsqltype="cf_sql_integer">
	</cfquery>
	<p class="success">Resource Updated.<br>
	<a href="resources.cfm">Return to Resource List</a>.</p>
<cfelse>
---- C O D E   O M I T T E D ----

Lesson: Managing Data

Creating a User Administration Page

Displaying Users

Code Sample:

Adding Users

Code Sample:

Editing and Deleting Users

Code Sample:

Controlling Access to the Page

Code Sample:

Aborting the Page for Unpermitted Visitors

Code Sample:

Providing Convenient Access to the Admin Page

Code Sample:

Important Note

Allowing Users to Add and Edit Data

Displaying Data

Code Sample:

Controlling Access to Individual Records

Code Sample:

Sorting Data

Code Sample:

Adding Data using Stored Procedures

Benefits of Stored Procedures

Calling Stored Procedures with ColdFusion

<cfstoredproc> and <cfprocparam>

Code Sample:

Code Sample:

Code Sample:

<cfprocresult>

Editing and Deleting Data

Code Sample:

Managing the Resources Table

Code Sample:

Code Sample:

Code Sample:

Solution:

Solution:

Solution: