LearnersPermit
Technical User
I have an application which allows users to keep track of their time and the projects that they are involved with.
I have a series of tables for projects, actvities, staff and clients, etc. As well as my record table.
When I'm trying to allow users to amend their records my update and delete features don't work. I have a form called timelist which allows users to view all of their records in date order. They can select a record to be edited and it does appear on my edit_time.cfm page. However when I select delete or Edit nothing happens. Here is my code. Probably its a very minor problem but I'm driving myself over the edge trying to find it.
<cfparam name="form.action" default="disp">
<cfparam name="form.name" default="">
<cfif not isdefined("form.submit")>
<cfparam name="form.recid" default="">
<cfparam name="form.servid" default="">
<cfparam name="form.day" default="">
<cfparam name="form.hours_worked" default="">
<cfparam name="form.staffid" default="">
<cfparam name="form.comments" default="">
<cfparam name="form.projid" default="">
<cfparam name="form.actid" default="">
<cfparam name="form.clientid" default="">
</cfif>
<!--- Set a local var for the recid --->
<cfif ISDEFINED("form.recid") AND form.recid GT 0>
<cfset locrecid='#Trim(form.btnEdit)#'>
<cfelse>
<cfset locrecid="0">
</cfif>
<cfif ISDEFINED("form.smtDelete") AND locrecid GT 0>
<!--- Delete the Record --->
<cfquery name="qrydel" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
DELETE FROM Record
WHERE recid = #locrecid#
</cfquery>
<cfset locPostMessage="Record Deleted Successfully">
<cfelseif ISDEFINED("form.smtSubmit") AND locrecid GT 0>
<!--- Edit the Record --->
<cfquery name="qryupdate" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
UPDATE Record
SET day = '#form.day#',
SET hours_worked = '#form.hours_worked#',
SET comments = '#form.comments#',
SET staffid = '#form.staffid#',
SET clientid = '#form.clientid#',
SET servid = '#form.servid#',
SET projid = '#form.projid#',
SET actid = '#form.actid#'
WHERE recid = #locrecid#
</cfquery>
<cfset locPostMessage="The Record was Edited">
</cfif>
<cfquery name="qrecord" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
select record.recid,record.day,record.hours_worked,record.comments,record.staffid,record.servid,record.projid,record.actid,record.clientid,staff.staffid,staff.firstname,service.servid,service.service,project.projid,project.project,activity.actid,activity.activity,client.clientid,client.client
from Record,
Staff,
Service,
Project,
Activity,
Client
where recid = '#locrecid#'
and record.staffid=staff.staffid
and record.servid=service.servid
and record.projid=project.projid
and record.actid=activity.actid
and client.clientid=record.clientid
order by firstname
</cfquery>
<!---Get a Staff Member--->
<cfquery name="qryStaff" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT staffid,firstname,lastname
FROM Staff
ORDER BY firstname ASC
</cfquery>
<!---Get a Service--->
<cfquery name="qryService" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT servid,service
FROM Service
ORDER BY service ASC
</cfquery>
<!---Get a Project--->
<cfquery name="qryProject" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT projid,project
FROM Project
ORDER BY project ASC
</cfquery>
<!---Get an Activity--->
<cfquery name="qryActivity" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT actid,activity
FROM Activity
ORDER BY activity ASC
</cfquery>
<!---Get a Client--->
<cfquery name="qryClient" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT clientid,client
FROM Client
ORDER BY client ASC
</cfquery>
<cfif ISDEFINED("locPostMessage")>
<cfoutput><h3>#locPostMessage#</h3></cfoutput>
</cfif>
<cfif form.action eq "disp">
<html>
<head>
<title>"Time Tracker - Edit Record"</title>
<link rel="stylesheet" href="stores.css" type="text/css">
<br />
</head>
<body>
<table>
<tr>
<td width="25%" background="#E5E5E5;"><cfinclude template="menu.cfm"></td>
<td width="75"><td>
<table cellpadding="10">
<tr><th>Edit a Record</th>
</tr>
<tr>
<td>
<pre>
<form name="editTime" action="#cgi.script_name#" edit="post">
<cfoutput>
<input type="text" name="recid" value="#locrecid#">
Day:
<input type="text" name="day" value="#qrecord.day#" size="60" maxlength="255">
Hours Worked:
<input type="text" name="hours_worked" value="#qrecord.hours_worked#" size="60" maxlength="255">
Comments:
<input type="text" name="comments" value="#qrecord.comments#" size="60" maxlength="255">
</cfoutput>
<!---staff drop down box --->
Staff:
<select name="staffid">
<option value="#qryselectCat.firstname#">
</option>
<cfoutput query="qryStaff">
<option value="#qryStaff.staffid#" <cfif qrecord.staffid EQ qryStaff.staffid> selected</cfif>>#qryStaff.firstname#</option></cfoutput></option>
</select>
<!---service drop down box --->
Service:
<select name="servid">
<option value="#qryselectCat.service#">
</option>
<cfoutput query="qryService">
<option value="#qryService.servid#" <cfif qrecord.servid EQ qryService.servid> selected</cfif>>#qryService.service#</option></cfoutput></option>
</select>
<!---project drop down box --->
Project:
<select name="projid">
<option value="#qryselectCat.project#">
</option>
<cfoutput query="qryProject">
<option value="#qryProject.projid#" <cfif qrecord.projid EQ qryProject.projid> selected</cfif>>#qryProject.project#</option></cfoutput></option>
</select>
<!---activity drop down box --->
Activity:
<select name="actid">
<option value="#qryselectCat.activity#">
</option>
<cfoutput query="qryActivity">
<option value="#qryActivity.actid#" <cfif qrecord.actid EQ qryActivity.actid> selected</cfif>>#qryActivity.activity#</option></cfoutput></option>
</select>
<!---client drop down box --->
Client:
<select name="clientid">
<option value="#qryselectCat.client#">
</option>
<cfoutput query="qryClient">
<option value="#qryClient.clientid#" <cfif qrecord.clientid EQ qryClient.clientid> selected</cfif>>#qryClient.client#</option></cfoutput></option>
</select>
</pre>
<input type="submit" value="Edit" name="smtSubmit">
<cfif locrecid GT 0>
<input type="Submit" value="Delete" name="smtDelete">
<input type="button" value="Cancel" onclick="location.href='edit_time.cfm'">
<cfelse>
<input type="Submit" value="Delete" name="" disabled>
<input type="button" value="Cancel" disabled>
</cfif>
</form>
</td>
</tr>
</table>
</table>
</cfif>
</body>
</html>
I have a series of tables for projects, actvities, staff and clients, etc. As well as my record table.
When I'm trying to allow users to amend their records my update and delete features don't work. I have a form called timelist which allows users to view all of their records in date order. They can select a record to be edited and it does appear on my edit_time.cfm page. However when I select delete or Edit nothing happens. Here is my code. Probably its a very minor problem but I'm driving myself over the edge trying to find it.
<cfparam name="form.action" default="disp">
<cfparam name="form.name" default="">
<cfif not isdefined("form.submit")>
<cfparam name="form.recid" default="">
<cfparam name="form.servid" default="">
<cfparam name="form.day" default="">
<cfparam name="form.hours_worked" default="">
<cfparam name="form.staffid" default="">
<cfparam name="form.comments" default="">
<cfparam name="form.projid" default="">
<cfparam name="form.actid" default="">
<cfparam name="form.clientid" default="">
</cfif>
<!--- Set a local var for the recid --->
<cfif ISDEFINED("form.recid") AND form.recid GT 0>
<cfset locrecid='#Trim(form.btnEdit)#'>
<cfelse>
<cfset locrecid="0">
</cfif>
<cfif ISDEFINED("form.smtDelete") AND locrecid GT 0>
<!--- Delete the Record --->
<cfquery name="qrydel" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
DELETE FROM Record
WHERE recid = #locrecid#
</cfquery>
<cfset locPostMessage="Record Deleted Successfully">
<cfelseif ISDEFINED("form.smtSubmit") AND locrecid GT 0>
<!--- Edit the Record --->
<cfquery name="qryupdate" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
UPDATE Record
SET day = '#form.day#',
SET hours_worked = '#form.hours_worked#',
SET comments = '#form.comments#',
SET staffid = '#form.staffid#',
SET clientid = '#form.clientid#',
SET servid = '#form.servid#',
SET projid = '#form.projid#',
SET actid = '#form.actid#'
WHERE recid = #locrecid#
</cfquery>
<cfset locPostMessage="The Record was Edited">
</cfif>
<cfquery name="qrecord" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
select record.recid,record.day,record.hours_worked,record.comments,record.staffid,record.servid,record.projid,record.actid,record.clientid,staff.staffid,staff.firstname,service.servid,service.service,project.projid,project.project,activity.actid,activity.activity,client.clientid,client.client
from Record,
Staff,
Service,
Project,
Activity,
Client
where recid = '#locrecid#'
and record.staffid=staff.staffid
and record.servid=service.servid
and record.projid=project.projid
and record.actid=activity.actid
and client.clientid=record.clientid
order by firstname
</cfquery>
<!---Get a Staff Member--->
<cfquery name="qryStaff" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT staffid,firstname,lastname
FROM Staff
ORDER BY firstname ASC
</cfquery>
<!---Get a Service--->
<cfquery name="qryService" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT servid,service
FROM Service
ORDER BY service ASC
</cfquery>
<!---Get a Project--->
<cfquery name="qryProject" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT projid,project
FROM Project
ORDER BY project ASC
</cfquery>
<!---Get an Activity--->
<cfquery name="qryActivity" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT actid,activity
FROM Activity
ORDER BY activity ASC
</cfquery>
<!---Get a Client--->
<cfquery name="qryClient" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT clientid,client
FROM Client
ORDER BY client ASC
</cfquery>
<cfif ISDEFINED("locPostMessage")>
<cfoutput><h3>#locPostMessage#</h3></cfoutput>
</cfif>
<cfif form.action eq "disp">
<html>
<head>
<title>"Time Tracker - Edit Record"</title>
<link rel="stylesheet" href="stores.css" type="text/css">
<br />
</head>
<body>
<table>
<tr>
<td width="25%" background="#E5E5E5;"><cfinclude template="menu.cfm"></td>
<td width="75"><td>
<table cellpadding="10">
<tr><th>Edit a Record</th>
</tr>
<tr>
<td>
<pre>
<form name="editTime" action="#cgi.script_name#" edit="post">
<cfoutput>
<input type="text" name="recid" value="#locrecid#">
Day:
<input type="text" name="day" value="#qrecord.day#" size="60" maxlength="255">
Hours Worked:
<input type="text" name="hours_worked" value="#qrecord.hours_worked#" size="60" maxlength="255">
Comments:
<input type="text" name="comments" value="#qrecord.comments#" size="60" maxlength="255">
</cfoutput>
<!---staff drop down box --->
Staff:
<select name="staffid">
<option value="#qryselectCat.firstname#">
</option>
<cfoutput query="qryStaff">
<option value="#qryStaff.staffid#" <cfif qrecord.staffid EQ qryStaff.staffid> selected</cfif>>#qryStaff.firstname#</option></cfoutput></option>
</select>
<!---service drop down box --->
Service:
<select name="servid">
<option value="#qryselectCat.service#">
</option>
<cfoutput query="qryService">
<option value="#qryService.servid#" <cfif qrecord.servid EQ qryService.servid> selected</cfif>>#qryService.service#</option></cfoutput></option>
</select>
<!---project drop down box --->
Project:
<select name="projid">
<option value="#qryselectCat.project#">
</option>
<cfoutput query="qryProject">
<option value="#qryProject.projid#" <cfif qrecord.projid EQ qryProject.projid> selected</cfif>>#qryProject.project#</option></cfoutput></option>
</select>
<!---activity drop down box --->
Activity:
<select name="actid">
<option value="#qryselectCat.activity#">
</option>
<cfoutput query="qryActivity">
<option value="#qryActivity.actid#" <cfif qrecord.actid EQ qryActivity.actid> selected</cfif>>#qryActivity.activity#</option></cfoutput></option>
</select>
<!---client drop down box --->
Client:
<select name="clientid">
<option value="#qryselectCat.client#">
</option>
<cfoutput query="qryClient">
<option value="#qryClient.clientid#" <cfif qrecord.clientid EQ qryClient.clientid> selected</cfif>>#qryClient.client#</option></cfoutput></option>
</select>
</pre>
<input type="submit" value="Edit" name="smtSubmit">
<cfif locrecid GT 0>
<input type="Submit" value="Delete" name="smtDelete">
<input type="button" value="Cancel" onclick="location.href='edit_time.cfm'">
<cfelse>
<input type="Submit" value="Delete" name="" disabled>
<input type="button" value="Cancel" disabled>
</cfif>
</form>
</td>
</tr>
</table>
</table>
</cfif>
</body>
</html>