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 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,
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
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”
can you show the code you are using to extract the recordset data?
what is the syntax error you are getting?[/quote]
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 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.