Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ASP - Record count from SQL Server

Posted on 2013-01-20
5
Medium Priority
?
1,299 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 54

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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 informatio…
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

597 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