Subsonic-Paging-Order problem

Question

I have a datagrid where I am using the custom paging option (ref: http://subsonicproject.com/querying/webcast-using-paging/) in the Subsonic framework.

I also have a dropdown that filters the data by State. This is added to the query through the addwhere call.

the data is ordered by state ASC and then city ASC.

the data seems to be ordered fine when no state is selected and thus no addwhere is added to the clause. But if you select a state that has enough records to cause pagination to kick in, then some records are displayed out of order. I have also noticed that it always seems to be the last few records on the current page are displayed somewhere in the middle of the grid.

snippet of code to loadgrid:

    Dim qry As New SubSonic.Query( {myTableSchema} )
    If ddlStates.SelectedValue.Trim.ToLower <> "all states" Then
        qry.AddWhere("state", ddlStates.SelectedValue.Trim)
    End If
    qry.ORDER_BY("state", "ASC").ORDER_BY("city", "ASC")
    qry.PageSize = ddlDisplay.SelectedValue
    qry.PageIndex = pageNumber
    gvOrganizers.DataSource = qry.ExecuteDataSet
    gvOrganizers.DataBind()

The problem doesn't seem to appear when a state is selected and there is only 1 page of data. Default ddlDisplay setting is 100 records per page but the error appears even if 50 or 25 is chosen.

Using Subsonic 2.1.0.0

Answer 1

Use qry.OrderAsc(New String(){"state asc, city asc"})

Answer 2

I think I found an answer to your question:

There seems to be a bug in PAGING_VIEW_SQL template (SqlProvider.cs, line 1702). Here is the snippet:

" ... SELECT _indexID = IDENTITY(int, 1, 1), {1} INTO #temp FROM {0} WHERE 1 = 0 INSERT INTO #temp ({1}) SELECT {1} FROM {0} {2} {3}

SELECT * FROM #temp WHERE _indexID BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)

--clean up

DROP TABLE #temp", where {3} represents serialized order-by-collection.

You can see that at first ordered data is sorted and inserted into a temp table but than the required page is retrieved without sorting again. Which in the end results in getting the right data for the requested page but without proper sorting.

The second select needs to be modified so that when the data is taken from the temp table, it's in the correct order. Like this:

"SELECT * FROM #temp

WHERE _indexID BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize) {3}"

and then of course recompile the code and off you go :-)