Can anybody please help me with this page? I've been fooling around
with it forever and I'm just not having any luck.
There are two things that I need help with:
1. Why aren't my dropdowns working properly? They should be pulling
the data from the database and displaying it when possible. As it
stands, they just look like regular drop-downs, no matter what value is
in the db. This code has worked for me on other forms, but for some
reason I must have done something different here.
2. How can I insert multiple records? This is something I haven't
done before and I can't find good documentation on... I'm not even sure
where to start. I've added in the code for doing a single insert...
which should be okay... but I'm not sure where to go from here.
Thank you for any help you are able to offer.
My code:
*** *** ***
<!--- QUERIES AND VARIABLE SETTING --->
<cfset today = DateFormat(now(),"mm/dd/yyyy")>
<cfquery name="permissions" datasource="#DSN#">
SELECT id, centraladmin, provadmin, trainer
FROM users
WHERE id = #client.id#
</cfquery>
<cfquery name="courses" datasource="#DSN#">
SELECT * from courses WHERE id = #url.id#
</cfquery>
<cfquery name="attendee" datasource="#DSN#">
SELECT attendee.*, users.id AS users_id, users.lname, users.fname
FROM attendee, users
WHERE attendee.assigneddate = #url.id# and users.id =
attendee.users_id
ORDER BY users.lname, users.fname ASC
</cfquery>
<cfquery name="salutation" datasource="#DSN#">
SELECT * FROM salutation ORDER by salutation ASC
</cfquery>
<cfquery name="province" datasource="#DSN#">
SELECT * FROM prov ORDER by abbreviation ASC
</cfquery>
<cfquery name="country" datasource="#DSN#">
SELECT * FROM country ORDER by name ASC
</cfquery>
<cfquery name="profession" datasource="#DSN#">
SELECT * FROM profession
</cfquery>
<cfquery name="designation" datasource="#DSN#">
SELECT * FROM designation
</cfquery>
<cfquery name="yearsprac" datasource="#DSN#">
SELECT * FROM years
</cfquery>
<cfquery name="empstatus" datasource="#DSN#">
SELECT * FROM empstatus
</cfquery>
<cfquery name="empplace" datasource="#DSN#">
SELECT * FROM empplace
</cfquery>
<cfquery name="empresp" datasource="#DSN#">
SELECT * FROM empresp
</cfquery>
<cfquery name="dpccat" datasource="#DSN#">
SELECT * FROM dpccat
</cfquery>
<cfquery name="dpcclass" datasource="#DSN#">
SELECT * FROM dpcclass
</cfquery>
<cfquery name="caeyear" datasource="#DSN#">
select * FROM caeyear ORDER BY year DESC
</cfquery>
<cfquery name="payment" datasource="#DSN#">
SELECT * FROM payment
</cfquery>
<cfquery name="assign" datasource="#DSN#">
SELECT * FROM postassign
</cfquery>
<!--- END QUERIES AND VARIABLE SETTING --->
<!--- SET DEFAULT VALUES FOR CHECKBOX/RADIO BUTTONS --->
<CFPARAM NAME="FORM.attended" default="0">
<CFPARAM NAME="FORM.withdrawn" default="0">
<CFPARAM NAME="FORM.paymentcleared" default="0">
<CFPARAM NAME="FORM.assignrec1" default="0">
<CFPARAM NAME="FORM.assignrec2" default="0">
<CFPARAM NAME="FORM.assignrec3" default="0">
<CFPARAM NAME="FORM.pretest" default="0">
<CFPARAM NAME="FORM.skilltest1" default="0">
<CFPARAM NAME="FORM.skilltest2" default="0">
<CFPARAM NAME="FORM.skilltest3" default="0">
<!--- END SET DEFAULT VALUES FOR CHECKBOX/RADIO BUTTONS --->
<cfif IsDefined("url.action")>
<cfif form.action eq 'update'>
<cflock timeout="30" throwontimeout="Yes"
name="Add" type="EXCLUSIVE">
<cfquery name="adduser" datasource="#DSN#">
UPDATE attendee
SET assigneddate = '#form.assigneddate#',
attended = '#form.attended#',
withdrawn = '#form.withdrawn#',
paymentmethod =
'#form.paymentmethod#',
paymentcleared =
'#form.paymentcleared#',
authnum = '#form.authnum#',
assignrec1 = '#form.assignrec1#',
assignrec2 = '#form.assignrec2#',
assignrec3 = '#form.assignrec3#',
pretest = '#form.pretest#',
writtentest1 =
'#form.writtentest1#',
writtentest2 =
'#form.writtentest2#',
writtentest3 =
'#form.writtentest3#',
skilltest1 = '#form.skilltest1#',
skilltest2 = '#form.skilltest2#',
skilltest3 = '#form.skilltest3#',
postassign = '#form.postassign#',
adminnote = '#form.adminnote#'
WHERE users_id = #form.id#
</cfquery>
<!--- QUERIES AND VARIABLE SETTING --->
<cfquery name="userinfo" datasource="#DSN#">
SELECT * FROM users WHERE id = #form.id#
</cfquery>
<cfquery name="attendeeupdate"
datasource="#DSN#">
SELECT users_id, attended,
writtentest1, writtentest2, writtentest3, skilltest1, skilltest2,
skilltest3, postassign
FROM attendee
WHERE users_id = #form.id#
</cfquery>
<!--- END QUERIES AND VARIABLE SETTING --->
<cfif attendeeupdate.writtentest1 GTE 70>
<cfset posttest =
'passed'>
<cfelseif
attendeeupdate.writtentest2 GTE 70>
<cfset posttest =
'passed'>
<cfelseif
attendeeupdate.writtentest3 GTE 70>
<cfset posttest =
'passed'>
<cfelse>
<cfset posttest =
'failed'>
</cfif>
<cfif (attendeeupdate.skilltest1 EQ
1) AND (attendeeupdate.skilltest2 EQ 1) AND (attendeeupdate.skilltest3
EQ 1)>
<cfset skilltest =
'passed'>
</cfif>
<cfif (posttest EQ 'passed') AND
(attendeeupdate.attended EQ 1) AND (attendeeupdate.postassign EQ
'Passed') AND (skilltest EQ 'passed')>
<cfset finitialtemp =
Left(userinfo.fname, 1)>
<cfset linitialtemp =
Left(userinfo.lname, 1)>
<cfset dateboxtemp =
DatePart("yyyy", userinfo.datebox)>
<cfset finitial =
LCase(finitialtemp)>
<cfset linitial =
LCase(linitialtemp)>
<cfset datebox =
Right(dateboxtemp, 2)>
<cfset gradnum =
'#userinfo.prov##finitial##linitial##datebox##userinfo.id#'>
<cfelse>
<cfset gradnum =
'Pending'>
</cfif>
<cfquery name="adduser"
datasource="#DSN#">
UPDATE attendee SET gradnum
= '#gradnum#' WHERE users_id = #form.id#
</cfquery>
<cfquery name="adduser"
datasource="#DSN#">
UPDATE attendee
SET confsent = <cfqueryparam
cfsqltype="cf_sql_date" value="#form.confsent#"
null="#IIf(Trim(form.confsent) EQ '', DE("Yes"), DE("No"))#">,
regreceived =
<cfqueryparam cfsqltype="cf_sql_date" value="#form.regreceived#"
null="#IIf(Trim(form.regreceived) EQ '', DE("Yes"), DE("No"))#">,
pkgsentdate =
<cfqueryparam cfsqltype="cf_sql_date" value="#form.pkgsentdate#"
null="#IIf(Trim(form.pkgsentdate) EQ '', DE("Yes"), DE("No"))#">,
reqdate =
<cfqueryparam cfsqltype="cf_sql_date" value="#form.reqdate#"
null="#IIf(Trim(form.reqdate) EQ '', DE("Yes"), DE("No"))#">,
asthmacertdate =
<cfqueryparam cfsqltype="cf_sql_date" value="#form.asthmacertdate#"
null="#IIf(Trim(form.asthmacertdate) EQ '', DE("Yes"), DE("No"))#">,
taxdate =
<cfqueryparam cfsqltype="cf_sql_date" value="#form.taxdate#"
null="#IIf(Trim(form.taxdate) EQ '', DE("Yes"), DE("No"))#">,
assstratdate =
<cfqueryparam cfsqltype="cf_sql_date" value="#form.assstratdate#"
null="#IIf(Trim(form.assstratdate) EQ '', DE("Yes"), DE("No"))#">,
ceudate =
<cfqueryparam cfsqltype="cf_sql_date" value="#form.ceudate#"
null="#IIf(Trim(form.ceudate) EQ '', DE("Yes"), DE("No"))#">,
caeexamdate =
<cfqueryparam cfsqltype="cf_sql_date" value="#form.caeexamdate#"
null="#IIf(Trim(form.caeexamdate) EQ '', DE("Yes"), DE("No"))#">,
caestatusgaindate = <cfqueryparam cfsqltype="cf_sql_date"
value="#form.caestatusgaindate#"
null="#IIf(Trim(form.caestatusgaindate) EQ '', DE("Yes"), DE("No"))#">,
recertdate =
<cfqueryparam cfsqltype="cf_sql_date" value="#form.recertdate#"
null="#IIf(Trim(form.recertdate) EQ '', DE("Yes"), DE("No"))#">,
recertrecdate =
<cfqueryparam cfsqltype="cf_sql_date" value="#form.recertrecdate#"
null="#IIf(Trim(form.recertrecdate) EQ '', DE("Yes"), DE("No"))#">
WHERE users_id = #form.id#
</cfquery>
</cflock>
</cfif>
</cfif>
<table>
<cfoutput query="attendee">
<form name="grading" action="grading.cfm?id=#courses.id#" method="post">
<tr>
<td><a href="user_info.cfm?id=#attendee.users_id#">#attendee.lname#,
#attendee.fname#</a></td>
<td>#attendee.id#</td>
<td>#attendee.gradnum#</td>
<td><input type="text" name="regreceived" size="10" class="text"
value="#attendee.regreceived#" /></td>
<td><select name="paymentmethod"><cfloop query="payment"><option
<cfif attendee.paymentmethod eq
payment.type>selected</cfif>>#payment.type#</option></cfloop></select></
td>
<td><input type="radio" name="paymentcleared" value="Yes" <cfif
attendee.paymentcleared IS 'yes'>checked</cfif>> Yes <input type="radio"
name="paymentcleared" value="No" <cfif attendee.paymentcleared IS
'no'>checked</cfif>> No</td>
<td><input type="text" name="authnum" value="#attendee.authnum#"
size="10" class="text" /></td>
<td><input type="text" name="confsent" size="10" class="text"
value="#attendee.confsent#" /></td>
<td><input type="text" name="pkgsentdate" size="10" class="text"
value="#attendee.pkgsentdate#" /></td>
<td align="center"><input type="checkbox" value="1"
name="assignrec1" <cfif attendee.assignrec1 EQ 1>checked</cfif> /></td>
<td align="center"><input type="checkbox" value="1"
name="assignrec2" <cfif attendee.assignrec2 EQ 1>checked</cfif> /></td>
<td align="center"><input type="checkbox" value="1"
name="assignrec3" <cfif attendee.assignrec3 EQ 1>checked</cfif> /></td>
<td align="center"><input type="checkbox" value="1" name="pretest"
<cfif attendee.pretest EQ 1>checked</cfif> /></td>
<td><input type="radio" name="attended" value="Yes" <cfif
attendee.attended IS 'yes'>checked</cfif>> Yes <input type="radio"
name="attended" value="No" <cfif attendee.attended IS
'no'>checked</cfif>> No</td>
<td><input type="radio" name="withdrawn" value="Yes" <cfif
attendee.withdrawn IS 'yes'>checked</cfif>> Yes <input type="radio"
name="withdrawn" value="No" <cfif attendee.withdrawn IS
'no'>checked</cfif>> No</td>
<td><input type="text" name="writtentest1"
value="#attendee.writtentest1#" size="3" class="text" />%</td>
<td><input type="text" name="writtentest2"
value="#attendee.writtentest2#" size="3" class="text" />%</td>
<td><input type="text" name="writtentest3"
value="#attendee.writtentest3#" size="3" class="text" />%</td>
<td align="center"><input type="checkbox" value="1"
name="skilltest1" <cfif attendee.skilltest1 EQ 1>checked</cfif> /></td>
<td align="center"><input type="checkbox" value="1"
name="skilltest2" <cfif attendee.skilltest2 EQ 1>checked</cfif> /></td>
<td align="center"><input type="checkbox" value="1"
name="skilltest3" <cfif attendee.skilltest3 EQ 1>checked</cfif> /></td>
<td><select name="postassign"><cfloop query="assign"><option <cfif
attendee.postassign eq
assign.status>selected</cfif>>#assign.status#</option></cfloop></select>
</td>
<td><input type="text" name="reqdate" value="#attendee.reqdate#"
size="10" class="text" /></td>
<td><input type="text" name="asthmacertdate"
value="#attendee.asthmacertdate#" size="10" class="text" ></td>
<td><input type="text" name="taxdate" value="#attendee.taxdate#"
size="10" class="text" /></td>
<td><input type="text" name="ceudate" value="#attendee.ceudate#"
size="10" class="text" /></td>
<td><input type="text" name="assstratdate"
value="#attendee.assstratdate#" size="10" class="text" /></td>
<td><input type="text" name="caestatusgaindate"
value="#attendee.caestatusgaindate#" size="10" class="text" /></td>
<td><input type="text" name="caeexamdate"
value="#attendee.caeexamdate#" size="10" class="text" /></td>
<td><input type="text" name="recertdate"
value="#attendee.recertdate#" size="10" class="text" /></td>
<td><input type="text" name="recertrecdate"
value="#attendee.recertrecdate#" size="10" class="text" /></td>
<td><textarea name="adminNote" cols="20" rows="2" class="text"
/>#attendee.adminNote#</textarea><br /><br /></td>
</tr>
</cfoutput>
<tr>
<td colspan="32" align="left">
<input type="hidden" value="update" name="action">
<input type="hidden" value="#attendee.users_id#" name="id">
<input type="submit" value="Update" />
</td>
</tr>
</form>
</table>
*** *** ***
--
Jillian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
