How do I do this with ADO and an Access DB in ASP 3.0?

[Mike Brind]

How do I return multiple counts for display in an ASP 3.0 page?

For example, I want to display record counts for each state on the page.
Something that looks like this:

CA - 50
NY - 12
SC - 18

Right now I loop through each state (the id for each state), open a record
set, execute a query, print the count, close the recordset and repeat like
this:

Dim cnt
cnt=1
Do while cnt <= 50

ssql="SELECT count(*) FROM states WHERE stateid =" & cnt
With rs
.CursorLocation=3 'adUserClient
.ActiveConnection=cn
.source=sSql
.Open
End With

Response.Write (rs.Fields(0))

cnt=cnt+1
rs.Close
loop

Obviously this not very efficient. Can anyone suggest an improvement?

Remember I am working with Access so I believe I have to return a record set
rather than accessing a singleton like a SQL Server RETURN value.

I am assumiong that you have a column with the state name in your
table. I have called it StateName below:

<%
ssql = "SELECT StateName, COUNT(StateName) AS Countofstate FROM States
GROUP BY StateID"

Set rs = cn.execute(ssql)
If not rs.EOF Then
arrCount = rs.Getrows
rs.close : set rs = nothing
For i = 0 to Ubound(arrCount,2)
Response.Write arrCount(0,i) & ": " & arrCount(1,i)
Next
Else
Response.Write "No Records to show"
rs.Close : Set rs = Nothing
End If
%>

The method above grabs all the records in one go, puts them into an
array, and allows you to close and destroy the recordset object
immediately.

 

[Egbert Nierop \(MVP for IIS\)]

How do I return multiple counts for display in an ASP 3.0 page?

For example, I want to display record counts for each state on the page.
Something that looks like this:

CA - 50
NY - 12
SC - 18

Right now I loop through each state (the id for each state), open a record
set, execute a query, print the count, close the recordset and repeat like
this:

Dim cnt
cnt=1

note that the GROUP BY statement has a syntax. look at google for the
correct syntax if you want to add more information on 'states' for instance.

ssql="SELECT count(stateid) FROM states GROUP BY stateid"
With rs
.CursorLocation=3 'adUserClient
.ActiveConnection=cn
.source=sSql
.Open
End With

Response.Write (rs.Fields(0))

cnt=cnt+1
rs.Close

 

[Dave]

How do I return multiple counts for display in an ASP 3.0 page?

For example, I want to display record counts for each state on the page.
Something that looks like this:

CA - 50
NY - 12
SC - 18

Right now I loop through each state (the id for each state), open a record
set, execute a query, print the count, close the recordset and repeat like
this:

Dim cnt
cnt=1
Do while cnt <= 50

ssql="SELECT count(*) FROM states WHERE stateid =" & cnt
With rs
.CursorLocation=3 'adUserClient
.ActiveConnection=cn
.source=sSql
.Open
End With

Response.Write (rs.Fields(0))

cnt=cnt+1
rs.Close
loop

Obviously this not very efficient. Can anyone suggest an improvement?

Remember I am working with Access so I believe I have to return a record set
rather than accessing a singleton like a SQL Server RETURN value.

Any ideas would be appreciated.

Dave

 

[Dave]

Thanks guys!

note that the GROUP BY statement has a syntax. look at google for the
correct syntax if you want to add more information on 'states' for
instance.

ssql="SELECT count(stateid) FROM states GROUP BY stateid"
With rs
.CursorLocation=3 'adUserClient
.ActiveConnection=cn
.source=sSql
.Open
End With

Response.Write (rs.Fields(0))

cnt=cnt+1
rs.Close