[thelist] ASP count

[Chris Blessing]

Kevin-

You have a couple options here, but the one I would go with (since I too am
partial to the set rs = conn.execute(somequery) syntax) is to put the
count(*) in your query and just return it with the results as such:

set RSResult = conn.execute ("select PostDate, Subject, Author, PostText,
count(PostDate) theCount
from Emails where Author like '%" & strSearchWord &  "%' order by PostDate
asc;")

Now RSResult("theCount") holds the count for that query.  It'll be returned
with every row, which is redundant for sure, but it's much less taxing than
running an additional query.

HTH!

Chris Blessing
webguy at mail.rit.edu
http://www.330i.net

> I thought it would be nice to let people know how many results their query
> has returned on my site and I have achieved this using the following SQL
> statement...
>
> set NumQuery = conn.execute ("select count(Author) as thecount from Emails
> where Author like '%" & strSearchWord &  "%'")
>
> which works fine. My question is this: using this method I am using 2 SQL
> statements, one to collect the results from the query and one to
> collect the
> number of results.
>
> set NumQuery = conn.execute ("select count(Author) as thecount from Emails
> where Author like '%" & strSearchWord &  "%'")
>
> set RSResult = conn.execute ("select PostDate, Subject, Author, PostText
> from Emails where Author like '%" & strSearchWord &  "%' order by PostDate
> asc;")
>
> Is this a reasonable way of achieving this (it works after all) but it is
> making 2 requests to the db which seems like a waste of resources. Is it
> possible to do this in one SQL statement and, if so, will it make any kind
> of significant difference?
>
> Thanks
>
> Kevin Stevens
> kjs at ratking.co.uk
>