asked on

Changing order of records in ASP (Dynamically?)

I've got this page, http://www.arrivalpreview.co.uk/testing/temp.asp, which allows me to change the order of records in a database, you can move them up or down and then the order is calculated in the database. It works well, but the list of records is likely to get pretty big, so if the order needed changing at the bottom up a few places, it could take a while for the page to load, then move down to the bottom of the page and move the record up again. I was wondering if there would be any dynamic way of doing this so the page didn't need to keep re-loading, or some sort of ajax script to drag a record to whatever position you wanted it?

Please feel free to experiment on my preview page.

My current code is as follows:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
if request.Form("update") = "yes" then
set adoCn = Server.CreateObject("ADODB.Connection")
adoCn.Open MM_cmroach_STRING
arrRecs = split(request.Form("recordlist"),",")
for i = lbound(arrRecs) to ubound(arrRecs)
SQL = "Update products set prodOrder = " & request.Form("product" & arrRecs(i)) & " where prodID = " & arrRecs(i) & ""
set RS = adoCn.execute(SQL)
'response.write SQL & "<br>"
next
SQL = ""
adoCn.Close
set adoCn = Nothing
end if

Set RS = Server.CreateObject("ADODB.Recordset")
RS.ActiveConnection = MM_cmroach_STRING
RS.Source = "SELECT * FROM products WHERE prodCategory = 47 ORDER BY prodOrder ASC"
RS.Open()
arrProds = RS.getrows()
RS.Close()
Set RS = Nothing
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Crown Office Chambers</title>

<link href="../style.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
function upOne(i,j)
{
el = document.form1.elements['product'+i]
el.value = el.value-1
el = document.form1.elements['product'+j]
el.value = el.value-1+2
//alert(el);
document.form1.submit()
}
function downOne(i,j)
{
el = document.form1.elements['product'+i]
el.value = el.value-1+2
el = document.form1.elements['product'+j]
el.value = el.value-1
//alert(el);
document.form1.submit()
}
</script>

</head>

<body id="maintenance">
<form name="form1" method="post" action="">
<div id="holder">
<div id="header"><img src="../images/logo.gif" width="274" height="20" /></div>
<div id="content">
<div id="left">
<h1>Amend/Delete Member </h1>
<table width="558" border="0" cellspacing="0" cellpadding="2">
<tr>
<td style="border-bottom:1px solid gainsboro;"> </td>
<td style="border-bottom:1px solid gainsboro;"> </td>
<td style="border-bottom:1px solid gainsboro;" width="322"><b>Member Name </b></td>
</tr>
<%
for i = lbound(arrProds,2) to ubound(arrProds,2)
reclist = reclist & arrProds(0,i) & ","
%>
<tr>
<td style="border-bottom:1px solid gainsboro;">
<% if i <> lbound(arrProds,2) then %>
<img style="cursor:pointer; " src="moveup.gif" onclick="upOne('<%=arrProds(0,i)%>','<%=arrProds(0,i-1)%>');" alt="move up"> 
<% else %>
<img src="spacer.gif"> 
<% end if %> </td>
<td style="border-bottom:1px solid gainsboro;">
<% if i <> ubound(arrProds,2) then %>
<img style="cursor:pointer; " src="movedown.gif" onclick="downOne('<%=arrProds(0,i)%>','<%=arrProds(0,i+1)%>');" alt="move down"> 
<% else %>
<img src="spacer.gif"> 
<% end if %> </td>
<td style="border-bottom:1px solid gainsboro;"><%=arrProds(1,i)%><input type="hidden" id="member<%=arrProds(0,i)%>" name="product<%=arrProds(0,i)%>" value="<%=i%>"></td>
</tr>
<%next%>
</table>
<p><b>«</b> <a href="access.html">Back to main menu</a></p>
</div>
</div>
</div>
<input type="hidden" id="recordlist" name="recordlist" value="<%=left(reclist,len(reclist)-1)%>">
<input type="hidden" id="update" name="update" value="yes">
</form>
</body>
</html>

Many thanks

Chris

ThinkPaper

Yeah I see what you mean.. it doesn't make sense to reload and pull from DB when all you're doing is resorting the data. Perhaps this can help? Looks like they already did the code for ya.
http://www.ajaximpact.com/ajaximpact.php?n=10&id=85&back=/detail_Tutorials.php?id=85

djfenom

ASKER

But that only sorts the order of the table column one way or another, I need to be able to move a record say at the bottom to the middle, or move the top record down into second spot.

swinslow

You definitley will need a client side JavaScript to be able to move the table rows around. I found this one that looks like it should be fairly easy to modify fro what you are trying to do. http://www.tek-tips.com/faqs.cfm?fid=5190

ASKER CERTIFIED SOLUTION

djfenom

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ThinkPaper

glad you got it worked out =)

Vee_Mod

Closed, 500 points refunded.
Vee_Mod
Community Support Moderator