Forum Moderators: open
I've been working on this for days and I have not been satisfied. The links seem to work whether the class is at maxenroll or not. I want to show the enroll link ONLY if the count is less than maxenroll.
Here's my code maybe you can help:
<%@ Language = "VBScript"%>
<%=Request.QueryString("enrolldate")%>
<%=Request.QueryString("enrolled")%>
<%=Request.QueryString("classname")%>
<%
'Declare all local variables
dim conn
dim rs
dim strconn
dim strsql
dim expire
strsql = ""
'set connection string to local variable-I use a DSN-less connection
strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("train.mdb")
expire= FormatNumber(Date)-31
'build the sql statement based on the input from the form
mySQL="SELECT count(e.enrollID) as enrolled, c.classID, e.title, c.className, c.classDate, e.first & ' ' & e.last as Name, e.ext, e.dept, e.enrollDate, c.maxEnroll "&_
"FROM enrollTable e "&_
"LEFT JOIN classTable c ON c.classID = e.classID "&_
"WHERE c.classID = " & Request.Querystring("classID") & " "&_
"GROUP BY c.classID, e.title, c.className, c.classDate, e.first, e.last, e.ext, e.dept, e.enrollDate, c.maxEnroll"
showblank=" "
shownull="-null-"
' Response.Write "DEBUG: SQL is " & mySQL & "<HR>"
'Set connection object
set conn = server.createobject("adodb.connection")
conn.open strconn
'Use the execute method of the connection object the insert the record
set rstemp=conn.execute(mySQL)
If rstemp.eof then
response.write "There are no enrollees at this time. Please check back at a later date."
response.write "<p>Back to <a href=""news-home.asp"">Training Registration</a> page."
conn.close
set conn=nothing
response.end
end if
%>
<html>
<head>
<title>View <%=classname%> Class</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" type="text/css" href="train.css">
</head>
<body bgcolor="#FFFFFF" text="#000000" leftmargin=0>
<div id="Layer1" style="position:absolute; left:6px; top:-13px; width:742px; height:69px; z-index:1; background-color: #CBCB9B; layer-background-color: #CBCB9B; border: 1px none #000000">
<img src="../../Images/spacer.gif" width=1 height=17> <span class=date>
<center><%= rstemp("classname")%></center></span> </div>
<table border=0>
<tr>
<td><img src="../../Images/class.gif"></td>
<td valign=top>
<img src="../../Images/spacer.gif" width=415 height=1><p><br><br><br><br>
<center><span class=co2><%= rstemp("enrollDate")%></span></center>
<table valign=top>
<tr><!--nested table-->
<td><p><br>
<table>
<%
response.write "<tr><td class=head>Branch/Department</td><td></td><td class=head>Name</td><td></td><td class=head>Title</td><td></td><td class=head>Ext</td>"
%>
<% DO WHILE NOT rstemp.eof
' put fields into variables
classid=rstemp("classID")
classname=rstemp("className")
classdate=rstemp("classDate")
enrolled=rstemp("enrolled")
if not isNumeric(enrolled) then enrolled = 0
maxenroll=rstemp("maxEnroll")
openslots = maxenroll - enrolled
name=rstemp("name")
ext=rstemp("ext")
dept=rstemp("dept")
title=rstemp("title")
' write the fields to browser
response.write "<tr class=view><td width=180> "& rstemp("dept") & "</td><td width=10> </td><td width=170> "& rstemp("name") & "</td><td width=10> </td><td width=120>"& rstemp("title") & "</td><td width=10> </td><td width=100>"& rstemp("ext") & "</td>"
response.write "</tr>"
rstemp.movenext
LOOP
%>
</table>
<!--end-->
</td>
</tr></table><p><br>
<table width=500 border=0>
<% set rstemp=conn.execute(mySQL)%>
<tr>
<td><img src="../../Images/spacer.gif" height=50 width=1>
<p><br> <% if enrolled < maxenroll then %><img src="../../Images/arrowgold.gif">
<a href="enroll.asp?classID=<%=rstemp("classID")%>&className=<%=Escape(classname)%>&classDate=<%=classdate%>">
Enroll</a>
<img src="../../Images/arrowgold.gif"> <a href=mgmt.asp> Class Description</a>
<img src="../../Images/arrowgold.gif"> <a href="index3.asp?classname=<%=classname%>"><%=classname%> Schedule</a>
<% else if enrolled = maxenroll then %>
<img src="../../Images/arrowgold.gif"> <a href=mgmt.asp> Class Description</a> Go <img src="../../Images/arrowgold.gif"><a href="index3.asp?classname=<%=classname%>"><%=classname%> Schedule</a>
<% end if %>
<% end if %>
</td>
</tr>
</table>
<tr><td><img src="../../Images/mgmt.gif" valign=bottom align="absbottom"></td>
</tr>
</table>
</body>
</html>
<%
rstemp.close
set rstemp=nothing
conn.close
set conn=nothing
%>
1. I would change the criteria if a links is shown to
<% else if enrolled >= maxenroll then %>2. Have you tried printing out the values of "enrolled" and "maxenroll"? That way you can check if your criteria is met.
3. How does the "enrolled"-value gets into the class-record? Do you update the classTable with every registration?
4. When I did it, I had two different queries: One with a count on the registrations-table (to get a value for enrolled). I saved this in a local variable and then compared that to the "maxenroll" in my second recordset to get the information for the registration form (or in your case, schedule)
I did test out what enrolled, openslots, and maxenroll gives me and I get 1, 2, and 3 respectively. (so it looks like it not counting properly and there's a problem somwhere in my logic).
