[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