Trinig
asked on
Next/Previous Item
I am having trouble with getting the prev/next item in my list. I have two ASP.net pages, one is a list page which lists out the items, the other is a view page which shows more details, etc.
The list page gets it data from the following sql (simplified version)
select ItemNumber, ISNULL(ListOrder, 9999) from itemList order by ListOrder, ItemNumber
Users have the ability to rearrange the order items are listed which is stored in the ListOrder column. If an item has not been ordered, or just added, its ListOrder is NULL which is set by the query to 9999 so it falls to the bottom, and then I order by ItemNumber as well to maintain a consistent display order for items with the 9999 ListOrder.
On the view page, I need a First, Prev, Next, Last button that will cycle through the list in the correct order. The First and Last are easy; just used a select top 1 on the list for first, and a select top 1 on the list ordered DESC for last. The problem comes with Prev/Next - It would be simple if all items had a ListOrder; its simply a select top 1 with a where clause ListOrder<Current or ListOrder>Current respectively. However, not all items have a ListOrder value, which is where the problem lies. I can't do a ListOrder < Current anymore b/c if the ListOrder is set at 9999 it could be the first 9999, middle or last.. and ListOrder < Current will automatically use it as the first. I'm lost as to how to approach this. And I can't just go through and assure all items have a ListOrder, its not an option at this point.
An example of some data:
ItemNumber ListOrder
104 1
103 2
106 3
101 9999
105 9999
The problem area: Current is one of the 9999 and need Prev
The list page gets it data from the following sql (simplified version)
select ItemNumber, ISNULL(ListOrder, 9999) from itemList order by ListOrder, ItemNumber
Users have the ability to rearrange the order items are listed which is stored in the ListOrder column. If an item has not been ordered, or just added, its ListOrder is NULL which is set by the query to 9999 so it falls to the bottom, and then I order by ItemNumber as well to maintain a consistent display order for items with the 9999 ListOrder.
On the view page, I need a First, Prev, Next, Last button that will cycle through the list in the correct order. The First and Last are easy; just used a select top 1 on the list for first, and a select top 1 on the list ordered DESC for last. The problem comes with Prev/Next - It would be simple if all items had a ListOrder; its simply a select top 1 with a where clause ListOrder<Current or ListOrder>Current respectively. However, not all items have a ListOrder value, which is where the problem lies. I can't do a ListOrder < Current anymore b/c if the ListOrder is set at 9999 it could be the first 9999, middle or last.. and ListOrder < Current will automatically use it as the first. I'm lost as to how to approach this. And I can't just go through and assure all items have a ListOrder, its not an option at this point.
An example of some data:
ItemNumber ListOrder
104 1
103 2
106 3
101 9999
105 9999
The problem area: Current is one of the 9999 and need Prev
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nice and simple, Works great. I was really over complicating things trying to come up with a solution. Thanks a lot!
ASKER