updating multiple recordsets with new display order

[sdagger2]

Hi

I have successfully queried my access database using asp and displayed the data in a css list (ordered by a numeric field called displayorder). But I am having trouble doing the update back to the database having changed the display order.

Too keep simple I have an access database with 3 fields, primary key ID, name (text) and displayorder (numeric). Lets say the data in the table is:

ID NAME DISPLAYORDER
100 Adam 1
101 Barry 2
102 Charley 3

I have a css list which allows me to drag and drop the list to change the order. As you drag the list up or down, I populate an array with the new order. If I drag Charley to the top and Adam to the bottom to look like:

103 Charley 3
101 Barry 2
100 Adam 1

The array variable to pass for the update will be:
newArray(3,2,1) so I guess I will now need to make Charley 1, Barry 2 and Adam 3.

This array is passed as a hidden input on form submit where I want to use the newArray(3,2,1) in the update statement to replace the display order field in the database.

I am not sure how to code the syntax to update multiple records in the database so that displayorder field is changed from 1,2,3 so that the new 3,2,1 becomes the 1,2,3? I know I must do either comparisons or loop in someway but i'm stuck.

I'm pretty sure this is probably very easy but i'm not seeing the wood for the trees.

My coding skills are not the best so any examples would be fantastic.

Many thanks for taking the time to read my problem and for any help you can offer.

Sean.

 

[sdagger2]

Hi

That still doesn't work. I'm feeling I want to give up but I really want to sort it now.

could you possibly copy the above code and create a database based on the screenshot above? The css list uses a script called scriptaculous.js which is a free small js file. the files needed are:

<script src="scriptaculous/lib/prototype.js" type="text/javascript"></script>
<script src="scriptaculous/src/scriptaculous.js" type="text/javascript"></script>

Free to obtain if you want to get? or I could send you link.

Really sorry about all this, you have been a fantastic help so far.

Sean.

 

[sdagger2]

Hi again.

I've cracked it!!!! thank god.

I noticed that the ID array when submitted was out of order so a new order of say 2,1,3,4,5,6,7 (first two swapped) should really also pass the ID of 15,14,16,17,18,19,20 but the id's always remained 14,15...20.

To overcome this, after submitting I requeried the database pulling out the data in the order of the new order 2,1,3..7 and whilst doing so, populating a newIDarray of the ID's which got them in the same order. Then I did the update of the code you supplied and bingo. All works brilliantly now.

All I can say is thank you so much for your help. Without which I would have given up. Thank you again for your help and hard work in helping me.

Sean.

 

[Remou]

I am glad you figured it out for yourself, and I hope you are giving yourself a hearty pat on the back

Never give up.

http://lessthandot.com

 

[sdagger2]

Yes I have just opened a nice cold beer to celebrate. Now for the next task. I'm better at photoshop and xhtml than serverside but i'm learning. Its the new css drag n drop that snagged me on this one.

Thanks again.