Count Problem

H

HighFlyer

Guppy
Hi guys, i'm having this problem with COUNT.

Error Message : Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/newasiasalsa/dstudentcourse2.asp, line 17, column 28
rs.Open "SELECT COUNT(*)as "males" FROM studentOnCourse WHERE courseID = "& courseID &" AND gender = "Male"", conn, 3, 3
---------------------------^

this is my coding:
<%
set conn = server.CreateObject ("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("db/db.mdb")
set rs = server.CreateObject ("ADODB.Recordset")
rs.Open "SELECT COUNT(*)as "males" FROM studentOnCourse WHERE courseID = "& courseID &" AND gender = "Male"", conn, 3, 3


set rs=nothing
set conn=nothing
%>

i'm trying to input the count value into a table using this:
<td width="17%"><% Response.Write (males) %>&nbsp;</td>

please help. thanx alot in advance
 
try the below version

<%
set conn = server.CreateObject ("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("db/db.mdb")
set rs = server.CreateObject ("ADODB.Recordset")
rs.Open "SELECT COUNT(*)as males FROM studentOnCourse WHERE courseID = "& courseID &" AND gender = "Male" ", conn, 3, 3


set rs=nothing
set conn=nothing
%>

the 3,3 are not required
 
That should be:

rs.Open "SELECT COUNT(*) as males FROM studentOnCourse WHERE courseID = " & courseID & " AND gender = ""Male""", conn, 3, 3
 
also i just seen

<td width="17%"><% Response.Write (males) %>&nbsp;</td>

should be

<td width="17%"><% Response.Write (rs("males")) %>&nbsp;</td>
 
Emagine said:
also i just seen

<td width="17%"><% Response.Write (males) %>&nbsp;</td>

should be

<td width="17%"><% Response.Write (rs("males")) %>&nbsp;</td>
Click to expand...

hi, thanx for your help but i was thinking if i could save the rs"males" into a variable? instead of doing a "><% Response.Write (rs("males")) %>, i wanna do a "><% Response.Write (males) %> how can i do it?
 
Emagine said:
try the below version

<%
set conn = server.CreateObject ("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("db/db.mdb")
set rs = server.CreateObject ("ADODB.Recordset")
rs.Open "SELECT COUNT(*)as males FROM studentOnCourse WHERE courseID = "& courseID &" AND gender = "Male" ", conn, 3, 3


set rs=nothing
set conn=nothing
%>

the 3,3 are not required
Click to expand...

I've tried the code but a new error appears.
Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
/newasiasalsa/dstudentcourse2.asp, line 17
Line 17 is rs.Open "SELECT COUNT(*) as males FROM studentOnCourse WHERE courseID = "& courseID &" AND gender = ""Male""", conn, 3, 3
 
you have to many " on the male section (gender = ""Male""",)

it should be gender = "Male" ",
and then the rest
 
You must log in or register to reply here.
Back
Top