Solved

ASP - Record count from SQL Server

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
is this a cms? 8 73
Help with query 3 37
Html Table Looping (part 2) 5 35
IIS  Displaying detail errors with the friendly http error pages for Classical ASP pages 5 22
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

733 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