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

M

Mike Brind

Dave said:
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.
 
E

Egbert Nierop \(MVP for IIS\)

Dave said:
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
 
D

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
 
D

Dave

Thanks guys!


Egbert Nierop (MVP for IIS) said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top