Server Error due to paging issue

Question

Hello all, I think I sorted out my GridView1_PageIndexChanged event and thinking it should work

 protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.DataSourceID = "lqPackWeights";
    GridView1.PageIndex = e.NewPageIndex;
}

However, when I now attempt to access page 2 of x, I receive the following:

Server Error in '/project' application. This provider supports Skip() only over ordered queries returning entities or projections that contain all identity columns, where the query is a single-table (non-join) query, or is a Distinct, Except, Intersect, or Union (not Concat) operation.

I'm a bit confused by this, I'm not using skip as far as I can see unless I am going blind?

I am currently using SQL2000, is this a problem directly related to this instance of SQL?

Answer 1

Are you using LINQ to SQL? The problem seems unique to SQL Server 2000. See these MSDN articles on Troubleshooting (LINQ to SQL) and Standard Query Operator Translation (LINQ to SQL) for more information.

EDIT

A similiar question has been asked before, see: LINQ, Skip, OrderBy, and SQL Server 2000

Answer 2

The control is using Skip to get to the records for the specific page that it is supposed to display.

In SQL Server there is no default ordering for queries (unless it's a direct table with a clustered index), so you have to specify an order in the query for the data source. The result of the query has to have a specific ordering; it doesn't make sense to page through a result if the ordering changes from page to page so that you get more or less a random pick of records from the result for each page.

Answer 3

Hello Guffa.

Currently my linq query is ordered...I guess that is not the way forward or am I misunderstanding you?

private object GetMaterialData(string MemberKey, string MaterialType, string MaterialLevel, int Count) { ORWeightsDataClassesDataContext db = newORWeightsDataClassesDataContext(); var query = db.tblOnlineReportingCOMPLETEWeights .Where(x => x.MaterialLevel == MaterialLevel && x.MaterialText == MaterialType && x.MemberId == MemberKey) .OrderByDescending(x => x.ProductPercentage).Take(Count); return query; }