Basically, ASP.NET does not have move next or move previous function, therefore you need to manually use a for loop to determine the position of the target index and then use position + 1 or position -1 to get the next or previous record.
If you are developing a windows form then you are lucky you can use
BindingContext[dataTable].Position + 1 or BindingContext[dataTable].Position - 1.
Well, we are talking about ASP.NET here, if we are populating a few hundreds items and use for loop to get the position and I think it is fine. Or u might be using datatable something like
DataTable dt = new DataTable();
dt.Rows.IndexOf(datarow);
to get the index and from there u manipulate it.
However what if we have 1 million of records in the table? That would be crazy!
The more efficient way is to use
over keyword which only available in SQL Server 2005 or later. The idea is to get the previous record and the next record given the record identity. I just have to get 2 records and I just want the first record as previous and second record as next. So here is the trick.
subcategoryid, name
31 | Game Downloads |
|
|
|
32 | Computers & Accessories |
|
|
|
33 | Software |
|
|
|
34 | PC Games |
|
|
|
35 | Computer Components |
|
|
|
36 | Office Products & Supplies |
|
|
|
select top(1) row_number() over (order by subcategoryid desc) row, subcategoryid, name from subcategory where subcategoryid<33
union
select top(1) row_number() over (order by subcategoryid) row, subcategoryid, name from subcategory where subcategoryid>33
results:
1 32 Computers & Accessories
1 34 PC Games
p/s please note that this solution only applicable for int column having auto numbering enabled only. the subcategoryid is the primary key, auto number and numaric