I suggest you ...

pagination bug mssql

We have a view:

`CREATE VIEW StockInformation AS SELECT dbo.Product.Code AS ItemCode, dbo.Warehouse.Code AS WarehouseCode, dbo.StockIndicator.Code AS StockIndicatorCode, dbo.Stock.StockOnShelve, dbo.Stock.StockAvailable,
dbo.Stock.StockPurchaseBackorder, dbo.Stock.StockFirstPurchaseDeliveryDate
FROM dbo.Stock INNER JOIN
dbo.Warehouse ON dbo.Stock.WarehouseID = dbo.Warehouse.WarehouseID INNER JOIN
dbo.StockIndicator ON dbo.Stock.StockIndicatorID = dbo.StockIndicator.StockIndicatorID RIGHT OUTER JOIN
dbo.Product ON dbo.Stock.ProductID = dbo.Product.ProductID`

and the following query:

var stocks = con.Select<StockInformation>(con.From<StockInformation>().OrderBy(x=> x.ItemCode).Limit(pageSize * pageNumber, pageSize));

The first page results into query:

SELECT TOP [pageSize] ... FROM StockInformation.

The second query looks like this:

SELECT * FROM (SELECT "ItemCode", "WarehouseCode", "StockIndicatorCode", "StockOnShelve", "StockAvailable", "StockPurchaseBackorder", "StockFirstPurchaseDeliveryDate", ROW_NUMBER() OVER (ORDER BY "StockInformation"."ItemCode") As RowNum
FROM "StockInformation") AS RowConstrainedResult WHERE RowNum > 1000 AND RowNum <= 2000

The problem is that actually the view isn't ordered by itemcode (since views can't be ordered). So some items go missing and some are displayed twice.

0 votes
Vote
Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
You have left! (?) (thinking…)
Anonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base