dbCountSQL

When this property is not blank, itíll be used to obtain the accurate record count instead of using the dbSQL information. There are three options to obtain the total number of records returned from the query specified by dbSQL -

  1. Use the dbCountSQL property to supply an efficient SQL statement to obtain the accurate count.
  2. Leave the dbCountSQL blank and ASPDB will direct the dbSQL statement to construct the dbCountSQL. For example - SELECT a,b,c from TABLE ORDER BY a will be converted to SELECT count (*) from TABLE for MS-SQL and SELECT count (*) (SELECT a,b,c from TABLE ORDER BY a) for ORACLE.
  3. Specify dbCountSQL="lowspeed" will force ASPdb to count via RS.MoveNext in  which is extremely ineficient. Use this option when there is absolutely no alternatives.
Property Syntax

Obj.dbCountSQL="SQL statement"

Parameters

Parameters

Description

SQL statement
  • Valid SQL statement returning the count of dbSQL.
  • "lowspeed" - force straight count using MoveNext  to obtain record count. 

Example

<%
response.write("Start=" & now())
Set X=Server.CreateObject("ASP.DB")
X.dbQuickProps="1;HOTEL_ACCESS;;Both;;;;ACCESS;5"
X.dbSQL="SELECT * FROM FRANK WHERE City = 'PLANO' ORDER BY CITY"
'do not need the ORDER BY to get count
X.dbCountSQL="SELECT count(*) FROM FRANK WHERE City = 'PLANO'"
X.ASPDB
response.write("End=" & now())
%>

<%
response.write("Start=" & now() & "<BR>")
Set X=Server.CreateObject("ASP.DB")
X.dbQuickProps="1;;;Grid;;;;ORACLE,Server;5"
X.dbDSN="Provider=MSDAORA; data source=dell500; user id=HOTEL; password=hotel"
X.dbSQL="SELECT * FROM Frank where city LIKE 'A%'"
X.dbRSCursor=0
X.dbCountSQL="SELECT count(*) FROM Frank where city LIKE 'A%'"
X.ASPDB
response.write("End=" & now())
%>

Example - ORACLE

Using the ODBC Tester to validate the dbCountSQL property. Note that when using COUNT(*) to obtain record count in ORACLE, ORDER BY cannot be used! Always validate the CountSQL statement in ODBC tester.

Obj.dbDSN="Provider=MSDAORA; data source=MyServer; user id=scott; password=tiger"
Obj. dbDBType="ORACLE"
Obj.dbSQL="SELECT DEPT.DEPTNO, DNAME, SUM(SAL) FROM EMP, DEPT WHERE EMP.DEPTNO(+) = DEPT.DEPTNO GROUP BY DEPT.DEPTNO, DNAME"
Obj.dbCountSQL="SELECT COUNT(*) FROM (SELECT DEPT.DEPTNO, DNAME, SUM(SAL) FROM EMP, DEPT WHERE EMP.DEPTNO(+) = DEPT.DEPTNO GROUP BY DEPT.DEPTNO, DNAME)"

Example - SQL7

X.dbSQL = "SELECT OrdD.ProductID AS ProdID,SUM(OrdD.Quantity) AS AmountSold FROM [Order Details] AS OrdD JOIN Products as Prd ON OrdD.ProductID = Prd.ProductID AND Prd.CategoryID = 2 GROUP BY OrdD.ProductID"
X.dbCountSQL = "SELECT count(*) FROM [Order Details] AS OrdD JOIN Products as Prd ON OrdD.ProductID = Prd.ProductID AND Prd.CategoryID = 2 GROUP BY OrdD.ProductID"

Notes