Subsonic 2.2- SQLite Data Proider, data pagging error

I have this error when try to implement pagging using sqlite in subsonic. error :near "DECLARE": syntax error ---> SubSonic.SqlQueryException: SQLite error

here is my code

SqlQuery q = new Select( new SubSonic.TableSchema.TableColumn[]

    {
       UserProfile.UseridColumn,

        UserProfile.UsernameColumn,
        UserProfile.DepartmentColumn,
        UserProfile.UserleverColumn,
        UserProfile.ExpiryColumn,
        UserProfile.StatusColumn,
        UserProfile.SystemidColumn
      }
    ).From(UserProfile.Schema)                           
    .Paged(pageNumber, PAGE_SIZE);

    Console.WriteLine(q.BuildSqlStatement());   
    q.ExecuteDataSet();

here is the script generate by Sqlquery.BuildSqlStatement()

DECLARE @Page int DECLARE @PageSize int

SET @Page = 1 SET @PageSize = 50

SET NOCOUNT ON

-- create a temp table to hold order ids

DECLARE @TempTable TABLE (IndexId int identity, _keyID VarChar(25))

-- insert the table ids and row numbersinto the memory table

INSERT INTO @TempTable ( _keyID ) SELECT dbo.UserProfile.USERID FROM dbo.UserProfile

-- select only those rows belonging to the proper page

SELECT dbo.UserProfile.USERID, dbo.UserProfile.USERNAME, dbo.UserProfile.DEPARTMENT, dbo.UserProfile.USERLEVER, dbo.UserProfile.EXPIRY, dbo.UserProfile.STATUS,dbo.UserProfile.SYSTEMID FROM dbo.UserProfile INNER JOIN @TempTable t ON dbo.UserProfile.USERID = t._keyID WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)

Any work around or update ? Please help. Thansk

ansaurus

tags:

views:

answers:

Subsonic 2.2- SQLite Data Proider, data pagging error

related questions