• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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


0
limei
Asked:
limei
  • 3
  • 3
  • 2
  • +2
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now