[web] ASP+Access: multiple recordset error
For the site I am making, when the results are returned from a search, if there are more than 10 results they should be split over multiple pages. The code seems like it should be working, but I am getting this error:
"Current provider does not support returning multiple recordsets from a single execution"
From what I can tell by poking around on google, this is because Access doesn’t support returning multiple recordsets. Now, this raises a few questions:
1. What exactly do they mean my multiple recordssets? Does this mean that Access cannot have an ASP page call two simultaneous SQL statements (as in, getting two records at the same time?). If so, I'm confused as to how my code is doing this, since it is just using some commands like .pagecount, .AbsolutePage, .pagesize and so forth.
2. Is it possible to split results over a series of pages with Access then?
I think we'd need to see the SQL you're using in order to pin-point why this is happening, but to re-assure you, you can page results using Access; you just have to do it in a slightly different way. [wink]
The problem with Access, as far as I can remember (it's been a while!), is that the Jet SQL engine doesn't support the LIMIT clause like SQL Server does. So you can't do something like the following to make paging really simple:
The 20, 10, would select the 21st-30th records (20 is the start position and 10 is the amount of records), which is incredibly useful for paging. However, being as Access doesn't support this, you have to do it a little differently and consequently, the performance isn't as great either.
So what to do then? Well, the way I did this ages ago, was to set a constant to be the number of records per page. Then, retrieve all of the records you want from the database and count them. Divide the total records by the records per page, add 1 to it and it gives you the page count.
Then, loop through the recordset until you come to the record to start at. From there, you just need to keep a track of the offset by passing it around in the query string.
As you can see, this method is pretty crappy. It will do what you want, but I was never happy with it. Unfortunately, as I said earlier, it's more because of a simple short-coming of Access that mucks the whole thing up.
Bummer eh? Anyway, hope that answers your question mate.
The problem with Access, as far as I can remember (it's been a while!), is that the Jet SQL engine doesn't support the LIMIT clause like SQL Server does. So you can't do something like the following to make paging really simple:
SELECT * FROM Employees ORDER BY EmployeeID LIMIT 20, 10
The 20, 10, would select the 21st-30th records (20 is the start position and 10 is the amount of records), which is incredibly useful for paging. However, being as Access doesn't support this, you have to do it a little differently and consequently, the performance isn't as great either.
So what to do then? Well, the way I did this ages ago, was to set a constant to be the number of records per page. Then, retrieve all of the records you want from the database and count them. Divide the total records by the records per page, add 1 to it and it gives you the page count.
// Just some pseudocode (in JS as well - sorry I never did VB with ASP ;))var rows_per_page = 10;var total_pages = parseInt(record_count / rows_per_page) + 1;
Then, loop through the recordset until you come to the record to start at. From there, you just need to keep a track of the offset by passing it around in the query string.
As you can see, this method is pretty crappy. It will do what you want, but I was never happy with it. Unfortunately, as I said earlier, it's more because of a simple short-coming of Access that mucks the whole thing up.
Bummer eh? Anyway, hope that answers your question mate.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement