If you love 'acrobatic' SQL this is a simulation of paging with an Access MyTable table with an
Id field as primary key:
SELECT MyTable.*
FROM MyTable
WHERE MyTable.Id In
(
SELECT TOP 10 A.Id
FROM [
SELECT TOP 30 MyTable.Id
FROM MyTable
ORDER BY MyTable.Id
]. AS A
ORDER BY A.Id DESC
)
ORDER BY MyTable.Id
The SQL statement queries the top 30 records, reverses the order, queries the top 10, and then selects the rows from the table that match, sorting in forward order again.
So it extracts the records from 21 to 30 from the table.
A problem is that the TOP clause doesn't accept parameters, so you must build the statement inserting the offset value (replacing '30') concatenating strings (pay attention to SQL Injection).
Rorah
Member
68 Points
33 Posts
Webmatrix, Access Database and SQL syntax
Feb 13, 2012 12:09 PM|LINK
Hi,
I've a classic asp site with an Access Database and i migrate this to cs webmatrix.
Actually, it's impossible to change my access database to mySQL or other SQL server for a lot of reasons.
i want make a pagination without webgrid helper like http://www.mikesdotnetting.com/Article/150/Web-Pages-Efficient-Paging-Without-The-WebGrid but FETCH function doesn't work with Access like LIMIT.
Anyone have solution ?
Thanks.
Mikesdotnett...
All-Star
154927 Points
19867 Posts
Moderator
MVP
Re: Webmatrix, Access Database and SQL syntax
Feb 13, 2012 01:01 PM|LINK
Access doesn't support any kind of record paging internally.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
GmGregori
Contributor
5470 Points
737 Posts
Re: Webmatrix, Access Database and SQL syntax
Feb 14, 2012 07:32 AM|LINK
If you love 'acrobatic' SQL this is a simulation of paging with an Access MyTable table with an Id field as primary key:
SELECT MyTable.* FROM MyTable WHERE MyTable.Id In ( SELECT TOP 10 A.Id FROM [ SELECT TOP 30 MyTable.Id FROM MyTable ORDER BY MyTable.Id ]. AS A ORDER BY A.Id DESC ) ORDER BY MyTable.IdThe SQL statement queries the top 30 records, reverses the order, queries the top 10, and then selects the rows from the table that match, sorting in forward order again.
So it extracts the records from 21 to 30 from the table.
It's not mine, and you can see more details at http://stackoverflow.com/questions/8627032/ms-access-limit-x-y.
A problem is that the TOP clause doesn't accept parameters, so you must build the statement inserting the offset value (replacing '30') concatenating strings (pay attention to SQL Injection).
Rorah
Member
68 Points
33 Posts
Re: Webmatrix, Access Database and SQL syntax
Feb 14, 2012 11:23 AM|LINK
i found this code and i try it.
it works great but the performances are bad.
Thanks for your help, i keep my old code for the moment.