How to count the no. of records ? 
Author Message
 How to count the no. of records ?

Hi,

        I am writing a program to count no. of record in a table
that satisfied a condition. I've tried to create a dynaset or snapshot
that satisfied the condition and then use dynaset.recordcount to
find the no. of records. but, according to VB menu, it returns only
the approximate number. So, how can I get the no. of record ?

        Idea 1: Use recordset.movenext and recordset.EOF and a counter
                variable to go through all records(seems slow :( )

        Are there any other way to count the records?

        Idea 2: I've look through the VB Professional Menu and I
                found a SQL command called count that may be useful.
                But as there is no example and I don't know much
                about SQL. How can I use it?

                The SQL statement is :

                Select Count([ship Country]) FROM Orders WHERRE [ship Country];
                = 'UK'

                How to Use it?
                I guess it should be :

                DynaSetVaiaible = CreateDynaset(SQLStmt, DB_READONLY)

                But what will it be returned, an integer in DynaSetVariable
                or an DynaSet in DynaSetVariable ?

VB HungryMan



Sun, 28 Dec 1997 03:00:00 GMT  
 How to count the no. of records ?

writes:

Quote:

>         I am writing a program to count no. of record in a table
> that satisfied a condition. I've tried to create a dynaset or snapshot
> that satisfied the condition and then use dynaset.recordcount to
> find the no. of records. but, according to VB menu, it returns only
> the approximate number. So, how can I get the no. of record ?

This is incorrect.  The .RecordCount property returns an accurate count IF
you use the .MoveLast method, first!  So the following will give u an
accurate count:

dynaset = database.CreateDynaset(SQL)

If dynaset.RecordCount > 0 Then
        dynaset.MoveLast
        accurate_count = dynaset.RecordCount
Else
        accurate_count = 0
Endif

-------------------

Note: When a dynaset is first created, .RecordCount will not contain an
accurate value BUT it will always be greater than zero if the dynaset is
not empty.
You can get further information from the KnowledgeBase (article Q109053).

...lars



Sun, 28 Dec 1997 03:00:00 GMT  
 How to count the no. of records ?

Quote:

>Hi,
>    I am writing a program to count no. of record in a table
>that satisfied a condition. I've tried to create a dynaset or snapshot
>that satisfied the condition and then use dynaset.recordcount to
>find the no. of records. but, according to VB menu, it returns only
>the approximate number. So, how can I get the no. of record ?
>    Idea 1: Use recordset.movenext and recordset.EOF and a counter
>            variable to go through all records(seems slow :( )
>    Are there any other way to count the records?
>    Idea 2: I've look through the VB Professional Menu and I
>            found a SQL command called count that may be useful.
>            But as there is no example and I don't know much
>            about SQL. How can I use it?
>            The SQL statement is :
>            Select Count([ship Country]) FROM Orders WHERRE [ship Country];
>            = 'UK'
>            How to Use it?
>            I guess it should be :
>            DynaSetVaiaible = CreateDynaset(SQLStmt, DB_READONLY)

>            But what will it be returned, an integer in DynaSetVariable
>            or an DynaSet in DynaSetVariable ?
>VB HungryMan

Hi,

        The easiest way to get around the problem is to apply the MoveLast
Method, then get the record count from from the RecordCount. When the
Movelast method is called, it fetches the whole dynaset onto the
memory, thus you will have access to the entire dynaset. Hope this
helps.




Wed, 31 Dec 1997 03:00:00 GMT  
 How to count the no. of records ?


Quote:
>    The easiest way to get around the problem is to apply the MoveLast
>Method, then get the record count from from the RecordCount. When the
>Movelast method is called, it fetches the whole dynaset onto the
>memory, thus you will have access to the entire dynaset. Hope this
>helps.

This is a easy way, that right but not the best one. JUst do the test on a big
table, of from ODBC table and you will see how long you have to wait until the
last record has been fetched ...

THis is a wasted time in some case.

So, take a 'better' approach : just ask to the database the number of record.

that is :

Quote:
> ...

>    Select Count([ship Country]) FROM Orders WHERRE [ship Country];
>            = 'UK'
> ...

and then i have the record count :

NB : in the original message , the statement was stated as :

        DynaSetVaiaible = CreateDynaset(SQLStmt, DB_READONLY)

This is wrong. the correct one can be :

        set DynaSetVaiaible = CreateDynaset(SQLStmt, DB_READONLY)
        ^^^
        rcount = DynaSetVaiaible.fields(0).value

With this method, I dont have to check before movelast for .eof = false

the movelast property (like movefirst) give an VB error if the dynaset is
empty. So, if rcount = 0, the dynaset is empty.

This is one solution, a lot of other exist, can be , in some case, quicker or
not, depending what's the need. In my case, my apps are database independant,
So, I have to take care of the performance problem.

any other subjections are welcome about this problem.

Nicolas



Wed, 07 Jan 1998 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Eliminate records of one table based on another table record count

2. Counting records in a record set

3. Record Count in a Record Set

4. Query record count

5. Record Count in VB?

6. Count Zero records as 0

7. dcount or counting records code?

8. counting records

9. Counting records returned by SQL Select statement

10. Wrong record count in code

11. How to count the records added to a table

12. depleting record counts

 

 
Powered by phpBB® Forum Software