asked on

ASP - Record count from SQL Server

The following returns a Count of -1 when connecting to SQL Server 2005.

It should return 10. What's wrong with this code.

thx

<%

Response.Write("Hello")

set conn=Server.CreateObject("ADODB.Connection")

conn.Open "Provider=sqloledb;Data Source=myDB;Initial Catalog=Cust;UID=Auser;PWD=auser;connect timeout=30"

set rs=Server.CreateObject("ADODB.recordset")

sql="SELECT top 10 * from Customers"

rs.Open sql,conn

if rs.Supports(adApproxPosition)=true then
i=rs.RecordCount
response.write("<BR> The number of records is: " & i)
end if

rs.Close
conn.Close

%>

Paul Jackson

known bug, try :

if rs.Supports(adApproxPosition)=true then
rs.MoveLast
rs.MoveFirst

i=rs.RecordCount
response.write("<BR> The number of records is: " & i)
end if

JElster

ASKER

I get

Microsoft OLE DB Provider for SQL Server error '80040e24'

Rowset does not support fetching backward.

Scott Fell

rs.Open sql,conn

if not rs.bof or not rs.eof then
myArray=rs.getrows() ' fast
end if

rs.Close
conn.Close
counter=0
' now you have an array and don't have to call the database

      For r = LBound(myArray, 2) To UBound(myArray, 2)
       name = arrZips(0, r)
              address= arrZips(1, r)
              city= arrZips(2, r)
              zip= arrZips(3, r)
counter=counter+1
response.write "Name "&name & " record count "&counter&"<br>"
next

response.write "You have "&counter& " records"

SOLUTION

plummet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Robert Schutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial