Change order of items

[bateman_ap]

Hi, I am writing a widgets menu in ASP/MSSQL and wondered if anyone had any idea for the following? What it is I am displaying a menu of widgets, so at the mo they might be

Widgets:
1. Blue Widgets
2. Red Widgets
3. Pink Widgets
4. Black Widgets

Wiggles:
1. Red Wiggles
2. Pink Wiggles
3. Blue Wiggles

This are all generated from a database and at the moment sorted by their UID. Now a widget supplier might want to change the order so Pink Widgets becomes first in the list. I have toyed with a few ways of doing this but they seem very cumbersome and wondered if here was a simpl way I am missing.

I was thinking I could have a field "fld_sort" that is used to sort the SQL query. thenin the admin section if pink widgets (currently 3 in the sort field) was moved to the top it would change its sort number to 1 and then everything between its old position and its new one increases by 1.

It should work but as I said before will put quite a load on the server unless I am missing something?

[mattglet]

The best way to do it is to add a sort field like you mentioned. Then when you do your query, add "ORDER BY yoursortfield" to do the ordering.

It shouldn't put a load on the server at all if you are coding it correctly.

[bateman_ap]

Thats what I am doing, it is more in the admin section when someone changes the sort order if there was a better way to reorder then than performing 60 odd UPDATE statements

[john_k]

You either have to put some time into the code that lets you set the sort order, or you have to put some time into manually shuffling the other items each time you insert an item in the middle of the sort order.

The ideal way is to start with the premise that two items can't have the same sort number. Then code your business logic or stored procedure to handle the insertion automatically. An easy way is to update all items where the sort order is greater than or equal to the one you are inserting. When you remove something from the sort, then decrement everything that is greater.

[aspdaddy]

I had this exact same problem with dropdown lists, the solution I did was to pass the uid of the color to the query and retrieve that one first.

CREATE PROCEDURE [dbo].[getWidgets]
@WidgetID smallint
AS
SELECT WidgetID, Color FROM tblWidgets ORDER BY
CASE WHEN WidgetID <> @WidgetID Then 1 END,Color
GO

Heres the code that uses it to make the dropdown list

function GetRefData ( strSPName, intSelected )
dim objConn,objRS, strReturn
set objConn=Server.CreateObject("ADODB.Connection")
openDB( objConn )
set objRS=objConn.Execute ("Exec dbo." & strSPName & " " & intSelected)
while not objRS.EOF
strReturn = strReturn & vbCrLf
strReturn = strReturn &"<option value='"&objRS(0)&"'>"&objRS(1)&"</option>" 
objRS.MoveNext
wend
GetRefData=strReturn
closeRS( objRS )
closeDB( objConn )
end function

To call it:

<select name="Widgets"><%= GetWidgets objReseller.FirstWidget )%></select>

[bateman_ap]

Many thanks, will give it a go