Allowing Users to Add and Edit Data

Contact Us or call 1-877-932-8228
Allowing Users to Add and Edit Data

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>

"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:

  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
---- 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.

Next