Solved

How to get a record count using ADO in visualbasic

Posted on 2002-04-18
12
204 Views
Last Modified: 2010-05-02
I got a select statement like this:
dim sql as string
sql = select * from table1
rs.open sql

If Not RS.EOF Then
        RS.MoveFirst
        rscount = RS.RecordCount
  Else
        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

vm

0
Comment
Question by:vmandem
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 35 total points
ID: 6951279
set the cursortype property to adOpenStatic
0
 

Expert Comment

by:lschuele
ID: 6951297
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6951389
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.

Anthony
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 4

Expert Comment

by:nutwiss
ID: 6951491
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.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6951559
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"
rs.open sql

rsCount = rs("RecordCount")

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

if RsCount > 0 then
   sql = "Select * from Table1"

   rs.open sql
else
   MsgBox "There Are no records to retrieve", VBOKOnly
end if


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6951567
>>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
    .Close
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.

Anthony
0
 
LVL 4

Expert Comment

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

:)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6951872
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.

Anthony
0
 
LVL 15

Expert Comment

by:ameba
ID: 6952115
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
0
 

Expert Comment

by:talluri_28
ID: 6952569
hi

before rs.open "select Statement"

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

 if rs.state = adstateopen then rs.close

 rs.cursorlocation = aduseclient
 rs.open "select Statement",connection
 rs.recordcount

jag



0
 
LVL 2

Expert Comment

by:corvanderlinden
ID: 6952803
If Not RS.EOF and RS.BOF Then
       RS.MoveLast
       RS.MoveFirst
       rscount = RS.RecordCount
 Else
       rscount = 0
   End If

Or change your cursor type as suggested above
0
 

Author Comment

by:vmandem
ID: 6954840
emoreau

I appreciate your response and the answer.

I really appreciate other comments too.

VM
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA: Select SQL query based on a config Sheet v2 11 55
how to Classify of email (pdf attachments) 7 56
backup program with robocopy 6 90
VB script to continue despite error 2 77
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question