Next/Previous Item

Posted on 2008-11-14
Last Modified: 2012-05-05
I am having trouble with getting the prev/next item in my list.  I have two 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
Question by:Trinig
    LVL 5

    Accepted Solution

    Could you change the code so the list order is based on "9999" + itemnumber?  If so, then you would have

    An example of some data:
    ItemNumber      ListOrder
    104                    1
    103                    2
    106                    3
    101                    9999101
    105                    9999105

    so the last ones will be ordered in the order they came in falling at the tail end of the list.



    Author Comment

    Wow.. I feel really stupid - I think that should work.  I'm going to implement it now and test it.

    Author Closing Comment

    Nice and simple, Works great.  I was really over complicating things trying to come up with a solution.  Thanks a lot!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now