I'm developing an online registration for a high school & trying to get a 

report which lists the total number of students enrol in a particular 

course.  But I can't seem to get the aggregate function from sql to work in 

vbscript.  It's giving me error saying "Syntax error (missing operator) in 

query expression 'count(student_id) TOTAL'."  I have given the count field 

an alias as well, so that I can retrieve from the record set.



Set Apples = Server.CreateObject("ADODB.Connection")

oConn = "DRIVER={Microsoft Access Driver (*.mdb)}; "

oConn = oConn & "DBQ=" & Server.MapPath("\sys01201\database\school.mdb")

Apples.Open(oConn)

sSQL = "SELECT s.course_id, c.title, count(student_id) TOTAL FROM 

StudentCourse s, CourseInfo c WHERE s.course_id=c.course_id GROUP BY 

s.course_id, c.title ORDER BY s.course_id"

Set oRS = Apples.Execute(sSQL)



Do While NOT oRS.EOF 

	Response.Write(oRS("course_id").Value)

	Response.Write(oRS("title").Value)

        Response.Write(oRS("total").Value)

	oRS.MoveNext

Loop



Thanks for your help!

Kavitha

I think it needs to be:



sSQL =3D "SELECT s.course_id, c.title, count(student_id) AS TOTAL FROM



--include the AS between (student_id) and TOTAL







                                                                       

                      

                    kavitha_baskar@h...                                   

                      

                    otmail.com              To:     "ASP Databases"    

                      

                                            <asp_databases@p...

>                     

                    06/27/2001 11:16        cc:                        

                      

                    AM                      Subject:     [asp_databases

] Syntax error        

                    Please respond          (missing operator) in query

 expression           

                    to "ASP                 'count(student_id) TOTAL'. 

                      

                    Databases"                                         

                      

                                                                       

                      

                                                                       

                      







I'm developing an online registration for a high school & trying to get

 a

report which lists the total number of students enrol in a particular

course.  But I can't seem to get the aggregate function from sql to wor

k in



vbscript.  It's giving me error saying "Syntax error (missing operator)

 in

query expression 'count(student_id) TOTAL'."  I have given the count fi

eld

an alias as well, so that I can retrieve from the record set.



Set Apples =3D Server.CreateObject("ADODB.Connection")

oConn =3D "DRIVER=3D{Microsoft Access Driver (*.mdb)}; "

oConn =3D oConn & "DBQ=3D" & Server.MapPath("\sys01201\database\school.

mdb")

Apples.Open(oConn)

sSQL =3D "SELECT s.course_id, c.title, count(student_id) TOTAL FROM

StudentCourse s, CourseInfo c WHERE s.course_id=3Dc.course_id GROUP BY

s.course_id, c.title ORDER BY s.course_id"

Set oRS =3D Apples.Execute(sSQL)



Do While NOT oRS.EOF

           Response.Write(oRS("course_id").Value)

           Response.Write(oRS("title").Value)

        Response.Write(oRS("total").Value)

           oRS.MoveNext

Loop



Thanks for your help!

Kavitha

Hi Kavitha,



Use the word "as" in between Count(Student_id) and

TOTAL. It should read as 



Count(Student_ID) as TOTAL



Thats it.... Now It should work.



Cheers and Regards

Vijay.G