Webmatrix, Access Database and SQL syntax

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.

Re: Webmatrix, Access Database and SQL syntax

Access doesn't support any kind of record paging internally.

Re: Webmatrix, Access Database and SQL syntax

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.

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).

Re: Webmatrix, Access Database and SQL syntax

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.