[thelist] ASP count

Scott pippen at bigpond.net.au
Sun Nov 3 19:10:01 CST 2002


From: "Kevin Stevens" <kjs at ratking.co.uk>

: 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 &  "%'")
...
: 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?


Kevin
You can do this with the use of GetRows, which is a method of the RecordSet
object.

The code would look a little like this:

set RSResult = conn.execute ("select PostDate, ... from YourTable")
if RSResult.eof then
  ' no results
else
  ' suck the results into a 2d array
  arrData = RSResult.GetRows
  RSResult.close

  ' get your rowcount nb. 0-based
  intRowCount = UBound(arrData, 2)+1
end if

The only major difference then is that your results are returned in a
2-dimensional
array not an ADO recordset, so only the raw data is returned, not field types
etc.

- Using GetRows is *much* faster however.
- no need for a second query, additional return columns or count() fns!
- The .RecordCount property of the recordset object is dependant as mentioned in
another post on the cursortype used and is not accessible if the recordset is
filled from a stored procedure.

See also
http://www.aspfree.com/asp/getrows.asp
http://www.asp101.com/samples/db_getrows.asp

hth






More information about the thelist mailing list