Problems using LIMIT OFFSET with ASP/mySQL

[Woz]

I am converting my ASP/ACCESS stuff to ASP/MYSQL and experienceing a few problems with the way ASP handles data and interfaces with the database. Most are small niggle things that have work-arounds, or even slight changes in thought processes, but I have hit a stumbling block with the LIMIT function in mySQL.

"SELECT * from sometable LIMIT 20;" works perfectly, returning only 20 results through the ASP script.

"SELECT * from sometable LIMIT 20, 20;" should return results 21 through 40, but ASP only picks up 21 through 30.

"SELECT * from sometable LIMIT 20, 40;" should return results 41 through 60, but ASP reports EOF.

From researching online this seems to be a common problem for ASP type people using mySQL, but as yet I cannot find any reports of solving the problem.

For the record, converting to PHP is not an option at this stage, or perhaps ever, so I need to find an ASP solution to this problem. Anyone got any ideas?

Onya
Woz

[arran]

Hi Woz,

Are you executing raw SQL or using a stored procedure?

arran.

[Woz]

Raw SQL arran, building the string on the fly for each iteration of the page.

Onya
Woz

[Demaestro]

Try this, Some SQL syntax I have seen requires the use of the word 'offset'

SELECT
*
from
sometable
LIMIT
20
offset
20

This should give you 20 - 40

Let me know if it works if not I might have something else to try.

[coopster]

It looks as if you may only have <= 30 rows in your file. Is that perhaps the issue?

[Woz]

Hehe, nice try Coop, but I'm not that bad. ;)

Demaestro, I am playing with that and it does show some promise. It seems this is an ASP quirk with perhaps no easy workaround.

Onya
Woz

[coopster]

hehe, I had to ask you know ;) How many rows are there?
... he said, still fishing ;-)

[Woz]

In my small test query - 62, in my large test query - 25,256. :)

Onya
Woz

[coopster]

All right, accepted.

I doubt this has anything to do with it, but when running queries through the MySQL API's you should NOT use terminating semicolons.

SELECT * from sometable LIMIT 20, 20;

I highly doubt this is the issue, but beyond that I can offer nothing else.

[Demaestro]

What type of DB connection are you running this through? ODBC?

[Woz]

I have just been reminded that I have not replied to this thread. Sorry all.

I found the problem, it was located midway between Chair and Keyboard. ;)

Remember I said I was converting ASP/Access to ASP/mySQL? I ended up paring the scripting back to the bone and building it up again, and found a legacy line of code that, whilst good for Access, was Baaaad for mySQL.

Problem solved.

Onya
Woz