Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Jun 28, 2000

Counting the Number of Records in
a Recordset

Editor's Note: This tip is an update to a previous Tip of the Day. Jai Bardhan claims to have a "better solution" for counting records in a recordset.

It is true that the Recordset object, created by the Open or Execute functions of the ADODB object, has a RecordCount property that reports '-1,' if the Recordset has been opened with a forward-only cursor (default). However, you can easily get the proper count using either of the two methods written below. You don't have to loop through all records and increment a variable, which can be a performance bottleneck. If your recordset has a large number of rows returned, then there's no reason to iterate through all of them.

The two methods are named Method1 and Method2. With Method1, you can use adUseClient to get the recordcount (utilizing disconnected recordset):

 Private sub Method1  
        Dim objRecordset As New ADODB.Recordset 
        Dim RecordCount As Long 
        Dim objMyConn As New ADODB.Connection 
        RecordCount = 0 
    
        'IMPL Replace with your specific string like "Provider=SQLOLEDB;Data   
        ' Source=SQLserver;Initial Catalog=yourdb;User Id=sa; Password=;" 
        objMyConn.ConnectionString = "Your connection string" 
    
        ' Added line for disconnected recordset 
        objMyConn.CursorLocation = adUseClient 
    
        objMyConn.Open 

Set objRecordset = objMyConn.Execute("select * from memberpolicy") 'IMPL Not required 'While Not objRecordset.EOF 'RecordCount = RecordCount + 1 'Debug.Print RecordCount 'objRecordset.MoveNext 'Wend Debug.Print objRecordset.RecordCount

end sub

Method2 uses the Movelast method on the recordset instead of looping through:

 Private sub  Method2  
        Dim objRecordset As New ADODB.Recordset 
        Dim RecordCount As Long 
        Dim objMyConn As New ADODB.Connection 
        RecordCount = 0 
    
        'IMPL Replace with your specific string like "Provider=SQLOLEDB;Data   
        ' Source=SQLserver;Initial Catalog=yourdb;User Id=sa; Password=;" 
        objMyConn.ConnectionString = "Your connection string" 
       
        objMyConn.Open 

        Set objRecordset = objMyConn.Execute("select * from memberpolicy") 
        ' IMPL Not required 
        'While Not objRecordset.EOF 
        'RecordCount = RecordCount + 1 
        'Debug.Print RecordCount 
        'objRecordset.MoveNext 
        'Wend 
        ' Add this call instead of looping through the recordset 
        objRecordset.MoveLast 

        Debug.Print objRecordset.RecordCount 

end sub

Jai Bardhan
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap