Advertisement Jump to content
Sign in to follow this  


This topic is 4981 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
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, you agree to our community Guidelines, Terms of Use, and Privacy Policy. is your game development community. Create an account for your GameDev Portfolio and participate in the largest developer community in the games industry.

Sign me up!