How to get around limitations of data source paging?

[Utpal]

Hi everybody,

Currently, I am exploring the new Data controls in ASP.NET 2.0.

I successfully tried out the feature of data source paging on the Object data source control and Details View control, with the help of an illustration in a book.

However, there is a big limitation to using this feature using SQL Server stored procedures. It is assumed that the stored procedure assigned to the "SelectMethod" property of the Object data source control has 2 numeric parameters - @StartRowIndex & @MaximumRows. Using the @StartRowIndex parameter to refer to the current row fails in 2 cases:

1) If a row is added to the table by another user on which the object data source is based, whose primary key value is lower than the primary key value of the current row. In such a case, clicking the "Next" button would not display the next code. It would be worse if multiple new rows were added that have lower primary key values than that of the current row, in which case, clicking on the "Next" button could even navigate backwards.
E.g. I have a "Commodity" table in which there are 2 commodity codes: "ABRALOTH" and "ACCEDUST". While the Details view control was displaying the commodity code "ACCEDUST", I added a new commodity "ABRRRRRR" through the Enterprise manager. On clicking the "Next" button (@MaximumRows = 1), the commodity code remained "ACCEDUST" only. This happened because before adding the new row, the @StartIndex of "ACCEDUST" was say 1000 and after the commodity code "ABRRRRRR" was added, it became 1001. On clicking the "Next" button, the @StartIndex was advanced to 1001, which happened to be the new number for "ACCEDUST".

2) If a row is deleted from the table by another user on which the object data source is based, whose primary key value is lower than the primary key value of the current row. In such a case, clicking the "Prev" button would not display the previous code. It would be worse if multiple rows were deleted that have lower primary key values than that of the current row, in which case, clicking on the "Prev" button could even navigate forward.

What is the best way to overcome these limitations?

Following is the stored procedure that I have used:

CREATE PROCEDURE dbo.GetPagedCommodities(@StartRowIndex INT, @MaximumRows INT)
AS
BEGIN

CREATE TABLE #PageIndex
(
IndexId INT IDENTITY (1, 1) NOT NULL,
commodity_code varchar(8)
)

INSERT INTO #PageIndex (commodity_code)
SELECT commodity_code FROM Commodity

SELECT Commodity.commodity_code, [description]
FROM Commodity INNER JOIN #PageIndex
ON Commodity.commodity_code = #PageIndex.commodity_code
WHERE IndexId > @StartRowIndex
AND IndexId < (@StartRowIndex + @MaximumRows + 1)

END

[mendhak]

I was typing a big reply when I accidentally closed the window, and now I can't be bothered to type it up again. So instead,

Have a look here: http://msdn.microsoft.com/msdnmag/is...09/DataPoints/

And also, keep in mind, the DataSource objects are not meant for... 'complex' scenarios. They're for simpler projects.