Recordcount of Recordset

[rzirpolo]

I have declared a variable as an ADODB.Recordset. It all works fine but I want to find out the number of records the recordset returns.

There is a property called RecordCount but this is set at -1 ?

Am I doing something wrong or not looking at the right property ?

 

[SteveGlo]

I believe it depends on what type of cursor you are using to open the recordset.

If you are using the Dynamic cursor the number of records is unknown. Suggest you see what happens if you use Keyset or Static or ForwardOnly

 

[SteveGlo]

found a fairly definitive description of problem at:

http://www.vb-faq.com/Articles/Greener/reccount.asp

 

[rzirpolo]

OK I follow what is being said in regards to the CursorType, but I get an error stating "Compile Error: invalid use of property"

My syntax is thus..

Dim ad As ADODB.Command
Dim rs As ADODB.Recordset

Set ad = New ADODB.Command
Set ad.ActiveConnection = mobjCnn

ad.CommandText = "sp_procedure"
ad.CommandType = adCmdStoredProc

Set rs.CursorType = adOpenDynamic
(^ error occcuring on this line ^)

Set rs = ad.Execute

 

[happyabc]

Yes, its a good article; but it seems to me the author puts things a little tangentially. He also seems to be saying, as SteveGlo does, that only countable Recorsets support the property. However he becomes a little dense when he brings bookmarkability into the picture. Perhaps there is a one to one relationship between countability and bookmarkability and he's trying to provide a way of determining countability in code ( maybe there is no adRecordCount property )?

 

[TJRTech]

That article stinks.

Dynamic will still return RecordCount of -1.

I am pretty sure that you should either use "Static", or you should simply fetch all your records keeping a counter, or interrogate the counter after rs.EOF.

I think more than anything you are hitting and ODBC limitation, or an ADO design that is based on an ODBC limitation. In ODBC, you cannot get the record count until the cursor has moved to the end of the recordset when dealing with a server side cursor. That is just the way it is. I suspect that the designer of ADO set up the same limitation for server side cursors in ADO, because often developers use ADO to access ODBC, and because of the inherent protocol coming from SQL server.

TR

 

[happyabc]

Dynamic cursors RecordCount returning -1 is as it should be according to the author.

 

[cjac]

Try using the client side cursor, ie.
Set ad = New ADODB.Command
Set ad.ActiveConnection = mobjCnn

ad.CommandText = "sp_procedure"
ad.CommandType = adCmdStoredProc

Set rs.CursorType = adUseClient

 

[ArtieChoke]

A keyset cursor will return the record count, assuming the database supports it.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[rzirpolo]

Using an adUseClient or adOpenKeyset cursor type returns the error 'Invalid use of property' ?

Am I coding it wrong ?

Dim ad As ADODB.Command
Dim rs As ADODB.Recordset

Set ad = New ADODB.Command
Set ad.ActiveConnection = mobjCnn

ad.CommandText = "sp_procedure"
ad.CommandType = adCmdStoredProc

Set rs.CursorType = adOpenKeyset
Set rs = ad.Execute

 

[JeffTullin]

Set rs.CursorType = adOpenKeyset

should probably just be

rs.CursorType = adOpenKeyset

as the item on the right of the equals sign is not an object, just a value.

 

[rzirpolo]

When typing the syntax the options I get are,

adOpenDynamic
adOpenForwardOnly
adOpenKeySet
adOpenStatic

But when I try any of these I get an error referring to another procedure stating 'Object variable or With block variable not set'. This may not be much help as it may be a problem in how the program was originally created.

Any advice would be helpful though.

 

[rzirpolo]

Sorry... my mistake. I forgot to Set the Object. But still this makes no difference as for each Cursor the Recordcount returns as -1.

Any help ?

 

[fredericofonseca]

If you are using access it will NOT return the recordcount.
If you are using Oracle it will.

It is database/driver dependent, and it s...s.

Just as an example this is how I HAD to do it to work with more than one DB type

Function loadcol() As Variant
    Dim rssSet As rdoResultset
    Dim nrows As Long
    Set dbQuery = New RDO.rdoQuery
    Set dbQuery.ActiveConnection = g_dbConn
    dbQuery.CursorType = rdOpenKeyset
    
    sSQL = "select abc from table_name"
    dbQuery.SQL = sSQL
    dbQuery.Execute

    nrows = dbQuery.OpenResultset(rdOpenKeyset, rdConcurReadOnly).RowCount
    If nrows < 0 Then
        nrows = 100000
    End If
    
    loadcol = dbQuery.OpenResultset.GetRows(nrows)
    dbQuery.Close
        
End Function

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[dilettante]

You can always resort to RTFM:

RecordCount Property

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