How to count the Rows on a Recordset?

Hi team!

How can I count the rows on a Recordset?
I noticed that Recordset.FieldCount counts the numbers of Fields, not rows.

Example:

Table 1

McKein’s Pet SHOP
ID Kind of Animal Price Tax Total Price
00528 Schnauzer Dog 400.00 58.00 458.00

This table has one row, So If I test it with Msgbox "Item " + NumPet, It shows me the MessageBox five times.

What can I do?

Recordset.RecordCount

if you want to do it inside your query use SQL function COUNT() e.g.: SELECT COUNT(YourID) as YourName FROM AnyTable"

For MySQL I use this generic way:

select count(*) as 'Count' From ( <<Your Subquery Here>> ) as x

Be careful using Recordcount of the Recordset object. It’s not working for all types of databases. I would prefer using just a query as shown above.

On MS databases the recordset record count was only an ‘estimate’. To get an accurate record count of any recordset you needed to do a RS.MoveFirst and a RS. MoveLast. Weird machine cycle gobbling methodology, but it worked well.

@Chris Benton - why not just use the simple SQL-query and let the database do the work, instead of moving around within the recordset and probably giving bad performance.

Its one of those things I learned that worked and never ventured beyond those boundaries, besides that, I found that the MSJET database engine did not always maintain an accurate record count after every SQL query was thrown at it. I often found that it was easy to end up with fresh air instead of another record when iterating through thousands of records because the recordset count indicated the wrong number of records. I got pretty good with SQL, but not with the nuts and bolts that made it work.

That really depends on the database and on the implementation of the database class in Xojo. The docs say, for which database servers the RecordCount returns a valid result directly after getting the record set.

@Chris: I am not sure if this is a good method. I think with MS Databases (MDAC or SQLServer) this was a matter of serverside or client-side cursor. You cannot jump back and forth when stepping through your Recordsets.

While that is sometimes true, it should not be presented as a general statement.

Using appropriate cursors, I never had any problem with recordcount on MS SQL Server using MDAC. That would be either with VB6 or Xojo, both using OLE Objects to create the recordsets.

This exerpt from a MS page tells the story:

[quote]The value of the RecordCount property also depends on the capabilities of the provider and the type of cursor used. For a forward-only cursor, the value is -1. For a static or keyset cursor, the value is the actual number of records returned in the Recordset object. For a dynamic cursor, the value is either -1 or the actual number of records, depending on the data source.
A cursor that supports Recordcount must work harder, and therefore requires more computing power, than a cursor does not support Recordcount. If you do not need to know the number of records, using different cursor type might help improve your application’s performance, especially if you must deal with a large data set.
In some cases, a provider or cursor is unable to determine the RecordCount value without first fetching all records from the data source. To ensure accurate counting, call the Recordset.MoveLast method before calling Recordset.RecordCount.
The sample Recordset object obtained using the JScript Code Example uses a forward-only cursor, so calling RecordCount on this object always results in –1.
If you change the line of code that calls the Recordset.Open method as shown in the following example, the RecordCount property will return the actual number of records fetched.[/quote]

the page is found here.

The only database I have found where the Record Count from a RecordSet was inaccurate was MSAccess… SQLite, mySQL, Oracle all give the correct answer.

