Solved

ASP - Record count from SQL Server

Posted on 2013-01-20
5
1,099 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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 …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

758 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

21 Experts available now in Live!

Get 1:1 Help Now