save list order in database?

Question

I have a list with values from the db, and with jquery ui I can change the sorting order of the list witch is great!

But now I wonder how I can save the changed order to the db? With a do until, but how?

This is what I got so far.

sql = "SELECT * FROM links ORDER BY u.avd_id DESC;"
set rs = conn.Execute (sql)


if saveList="saveList" then

userIdet = session("user_id")
sql3="delete from links where userId="& userIdet &";"
set rs3=conn.execute(sql3)

response.write " $('li','#myList').each(function(i,li) { "


sql= "INSERT INTO links SET headline='"& headline &"',datum='"& datum &"',links_adress='"& links_adress &"', userId=" & userId & " ;"
conn.Execute (sql)


response.write" })"

response.redirect("links.asp")

end if

I would really need some help, thanks! /Claes

Answer 1

If the user is able to choose and save how the list is sorted, then you should have a displayOrder column in your table. When the user saves the changes, just do an update on the displayOrder column for each item in your list. Then your select statement will ORDER BY displayOrder, rather than by u.avd_id.

Or if you want to delete and re-add like you are doing, you need something like this:

response.write " $('li','#myList').each(function(i,li) { "

int i = 1;

sql= "INSERT INTO links SET headline='"& headline &"',datum='"& datum &"',links_adress='"& links_adress &"', userId=" & userId & ", avd_id=" & i & " ;"

i++;

conn.Execute (sql)

response.write" })"

Answer 2

I would first ask myself if there is a better JavaScript library that better ties HTML/UI to the backend database to make my life easier.

Answer 3

Now I have 2 problems: I have a list with 3 headlines with values: aaa, bbb, ccc

1. How do I write the "Do until loop" so it only runs as many times as the list is long?
2. Im trying to write the "i" after each headline, but now it writes aaabbbccc1, aaabbbccc2 and I want it to be aaa1, bbb2, ccc3 and so on.

if(request.form("submit") <> "") then

int i=1
do until i = 5

if(request.form("headline") <> "") then

response.write request.form("headline")&i&"<br>"

end if
i = i + 1

loop 

end if

Thanks for any help!