?
Solved

ASP - Record count from SQL Server

Posted on 2013-01-20
5
Medium Priority
?
1,254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 53

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 1000 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 1000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

801 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