Server-side Paging with SQL Server

Hi all, hoping this one is an easy one to answer....
I have an asp page connecting a SQL database, retrieving partial data (ie 20 rows), displaying in a table and disconnecting. I have a feature to go back X, forward X, and everything works fine.

The one missing piece of my puzzle is the "displaying x of y" part. How do I find out the total # of records when I'm only returning 1 page of data? I know how to get x, not sure how to get y.
Here's a sample of the technique I'm using:

===========================================================
SELECT * FROM table WHERE primary key IN

(SELECT TOP page_size primary_key FROM table WHERE primary_key NOT IN

       (SELECT TOP page_size * (page_number - 1) primary_key FROM table

       WHERE filter_conditions

       ORDER BY sort_field)

       AND filter_criteria

       ORDER BY sort_field)

ORDER BY sort_field
=========================================================

Any help would be greatly appreciated!
Thanks in advance,
Yazster

Start Your Own Tek-Tips Group! Click Here!

Genimuse (Programmer)

Oct 28, 2004

You can begin with a query for the number of records, then get the actual records, a la

CODE

SELECT COUNT(*) AS MyCount FROM table WHERE primary key IN

(SELECT TOP page_size primary_key FROM table WHERE primary_key NOT IN

       (SELECT TOP page_size * (page_number - 1) primary_key FROM table

       WHERE filter_conditions

       ORDER BY sort_field)

       AND filter_criteria

       ORDER BY sort_field)

ORDER BY sort_field

after which rs("MyCount") will contain your y. (You could eliminate the ORDER BY clauses in the count SQL since they have no effect on the count, speeding it up a bit.)

Easier, if you're using a keyset or static cursor then rs.RecordCount will be your y.

Yazster (Programmer)

Oct 29, 2004

Thanks Genimuse,

I thought I may have to run a count(*) for the whole query before actually running it. I was hoping there was actually a way to do it simultaneously, without having to run an initial query just to get a count. If for whatever reason the query is a long one, getting the count could really slow down the entire process. As you said though, removing sorting will help with that.

I suppose I could run it once at the beginning, and then when users navigate back and forth through the recordset, I don't have to rerun the count.

Unfortunately, I can't use .recordcount because I'm using a ForwardOnly cursor, and only bringing back 20 or so records from the server.

Thanks for your help!
Yazster

Yazster (Programmer)

Oct 29, 2004

Actually Genimuse, looking more closely at your reply, wouldn't your COUNT(*) in the example you gave only return the count of actual rows returned by the server (for example, 20), and not the entire count of matching records?

Genimuse (Programmer)

Oct 29, 2004

Aye, you'll have to take out your TOPs, too. I shouldn't answer questions late at night. :)