Jump to content
  • Advertisement
Sign in to follow this  
chbrules

[web] MS/SQL LIMIT

This topic is 4891 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

As my findings have.... found, MS SQL 2000 does not have a LIMIT command for SQL queries? I tried this query:
query = "SELECT 'ProductNum', 'PartName', 'Price', 'DealerPrice', 'RetailPrice' FROM products LIMIT '" & start & "','15'"

rs.Open query, sConn
Gives me the error:
Microsoft OLE DB Provider for SQL Server error '80040e14' 

Line 1: Incorrect syntax near '0'.
When start = 0. So MS SQL doesn't have a limit command? How can I achieve the same effect if that is true? I need to make a paging system for result queries.

Share this post


Link to post
Share on other sites
Advertisement
LIMIT is not standard SQL. You'll find very, very few database that support this proprietary command. In general "TOP" is the closest standard command in SQL (to select items in the middle of a query like LIMIT does, the records before it need to be determined anyway, making LIMIT with 2 parameters somewhat of a hack that simply discards the first X records as it does the query, then does a TOP like return for up to Y records found after that). To do the same thing in your environment, you can use ADO's paging and caching system to instruct your SQL provider to skip ahead X pages, and only report back Y items:


rs.CursorLocation = 3 'adUseClient
rs.CursorType = 3 'adOpenStatic
rs.CacheSize = 15 'We want CacheSize and PageSize to match for best efficency
rs.PageSize = 15
rs.Open query, sConn
rs.AbsolutePage = startPage 'The Page number where you want to start


You then read 15 records or until you reach EOF (if you read past 15 records, you'll be outside of the cache, and a request will go out to retrieve the next 15 records). AbsolutePage starts at 1 (so page 1 = 1). You can also read the rs.PageCount value to determine how many pages there are. For example if you want to go to the last page, you can simply do the above, and set AbsolutePage=PageCount.

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!