Hello friends
Xojo
Very grateful for your attention and for your responses to resolve the following:
How could I determine the number of records in a recordset obtained from an MS SQL SERVER database?
the recordcount is not supported by MS SQL SERVER
The version of xojo that I use is 2017
Cordially,
Raul Juarez Pulache
when record count is not supported you end up doing the query twice
some may suggest doing it once & just copying all the data locally but with a large # of records that may not be practical
maybe with select case(*) as c from table where …
or count self in a loop
[quote=465401:@Norman Palardy]when record count is not supported you end up doing the query twice
some may suggest doing it once & just copying all the data locally but with a large # of records that may not be practical[/quote]
Norman’s suggestion is a good one. And not unlike what I often do, if I have to display the results to the UI.
After getting the count from the first query, I then determine whether pagination is required (to manage more results that can practically be displayed, as Norm says). So, based on the total count, the second query is dynamically modified to only retrieve say the first 50 rows to display to the user. Subsequent queries than step through and grab the next 50, and so on. From the total count and bit of math, you can predetermine how many pages of 50 there will be.
This way your application is not overloaded with an unmanageable number of records. SQL Server pagination can be achieved either with cursors or T-SQL Window pagination functions.
Pulling back all the results then counting them is especially problematic in web applications, where browsers and not meant to display 10’s of thousand of rows in an HTML table. Plus you retain all that data in memory, at least temporarily, while you count the total. You gain more in performance with two queries, when you know the potential results are in the thousands or more.
Also, when it comes to SQL Server SELECT statements, check out the use of WITH (NOLOCK) to speed up your queries. But be careful where you use NOLOCK, this modifier is not a magic bullet for all types of SELECT queries.
Have fun 
Hello
Test
Select field1, field2, field3, count(*) over() As total
From table;
Mauricio.
Infinite Thank you
Norman Palardy
Markus Rauch
Scott Cadillac
Mauricio Pulla
With your great help I was able to solve the problem
Raul Juarez Pulache