How to get a record count using ADO in visualbasic

I got a select statement like this:
dim sql as string
sql = select * from table1 sql

If Not RS.EOF Then
        rscount = RS.RecordCount
        rscount = 0
    End If

I have records in table but i get rscount = -1
i got 44 records, how to get number of records count


Who is Participating?
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
set the cursortype property to adOpenStatic
The bad news is that whether RecordCount returns a value depends on the provider and type of cursor that you are using.  The default cursor type is forward only which does not support RecordCount.

Try changing your open to

rs.Open sql, , adOpenStatic

Static cursors return RecordCount for the SQL Server OLEDB provider.
Anthony PerkinsCommented:
Actually I beg to differ the RecordCount property depends on the CursorLocation not the CursorType.  The CursorLocation should be set to adUseClient.

What is happeing is that for some providers setting the CursorType to adOpenStatic will implicitly set the CursorLocation to adUseClient.  But this is Provider dependant.

As a side note, you probably should not be using the RecordCount property in the first place.

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Anthony - actually you're both half right...

The ability of the recordset to return its recordcount depends on a number of factors including the provider and cursor location, but however you're opening your recordset, check .Supports(adApproxPosition) or .Supports(adBookmark) returns true, if so then the recordcount property will return the exact number of records in the Recordset.

Recordcount will always return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and -1 or the actual count for a dynamic cursor.
if all you want is a count of how many records meet your requirement then why not simply return the count in the Query?

dim sql as string
dim rsCount as Integer

sql = "select count(*) as Recordcount from table1" sql

rsCount = rs("RecordCount")

then you can proceed , if the count is >0:

if RsCount > 0 then
   sql = "Select * from Table1" sql
   MsgBox "There Are no records to retrieve", VBOKOnly
end if

Anthony PerkinsCommented:
>>Anthony - actually you're both half right...<<
I guess you must be talking about my left half and my right half <g>

>> .Supports(adApproxPosition) or .Supports(adBookmark) returns true<<
The first is correct the second is not (see the next point)

>> Recordcount will always return -1 for a forward-only cursor<<
This is not true, try this code and you will see what I mean:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
With rs
    .Source = "select * from table1"
    .ActiveConnection = "Provider=SQLOLEDB.1; ..."
    .CursorType = adOpenForwardOnly
    .CursorLocation = adUseClient
    .LockType = adLockReadOnly
    .Open Options:=adCmdText
    Debug.Print .RecordCount
End With
Set rs = Nothing

Again, the use of the RecordCount property should not be encouraged.  Not least of its evils, is that it is provider dependant.

Having said that the facts speak for themselves.

fair enough - I was only quoting MSDN - but it wouldn't be the first time MS were wrong, would it?

Anthony PerkinsCommented:
Arthur_Wood has given the correct answer (among several that I can think of) as to how to calculate the record count.  This is totally Provider/CursorType/ CursorLocation independent.

Why do you need "rscount"?
Q: What do you think, which is faster: Getting the record count or retrieving all records?

I use GetRows to get data into variant array. To show record count, I use:
     numitems = UBound(avarData, 2) + 1

before "select Statement"

u have to write   rs.cursorlocation = aduseclient
then u will get

 if rs.state = adstateopen then rs.close

 rs.cursorlocation = aduseclient "select Statement",connection


If Not RS.EOF and RS.BOF Then
       rscount = RS.RecordCount
       rscount = 0
   End If

Or change your cursor type as suggested above
vmandemAuthor Commented:

I appreciate your response and the answer.

I really appreciate other comments too.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.