Jump to content
  • Advertisement
Sign in to follow this  

[web] ASP+Access: multiple recordset error

This topic is 4884 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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?

Share this post

Link to post
Share on other sites
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:

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.

Share this post

Link to post
Share on other sites
Sign in to follow this  

  • Advertisement

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!