And while “SELECT COUNT(*) FROM table” (with an optional where clause" also gives a count, sometime you need to return the contents AND know how many you got,

Maybe it explains why I’m getting two values of a record, I think that is for using RecordCount.

Example.

When I have 1 record in the table. It shows One Record

But when I have two or more records in the Table, It shows each record as twice. like this:

Original Table:

  1. Melons
  2. Grapes
  3. Stawberries

And it shows like this:

  1. Melons
  2. Melons
  3. Grapes
  4. Grapes
  5. Strawberries
  6. Strawberries.

And this Is How I’m making the Query, and I’m Use mySQL as database:

sqlConceptos = “SELECT Cantidad, Unidad, Descripcion, Valor_Unitario, Importe FROM Conceptos_Facturas_recibidas” + " WHERE Sello_Digital_UUID = ‘"+ compro.FolioFiscal +"’"

Dim articulo As RecordSet
articulo = app.mDb.SQLSelect(sqlConceptos)

If app.mDB.Error Then
MsgBox("DB Error: " + app.mDB.ErrorMessage)
'Return
End If

Ah… that is a different question :slight_smile:

you would use a group by statement which will give you a distinct listing for product name and how many records are associated. here is a basic example:

select product_name, count(*) as product_count
from T_PRODUCTS
group by product_name;

or… if you want to find the count of distinct product names you can do:

select count(distinct product_name) as product_count
from T_PRODUCTS

[quote=203031:@Rich Hatfield]Ah… that is a different question :slight_smile:

you would use a group by statement which will give you a distinct listing for product name and how many records are associated. here is a basic example:

select product_name, count(*) as product_count
from T_PRODUCTS
group by product_name;

or… if you want to find the count of distinct product names you can do:

select count(distinct product_name) as product_count from T_PRODUCTS [/quote]

Noo, I dont wanna Group that.

My records are shown two times, instead of only one.

I try a simple example, just for count all the records on the table. Later I will Add the CLAUSE “WHERE” to refine the query:

sqlConteo = “SELECT COUNT (*) AS Product_Count FROM Conceptos_Facturas_recibidas”

But I got an MySQL Syntax Error.

Wnat Am I doing wrong?

[quote=203033:@Gerardo García]Noo, I dont wanna Group that.

My records are shown two times, instead of only one.

I try a simple example, just for count all the records on the table. Later I will Add the CLAUSE “WHERE” to refine the query:

sqlConteo = “SELECT COUNT (*) AS Product_Count FROM Conceptos_Facturas_recibidas”

But I got an MySQL Syntax Error.

Wnat Am I doing wrong?[/quote]
The weird part of it is that I can do the query in MySQLWorkbench without problems.

But when I try to do it on Xojo I get this Syntax Error

two things:

  1. can you show the code you are using to extract the recordset data?

  2. what is the syntax error you are getting?

[quote=203037:@Rich Hatfield]two things:

  1. can you show the code you are using to extract the recordset data?

  2. what is the syntax error you are getting?[/quote]

  3. For get the Number of Records, At this time I Only made the Query, If I get no error then I’ll make the code for extract the recordset data:

Dim sqlConteo As String
'Donde Selector es la variable que decide si mostrar las facturas recibidas o emitidas
sqlConteo = “SELECT COUNT (*) FROM Conceptos_Facturas_recibidas”

//Establecemos un Recordset para llenar la lista de facturas recibidas
Dim rsConteo As RecordSet
rsConteo = app.mDb.SQLSelect(sqlConteo)

If app.mDB.Error Then
MsgBox("DB Error: " + app.mDB.ErrorMessage)
'Return
End If

Just for get all the Records on that table. If I do this on MySQLWorkBench It works flawlessly.
But when I do this on Xojo, Shows me this error: You have an error in your SQL Syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘*) FROM Concepts_Facturas_recibidas’ at line 1

This may be a reach… .but is there a Space between COUNT and (*)?
some DB engines dont’ care, but mySQL just "might’ or the way XOJO handles it just might

I catch the error. I put the Recordset.MoveNext outside the Loop counting RecordCount instead of Inside the loop. :stuck_out_tongue: :stuck_out_tongue:

Finally works

I got a ne question, I decided not open a new thread.

So Here I go.

I have a mySQL Query and a Recordset, but I want to separate the first Record of the other records.
Example, I have this code:

  sqlMeses = "SELECT DISTINCT date_format(Comprobante_Fecha, '%m') FROM facturas_" + Selector(1) + " WHERE Emisor_RFC = '"+ ClienteRFC +"' AND Comprobante_Fecha BETWEEN '"  + InitialDate  + "' AND '" + FinalDate + "' ORDER BY Comprobante_Fecha"
  rsMeses = app.mDb.SQLSelect(sqlMeses)
  
  If rsMeses <> Nil Then
    While Not rsMeses.EOF
      
      xl.AddWorksheet(rsMeses.IdxField(1).StringValue.DefineEncoding(Encodings.UTF8))
      rsMeses.MoveNext
    Wend
    rsMeses.Close
  End If

As you can See I made a Query in order to know how many Months have in One year registered of certain Customer.
Maybe In 2016 I have records from May, June and July
Maybe in 2015 I have records from Jan,Sept And December

So For each month encountered in the query I add a row.

But thats no the question.
The question here is, how can I separate the first record from the others?

Thats for give it to the first month encountered a different treatment than the others.
I tried to see if theres an integer value that increases each time that cross the “While”, in order to do an “IF” and exclude “1”

Regards

Sorry.

I solved at this way.

I made a Integer property, and add 1 each time loops until the EOF.
And then evaluate that Integer Property, If equals “1” Do this, and If its different than “1” do that.

:smiley: :smiley: