Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Next/Previous Item

Posted on 2008-11-14
Medium Priority
Last Modified: 2012-05-05
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
Question by:Trinig
  • 2

Accepted Solution

scgstuff earned 2000 total points
ID: 22961997
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

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

Author Closing Comment

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

580 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