RecordCount property for VB Script

Any idea why the Recordset.RecordCount method for ADO does NOT return the correct count ? It always returns the value 1 (unlike Visual Basic which return the total number of records in the table). Here is my scenario:
I have 10 records in a table which are accessed by the following code:

Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open DSN, USER, PASSWORD
SQLText = "SELECT Name FROM CustTable"
Set RSName = Server.CreateObject ("ADODB.Recordset")
RSName.Open SQLText, Connection
      
if RSName.RecordCount = 0 Then
      Response.write "No record found"
Else
      Perform other processes
End If


limeiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

micwongCommented:
You need to add specific parameters to configure the type of recordset. You may try the following codes to retrieve your recordset:

RSName.Open SQLText, Connection, adOpenKeyset, adLockOptimistic

, where constants adOpenKeyset = 1 and adLockOptimistic = 3.
0
limeiAuthor Commented:
These values which you have specified only affects the direction of the cursor or pointer to the recordset. It does not solve the problem for the RecordCount
0
Yury_DelendikCommented:
Why?!
....
if RSName.EOF Then
Response.write "No record found"
Else
Perform other processes
End If

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

limeiAuthor Commented:
Using RSName.EOF alone does not accurately indicate an empty table. You would need to use RSName.EOF = TRUE and RSName.BOF = TRUE.
However this still does not answer my query about the inaccuracy of the RecordCount property. What if i need to check the total recs (without using Select count SQL statement)
0
Yury_DelendikCommented:
Really?

[Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. ]

And you must specify CursorType and  LockType as micwong said.
0
rcw119Commented:
The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.

If the Recordset object supports approximate positioning or bookmarks—that is, Supports (adApproxPosition) or Supports (adBookmark), respectively, return True—this value will be the exact number of records in the Recordset, regardless of whether it has been fully populated. If the Recordset object does not support approximate positioning, this property may be a significant drain on resources because all records will have to be retrieved and counted to return an accurate RecordCount value.

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
reido2Commented:
rcw119 is correct you cannot get a recordcount of an EOF recordset. You will have to use a count variable to get the correct number of records

<%...
dim count
count=0
rsname.movefirst

while not rsname.eof
count=count+1
wend

if count = 0 Then
Response.write "No record found"
Else
Perform other processes
End If

....%>






0
limeiAuthor Commented:
Thank you very to each of you for your comments and views on this. I appreciate it.
To rcw119 specifically, thanks for being so detailed in your explanation.
0
Yury_DelendikCommented:
Fine! "...To rcw119 specifically, thanks for being so detailed in your explanation..." or microsoft? :)
http://msdn.microsoft.com/library/psdk/dasdk/mdap1aes.htm
0
rcw119Commented:
I'll never deny that the horses mouth is the best place to go.  You tend not to miss the finer parts of the explaination that way.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.