Solved

ASP - Record count from SQL Server

Posted on 2013-01-20
5
1,115 Views
Last Modified: 2013-01-21
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

%>
0
Comment
Question by:JElster
5 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 38799394
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
0
 
LVL 1

Author Comment

by:JElster
ID: 38799404
I get

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

Rowset does not support fetching backward.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38799446
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"
0
 
LVL 10

Assisted Solution

by:plummet
plummet earned 250 total points
ID: 38800196
Can't test it here, but try adding an adOpenStatic  to the rs open:

rs.Open sql,conn, adOpenStatic

adOpenStatic is a constant with the value 3.

I hope that might fix the problem!
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 250 total points
ID: 38800346
You've had some comments already that may solve it for you, but I think I have a different take on your problem that may be of use to you in this case and perhaps in general.

You posted some code that may not be the whole page but if it is, there's a problem in the fact that you don't have an include or object reference for ADO constants (specifically the one you use: adApproxPosition). This can be checked easily in a number of ways:

- easy way: add this code after writing "Hello":
Response.Write("<BR>adApproxPosition = " & adApproxPosition)

Open in new window

The output should be
adApproxPosition = 16384 

Open in new window


- better way, but a bit more work: use "Option Explicit" at the start of your code, use "Dim" for all your variables and if the reference is indeed missing then the page will error with
Microsoft VBScript runtime error '800a01f4' 

Variable is undefined: 'adApproxPosition' 

Open in new window


To solve this, again an easy way and a better way:

- add this code after the "Option Explicit" (or at the start of your code if you decided not to go that route):
Const adApproxPosition = &H00004000

Open in new window


- add a reference to the ADO constants, this can be done in a number of ways (3 come to mind: include the file direct in your page after downloading it, include object reference in page, object reference in global.asa), this should be easy to find on the net


Now after this, you will still not get the RecordCount. Instead the "rs.Supports(...)" will return false and that part of the code will not be executed. The RecordCount can only be retrieved when certain Recordset types are used. In this case you could use
rs.Open sql, conn, adOpenKeyset

Open in new window

But again, if you didn't go the 'better' route you can just use
rs.Open sql, conn, 1

Open in new window


A reference for RecordSet.Open can be found here: http://msdn.microsoft.com/en-us/library/windows/desktop/ms675544(v=vs.85).aspx 

Some more info on using the various types of Recordset and what they do and don't support: http://msdn.microsoft.com/en-us/library/windows/desktop/ms681510(v=vs.85).aspx 

PS: the error that you get when using .MoveLast en First should also be clear from the links above.

Here's the code that I used for testing (so taking the easy way with the Const's but forcing myself to be Explicit to avoid 'sloppy' errors)
<%
Option Explicit

Dim conn, rs, sql, i


'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- CursorOptionEnum Values ----
Const adHoldRecords = &H00000100
Const adMovePrevious = &H00000200
Const adAddNew = &H01000400
Const adDelete = &H01000800
Const adUpdate = &H01008000
Const adBookmark = &H00002000
Const adApproxPosition = &H00004000
Const adUpdateBatch = &H00010000
Const adResync = &H00020000
Const adNotify = &H00040000

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4


Response.Write("Hello ")
Response.Write("<BR>adApproxPosition = " & adApproxPosition)


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, adOpenKeyset

'response.write "<BR>test before: " & rs.RecordCount

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

rs.Close
conn.Close

%>

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Smart quotes being changed on insert 9 50
UTC (timezone) without using an API 16 41
JQuery to parse xml string and get element by id 4 24
is this a cms? 8 36
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now