count function

[aweise]

My count function is not working properly...what am I doing wrong? I am viewing a list of attendees on a roster and it should show the amount enrolled instead it just lists it as one. How do I solve this so I can add links based on the amount of enrollees?

Here's my code:


''build the sql statement based on the input from the form
mySQL="SELECT c.classID, e.title, c.className, c.classDate, e.first & '' '' & e.last as Name, e.ext, e.dept, e.enrollDate, c.maxEnroll, count(e.enrollID) as enrolled "&_
"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"

''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
%>

<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>&nbsp;</td><td width=170> "& rstemp("name") & "</td><td width=10>&nbsp;</td><td width=120>"& rstemp("title") & "</td><td width=10>&nbsp;</td><td width=100>"& rstemp("ext") & "</td>"


response.write "</tr>"
rstemp.movenext
LOOP

%>


</table>

<!--end-->
</td>
</tr></table><p><br>

<table>
<tr>
<td>
<% response.write "openslots is" & openslots %>

<p>
</td>
</tr>
</table>

</html>
<%
rstemp.close
set rstemp=nothing
conn.close
set conn=nothing
%>

[jsawkang]

Hi...

i was wondering whether ur datatype for maxEnroll is of type number. i don't think u can calculate if it's of type text. try change the datatype if it's of text.

[aweise]

I needed to add the count during the loop as such
count=0
do until rs.eof
count=count +1
'show text
rs.MoveNext
Loop
Response.Write "Total enrolled: " & count

Thanks for responding.

[maxell120]

Try Isolating, the EnrollID, Like....

SQL = SELECT COUNT(EnrollID) where blah blah blah...

then

sCount = rstemp(0).value