.RecordCount = -1 ??? 3

[vza]

i have a question with the .RecordCount method...

Private Sub UsernameCombo_Change()
    ' Variables
        Dim Partial As String
        Dim i As Integer
        
    ' RecordSets
        Dim rsSelect As New ADODB.Recordset
    
    Partial = UsernameCombo.Text
    If Partial <> &quot;&quot; Then
    ' Find closest stored value to Partial string variable
            ' Open RecordSets
                rsSelect.Open &quot;Select * From table Where Field1 Like '&quot; & Partial & &quot;%'&quot;, Cn, adOpenStatic
               If rsSelect.EOF <> True Then
                    Partial = rsSelect!Field1
                
                For i = 0 To rsSelect.RecordCount
                    If Partial <> UsernameCombo.List(i) Then
                         UsernameCombo.AddItem (Partial)
                         rsSelect.MoveNext
                    Else
                         Exit For
                    End If
                Next i
               End If
End Sub

I know the recordset returns more than one record...why is the RecordCount method returning a value of -1???? (during debugging) Any response would be greatly appreciated.

Thanks
-vza

 

[CassandraR]

Hi vza:

There is a problem with RecordCount. Microsoft has advised not to rely on it for checking if the record count is non-zero.

The better way to handle the situation is:

    If not (rsSelect.BOF And rsSelect.EOF) then
        ' Recordset has at least one record.
        '  .RecordCount is meaningless here.
        rsSelect.MoveLast    ' To correct .RecordCount
        '  .RecordCount now is correct.
        rsSelect.MoveFirst
        '  Process the recordset.
    Else ' Recordset has no records.  
        '  .RecordCount is meaningless here.
    End If

HTH,
Cassie

 

[vza]

Thanks for the help Cassie!

I was wondering why it wasnt working...i thought i might of had something to do with it.....
I will try your suggestion instead.

Thanks
-vza

 

[CassandraR]

Good luck!

I tried to figure out what you were doing with the Sub, but I am not sure.

Thanks for the star!

Cassie

 

[CCLINT]

vza, try this:
Either use a client side cursor:

rsSelect.CursorLocation = adUseClient
rsSelect.Open "Select * From table Where Field1 Like '" & Partial & "%'", Cn

or use this:

rsSelect.Open "Select * From table Where Field1 Like '" & Partial & "%'", Cn
rsSelect.MoveLast
rsSelect.MoveFirst

If that doesn't help, then tell use what provider you are using, or better, post the connectionstring, and also after the recordset is opened, get the value of rsSelect.CursorType

You can also use a second recordset object to get the count:
Set rsCount = Cn.Execute "SELECT COUNT(*) FROM table"
If not IsNull(rsCount.Collect(0)) Then lMyCount = rsCount.Collect(0)
This is pretty fast.

There shouldn't be any real problems with using the RecordCount method.
It will not work with a ForwardOnly cursor, and will otherwise not be accurate if there are alot of records and using a server side cursor (actually depending the the number of records which are being cached), until all records have been at one time retreived into the cache - therefore the use of MoveLast/MoveFirst in the example.
And, when using a server side cursor, not all providers are able to provide a recordcount.

 

[VBDemiGod]

I believe CClint is right about the Client side cursor. gotta have it to traverse the recordset and get properties like recordcount, absolutepage, etc...

 

[CassandraR]

Hi vza:

Microsoft's article concerning a problem with RecordCount giving an erroneous count:

http://support.microsoft.com/default.aspx?scid=kb;en-us;109053&Product=vbb

In particular, look at the Loops and RecordCount section.

Cassie

 

[vza]

I appreciate the help all...
Sorry for such a late response...was working on another project for a few days....

CCLINT,
setting my Cursor Location solved the problem.

Cassie,
I will check out the link you provided.

Much Obliged!
Stars all around.

Have a good weekend!

Thanks
-vza

 

[CCLINT]

The (old) article unfortunately is turned to DAO and doesn't supply sufficient information or work-a-rounds for the different ADO cursor locations, processing types, and cursor types, so do not be mis-lead by it.
Please be aware that the recordcount=-1 is NOT a bug, or an error, but a unavoidable condition due to a the way data is retreived using a Server side cursor and some providers.

Doing the method the article mentions is more of a "last result", when working with large amounts of data, and ESPECIALLY when doing async processing (the recordcount property OR MoveLast method kills any benefits of async processing). It will also kill any benefits of pulling in pages of data on an as-needed basis.

Often it is better, to just use a seperate SELECT COUNT(*).

You should anyways be not using the RecordCount with a server side cursor for much of anything (client side cursors are OK) unless you know that all records have been already retreived into the cache at one time or another.
Always use EOF/BOF to see if there are more records and use a Do Until .EOF Loop to move through all records.

With DAO, almost always the best thing to with a large recordset is to run a seperate SELECT COUNT(*).