recordCount blues....

[BlueByte]

hi!
the problem is as under..

s="select * from emp"
rs=new adodb.recordset
rs.open s,Conn, adOpenForwardOnly, adLockReadOnly

If rs.BOF = False Or rs.EOF = False Then
For k = 1 To rs.RecordCount
foo
foo
next k

issue: tho the query returns 1 or more than 1 records,
rs.recordCount is returning -1 !!

in rdo i used to make use of .rowCount...in ado,it doesnt
seem to work.

why am i getting -1?
any alaternatives?

thnx,
bluebyte

 

[SonOfEmidec1100]

Use this method to loop thru the recordset

If not (rs.BOF AND rs.EOF) Then
Do Until rs.eof
foo
foo
Loop
end if

Extract from msdn re ado and recordcounts

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

 

[Omkar158]

Hi,
if you are use rs.open s,Conn, adOpenForwardOnly, adLockReadOnly then avoid the statement :

If rs.BOF = False Or rs.EOF = False then

try :

if rs.recordcount > 0 then
rs.movefirst
do while rs.eof = false
aa = 1
rs.movenext
loop
else
msgbox "file is empty"
endif

 

[CCLINT]

>if you are use rs.open s,Conn, adOpenForwardOnly, >adLockReadOnly then avoid the statement :

>If rs.BOF = False Or rs.EOF = False then

May I ask why not?
The BOF and EOF statement should be used.

The rest of your code is also incorrect and will not work properly.

An adOpenForwardOnly will always return -1, and so your code will always return "file is empty".

The only time this will work is if you are using a client cursor (not the case above), and then it is not a adOpenForwardOnly cursor anymore (no matter if you wanted one by setting this cursor type) but changes to a Static cursor instead. A Client side cursor here is always Static to the source, but updatable to the Client Cursor.
Then the adOpenForwardOnly is a useless/meaningless argument.

BlueByte:
>rs.recordCount is returning -1

That's because of the adOpenForwardOnly cursor.

You have 3 choices:

1. You could change the cursor type to Static or Keyset.
You may need to then issue a MoveLast and MoveFirst to make sure the count is accurate.
2. Or, just use a Client cursor (rs.CursorLocation = adUseClient) and then the record count should be correct - However, all of the records will be pulled in locally.
May not be what you want, actually defeating the purpose of a ForwardOnly cursor.

3. Or, if it needs to be a adOpenForwardOnly server cursor, you will need to issue a second query to count the records. This is done effeciently by using:

Dim lRecordCount As Long
If Not (rs.BOF and rs.EOF) Then
'(We now know there are records in the rs, but do not know how many)
set rs2 = conn.Execute "SELECT COUNT(*) FROM SomeTable",,adCmdText
lRecordCount = rs2.Collect(0)
rs2.Close
Else
'No records
End If
set rs2 = nothing

Now we have an approx. record count for a Forward Only Cursor.
I say "approx." because one or more records could have been added by some other user(s) in the time between the first rs and the second rs were opened.
Therefore, always use a EOF check if you are using the record count for anything other than information purposes.
(You could also use a SP with a return count).

 

[SonOfEmidec1100]

Missed an important line in my reply

If not (rs.BOF AND rs.EOF) Then
Do Until rs.eof
foo
foo
rs.movenext
Loop
end if