Hi guys,

I'm having trouble returning paged data from ms access 2003 (MDB).

This is for an asp.net page, so I simply need to give my page number as an argument, and for 5 records to be returned.

I have 1,300 records, and I only want to retrieve 5 records a time - starting at index X.

This is what I have so far:

SELECT round((Count([ID]) / 5) + 0.49) AS Pages
FROM tblEmployee;

This snippet returns to me how many pages of 5 records there are.

so for 1300 Records the value returned is 260 (pages).

to view page 1 I can simply do:
SELECT TOP 5 tblEmployee.Title, tblEmployee.FirstNames, tblEmployee.LastName
FROM tblEmployee;

but this always returns the top 5 values.

PARAMETERS PageNo Short;
SELECT TOP 5 tblEmployee.Title, tblEmployee.FirstNames, tblEmployee.LastName
FROM tblEmployee;

So I want the SELECT-ing to start at the index of "5 * PageNo".

I could quite easily return all the records and do my workings in ASP.net, but the trouble is pulling 5 records across the net is easy and pulling 1300 records across the net is insanely not needed.

So is it possible?

Thanks,

Smithy.

p.s. I Omitted there rest of the tables column names for simplicity.

NOTE:
without an order by statement any query is by default random in order.
Thus what ever happens to be on page one in the first query, when executing the same query a second time does not guarantee the same 5 records are the top 5.
Chances are relatively minimal, but important to take note of.

Now, The trick here is to do Select Top 5 for your first page.
Then your second page is the same query, except outer joined with a query that does "Top 5" again.
This will "remove" the first top 5 from the query and return the next top 5...

Alternatively you can add a "Row number" to your query (rownum in oracle for example)
Then instead of "Top 5" you would use Where Rownum > 5 and <= 10 or something simular....

Good luck on your project & Welcome to AWF

Thanks for the reply,

You have enlightened me of how the process works, I didn't seem to pick it up while reading else where >.>

I did some more searching and asked my tutor, he said for this assignment don't worry. So i will be using a brilliant ASP.net paging method I found at this web address...
Asp.net / Access Data Source / Paging Method (http://aspnet.4guysfromrolla.com/articles/081804-1.aspx)

My apologies if links to external sites is not allowed.

Thanks again,

Smithy.