Multiple Count Function 1

[uncgis]

I am trying to create a ASP page that will count fields in a table and display the number...For instance

Men's Soccor 10
Women's Soccor 5
Men's Basketball 0

I have wrote a SQL that will count all the records...but I don't know how to assign each one a value...It only rembers the last line of code...below is my code

SQL1 = "SELECT ACTC_Code, COUNT(*) AS Sport " &_
"FROM SV1ATHL " &_
"WHERE (ACTC_Code = 'MBASE') OR " &_
"(ACTC_Code = 'MSOCC') OR " &_
"(ACTC_Code = 'WSOCC') OR " &_
"(ACTC_Code = 'MBASE') OR " &_
"(ACTC_Code = 'WBASK') OR " &_
"(ACTC_Code = 'MTENN') OR " &_
"(ACTC_Code = 'WTENN') OR " &_
"(ACTC_Code = 'CHEER') OR " &_
"(ACTC_Code is null) " &_
"GROUP BY ACTC_Code " &_
"ORDER BY ACTC_Code "

Does anyone know how to make an array or get a count for each field value...Thanks for all your help

 

[Sheco]

If you use ADO to run this SQL query then you'll get the results in an ADO recordset object.

Then you can iterate through the recordset with the MoveNext property. To give an idea:

Set adoRS = adoCN.Execute(SQL1)
if (adoRS.State <> 1) then
  Wet adoRS = Nothing
  adoCN.Close
  Set adoCN = Nothing
  Response.Write "ADO Error"
  Response.End
end if

Do While Not adoRS.EoF
 Response.Write adoRS("ACTC_Code") & "&nbsp;&nbsp;&nbsp;" & adoRS("Sport")
 Response.Write "<BR>" & vbCrLF
 [highlight]adoRS.MoveNext[/highlight]
Loop

adoRS.Close
Set adoRS = Nothing

adoCN.Close
Set adoCN = Nothing

 

[uncgis]

Thanks for your reply...the code works great.