priority editing (c# + sql)

whats the reccomended method for editing the priority of data. for example a movie rental site. a user adds 5 movies, and wishes to reorder the order in which they will be sent.

so far i have only managed to make it work one way, and thats changing the priorty to soemthing higher, but lower makes it skip a row.

if you would please just give me a clear enough direction to work on, would be greatly appreciated,

stevan

Dear Stevan,

I could be just daft, but you are clear as mud to me. Could you explain your requirement a little better.

ok, i apologise for being unclear.

basically. Say you are developing a dvd rental website. you make it so that your users add a movie to be dispatched to them, in the order that they click the movies to be added. so for example, a user views the movie listing and chooses to add 3 movies to their list of rented movies.. the added movies will be added in order starting from the first clicked being the highest priority (1) to the last being clicked having the lowest priority (3).

Now, say a user wishes to change the priority, so for example the last added movie which has third priority a user wishes to higher its priority to first, or second. Likewise a user may wish to lower a priority of a movie.

I have had some luck in achieving this, however i have only managed to get as far as changing the priority successfuly to something higher, not lower though.

I hope this time i am a little clearer.

Thank you,

stevan

could you send a code sample of how you managed to upgrade the priority of an item?

we'll take that as a starting point

i unfortunately don't have the code here, it is at work and i am at home.

I can't remember enough of the code to actually explain in detail what it does, overall i think there may be a better way of doing it, at the moment my colleague made it so it incremented the priority +1 to move up when raising the priority, but when doing the opposite (-1) it skipped a row.

How would you go about putting in a priority organisation function? we are using SQL btw.