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.

 

[Remou]

I think you would be better storing the ID in the array order, so you would then have something on the lines of:

astrArray=Split(newArray,",")
For i=0 To UBound(astrArray)
    strSQL="Update tblTable Set DisplayOrder=" & i+1 _
         & " Where ID=" & astrArray(i)
    cn.execute strSQL
Next

http://lessthandot.com

 

[sdagger2]

Hi

Thats a brilliant step forward and gets me sooo close to right result. I'm not sure if I need a small addition to the code to get it to work.

The code above works great but as it goes through the loop (say 10 records), the condition maybe true twice within the 10 updates therefore you may have two records with a display order of 1.

Is there a way of adding a second array to the loop so the WHERE clause could also check for the actor perhaps? or is there an easier way?

Many thanks again for your help..you are helping big time!

Sean.

 

[Remou]

Can you run that by me again? What condition may be true twice? You mean the Unique ID may occur twice in the array? If so, this seems a little odd. Which actor, and check where?

http://lessthandot.com

 

[sdagger2]

Hi

I have made the database order 1 - 7 (7 items). But you'll see that when you change the orders (drag the cells) that it will mess up. looking at the attached image, you can see my screen before making the change, then after the change (moving item 1 to position 6) then the output after you click on the update.

I have noticed that the loops looks good but when when re-queried again after the update (see bottom) it shows the displayorder with 6 twice?

I'm so stuck.

 

[Remou]

You seem to have
Update tbdMovieCast Set DisplayOrder=1
Where DisplayOrder=2

This cannot work, because you follow it up with:

Update tbdMovieCast Set DisplayOrder=6
Where DisplayOrder=1

You must use the ID, or something other than display order. If you look at my suggestion again, you will see that the array expected is an ID list, for example:

105,101,102

As the code runs, you get:

[tt]Update tbdMovieCast Set DisplayOrder= 1 ' i + 1
Where ID=105 ' astrArray(i)[/tt]

[tt]Update tbdMovieCast Set DisplayOrder= 2 ' i + 1
Where ID=101 ' astrArray(i)[/tt]

[tt]Update tbdMovieCast Set DisplayOrder= 3 ' i + 1
Where ID=102 ' astrArray(i)[/tt]

Is that clearer?

http://lessthandot.com

 

[sdagger2]

Ah I see what you mean but I think i've messed up a little bit. When I change the order of the list, the new array created can only be the numbers of how many in the list. i,e. in the example, it would be 3,2,1 that is passed not 102,101,100.

Because in your example code, you compare with the ID which could not be true because only 3,2,1 would be passed through.

In your most recent example. you say the values would be 103,101,102 that is passed. I wish it was but it would actually be 3,2,1

Anyway I can do it that way?

So sorry to mess you about with this.

 

[Remou]

I notice you have a name array, is that passed, and is the order correct?

AFAIK, the only way you can update the display order when all you have is a list of display order is with some sort of complicated fudge.

http://lessthandot.com

 

[sdagger2]

It is passed as a hidden field yes but I'm not using it yet because I don't know how to add it to the loop with having two loops which just breaks my brain. I'm just can't see the wood for the trees.

The name array I pass through is:

newArray = split(request("newnamearray"), ",")

 

[Remou]

Ok, so try this:

astrArray=split(request("newnamearray"), ",")

For i=0 To UBound(astrArray)
    strSQL="Update tblTable Set DisplayOrder=" & i+1 _
         & " Where Actor='" & Trim(Replace(astrArray(i),"'","''") & "'"
    cn.execute strSQL
Next

Replacing ' with '' means that O'Leary will not cause a problem. Trim may not be necessary.

http://lessthandot.com

 

[sdagger2]

Still not working and my head is now bashed in. Think I need to get to bed. Any chance I could send you the mdb and code to your email?

Your help has been fantastic and really appreciate it. must buy you lots of beers.

Sena.

 

[Remou]

Emails attract spammers, and so are frowned upon. Someone recently used

http://www.savefile.com/

to upload a file, would that suit?

http://lessthandot.com

 

[sdagger2]

I think I may know what is going wrong. If I still have a problem after this I can use the savefile.com as you suggest. Just looking through my code again I may have spotted why it is not working but wouldn't know how to fix. Going back to your previous way perhaps rather than using name field but you will know better than me.

Here is order (no. next to name shows the current display order before the submit).

One Morgan Freeman(1)
Two Tim Robbins(2)
Three Bob Gunton(3)
Four William Sadler(4)
Five Patrick Swayze(5)
Six Keanu Reeves(6)
Seven Christian Bale(7)

order before submit is (newcastorder) 1,2,3,4,5,6,7

Then changing the order before submit:

Two Tim Robbins(2)
Three Bob Gunton(3)
Four William Sadler(4)
Five Patrick Swayze(5)
Six Keanu Reeves(6)
One Morgan Freeman(1)
Seven Christian Bale(7)

order to pass in submit is (newcastorder) 2,3,4,5,6,1,7

All that has changed on this instance is that Morgan Freeman(1) has moved to position 6 so the Tim(2) now needs to be 1 and Morgan(1) needs to be (6). If that makes sense. my head is hurting.

 

[Remou]

Forget about the display order for now, what is newnamearray or any version of the name array before and after?

http://lessthandot.com

 

[Remou]

... and what are the name fields in the database?

http://lessthandot.com

 

[sdagger2]

I have attached a link to the database names and here is the code (sorry if bit messy but a lot of it is commented out incase I need it again - I tidy it up when working).

<!--#include file="GetConnString.asp"-->

<%
SELECT CASE Left(UCase(Request("Action")),4)
CASE "UPDA"
REM Modify Database
response.write "update chosen<br/><br/>"
response.write "New order is: " & request("newcastorder") & "<br/><br />"
response.write "New name array is: " & request("newnamearray") & "<br/><br />"
response.write "ID array is: " & request("IDarray") & "<br/><br />"

arrAll = split(request("newcastorder"), ",")
'newArray = split(request("newnamearray"), ",")
'arrIDs = split(request("idArray"), ",")


Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Mode = 3
Connection.Open strProvider


astrArray=split(request("idArray"), ",")
For i=0 To UBound(astrArray)
strSQL="UPDATE tbdMoviecast SET displayorder=" & i+1 _
& " Where castID=" & astrArray(i)
' Connection.Execute(strSQL)
' cn.execute strSQL
response.write "sql is: " & strSQL & "<br />"
Next

astrArray=split(request("newnamearray"), ",")
For i=0 To UBound(astrArray)
strSQL="Update tbdMoviecast SET displayorder=" & i+1 _
& " Where Actor='" & Trim(Replace(astrArray(i),"'","''")) & "'"
Connection.Execute(strSQL)
response.write "sql is: " & strSQL & "<br />"
Next



' SQL = "UPDATE tbdMoviecast SET displayorder = " & item & " WHERE movieID = 22 AND actor = '" & item2 & "';"
' set rs = Connection.Execute(SQL)

Connection.Close


END SELECT
%>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"

http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html>
<head>
<title>test ordering with access</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-15" />



<script src="scriptaculous/lib/prototype.js" type="text/javascript"></script>
<script src="scriptaculous/src/scriptaculous.js" type="text/javascript"></script>
<style type="text/css">
body{font-family:"Lucida Sans Unicode", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; font-size:12px; color:#555555;}
div.main{margin:30px auto; width:600px;}
ul, ul li{padding:0px; border:0px; margin:0px; list-style:none;}
ul.myList li{display:block; border-top:solid 1px #DEDEDE; padding:3px;}
ul.myList li.over {background-color:#FFFFCC; border-top:solid 1px #999999;}
</style>



</head>
<body>

<form action="<% =Request.ServerVariables("SCRIPT_NAME")%>" method="post">

<h2>View test movies</h2>

<%
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Mode = 3
Connection.Open strProvider
%>

<div class="main">
<ul id="myList" class="myList">

<%
Set rsCast = Server.CreateObject("ADODB.Recordset")

rsCast.CursorType = 3
rsCast.CursorLocation = 2
rsCast.LockType = 1

SQL="SELECT * FROM tbdmoviecast WHERE movieID = 22 ORDER BY DisplayOrder;"
rsCast.open SQL, Connection

newnamearray = ""
IDarray = ""
' Display all contacts
Do Until (rsCast.EOF)
newname = rsCast("actor")
ID = rsCast("castID")
%>

<li id="item_<%= rsCast("displayorder") %>"><%= rsCast("actor") %>(<%= rsCast("displayorder") %>)</li>

<%
if newnamearray <> "" then
newnamearray = newnamearray & "," & newname
else
newnamearray = newname
end if

if IDarray <> "" then
IDarray = IDarray & "," & ID
else
IDarray = ID
end if
rsCast.MoveNext
Loop
%>
</ul>

<p id="myList_serialize"></p>

<script type="text/javascript" language="javascript" charset="utf-8">
Sortable.create('myList',{ghosting:false,constraint:true,hoverclass:'over',
onChange:function(element){
var totElement = 7;
var newOrder = Sortable.serialize(element.parentNode);
for(i=1; i<=totElement; i++){
newOrder = newOrder.replace("myList[]=","");
newOrder = newOrder.replace("&",",");
}
$('myList_serialize').innerHTML = '<strong>New Order</strong>: '+newOrder;
document.getElementById("myHiddenVar").value = newOrder;
}
});
</script>
</div>

<%
Set rsCast = Nothing
Connection.Close
%>
<input type="hidden" name="newnamearray" value="<%= newnamearray %>">
<input type="hidden" name="IDarray" value="<%= IDarray %>">
<input type="hidden" id="myHiddenVar" name="newcastorder">
<input type="Submit" name="Action" value="Update Page" />

</form>

</body>
</html>

 

[sdagger2]

Sorry. forgot to comment out some code. here it is working...but with the update execute commented out.

<!--#include file="GetConnString.asp"-->

<%
SELECT CASE Left(UCase(Request("Action")),4)
CASE "UPDA"
REM Modify Database
response.write "update chosen<br/><br/>"
response.write "New order is: " & request("newcastorder") & "<br/><br />"
response.write "New name array is: " & request("newnamearray") & "<br/><br />"
response.write "ID array is: " & request("IDarray") & "<br/><br />"

arrAll = split(request("newcastorder"), ",")
'newArray = split(request("newnamearray"), ",")
'arrIDs = split(request("idArray"), ",")


Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Mode = 3
Connection.Open strProvider


astrArray=split(request("idArray"), ",")
For i=0 To UBound(astrArray)
strSQL="UPDATE tbdMoviecast SET displayorder=" & i+1 _
& " Where castID=" & astrArray(i)
' Connection.Execute(strSQL)
' cn.execute strSQL
response.write "sql is: " & strSQL & "<br />"
Next

'astrArray=split(request("newnamearray"), ",")
'For i=0 To UBound(astrArray)
' strSQL="Update tbdMoviecast SET displayorder=" & i+1 _
' & " Where Actor='" & Trim(Replace(astrArray(i),"'","''")) & "'"
' Connection.Execute(strSQL)
'response.write "sql is: " & strSQL & "<br />"
'Next



' SQL = "UPDATE tbdMoviecast SET displayorder = " & item & " WHERE movieID = 22 AND actor = '" & item2 & "';"
' set rs = Connection.Execute(SQL)

Connection.Close


END SELECT
%>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"

http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html>
<head>
<title>test ordering with access</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-15" />



<script src="scriptaculous/lib/prototype.js" type="text/javascript"></script>
<script src="scriptaculous/src/scriptaculous.js" type="text/javascript"></script>
<style type="text/css">
body{font-family:"Lucida Sans Unicode", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; font-size:12px; color:#555555;}
div.main{margin:30px auto; width:600px;}
ul, ul li{padding:0px; border:0px; margin:0px; list-style:none;}
ul.myList li{display:block; border-top:solid 1px #DEDEDE; padding:3px;}
ul.myList li.over {background-color:#FFFFCC; border-top:solid 1px #999999;}
</style>



</head>
<body>

<form action="<% =Request.ServerVariables("SCRIPT_NAME")%>" method="post">

<h2>View test movies</h2>

<%
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Mode = 3
Connection.Open strProvider
%>

<div class="main">
<ul id="myList" class="myList">

<%
Set rsCast = Server.CreateObject("ADODB.Recordset")

rsCast.CursorType = 3
rsCast.CursorLocation = 2
rsCast.LockType = 1

SQL="SELECT * FROM tbdmoviecast WHERE movieID = 22 ORDER BY DisplayOrder;"
rsCast.open SQL, Connection

newnamearray = ""
IDarray = ""
' Display all contacts
Do Until (rsCast.EOF)
newname = rsCast("actor")
ID = rsCast("castID")
%>

<li id="item_<%= rsCast("displayorder") %>"><%= rsCast("actor") %>(<%= rsCast("displayorder") %>)</li>

<%
if newnamearray <> "" then
newnamearray = newnamearray & "," & newname
else
newnamearray = newname
end if

if IDarray <> "" then
IDarray = IDarray & "," & ID
else
IDarray = ID
end if
rsCast.MoveNext
Loop
%>
</ul>

<p id="myList_serialize"></p>

<script type="text/javascript" language="javascript" charset="utf-8">
Sortable.create('myList',{ghosting:false,constraint:true,hoverclass:'over',
onChange:function(element){
var totElement = 7;
var newOrder = Sortable.serialize(element.parentNode);
for(i=1; i<=totElement; i++){
newOrder = newOrder.replace("myList[]=","");
newOrder = newOrder.replace("&",",");
}
$('myList_serialize').innerHTML = '<strong>New Order</strong>: '+newOrder;
document.getElementById("myHiddenVar").value = newOrder;
}
});
</script>
</div>

<%
Set rsCast = Nothing
Connection.Close
%>
<input type="hidden" name="newnamearray" value="<%= newnamearray %>">
<input type="hidden" name="IDarray" value="<%= IDarray %>">
<input type="hidden" id="myHiddenVar" name="newcastorder">
<input type="Submit" name="Action" value="Update Page" />

</form>

</body>
</html>

 

[Remou]

This is the information I need:

response.write "New name array is: " & request("newnamearray") & "<br/><br />"
response.write "ID array is: " & request("IDarray") & "<br/><br />"

What is written out?

http://lessthandot.com

 

[sdagger2]

These are the values written out as attached link shows (in bordered box).

http://www.skms.co.uk/test/untitled-1.gif

 

[Remou]

So use the ID array, not the display order array. You even have it here:

SELECT CASE Left(UCase(Request("Action")),4)
    CASE "UPDA"    
	response.write "ID array is: " & request("IDarray") & "<br/><br />"

	Set Connection = Server.CreateObject("ADODB.Connection")
	Connection.Mode = 3
	Connection.Open strProvider


	astrArray=split(request("idArray"), ",")
	For i=0 To UBound(astrArray)
	    strSQL="UPDATE tbdMoviecast SET displayorder=" & i+1 _
		 & " Where castID=" & astrArray(i)
	    Connection.Execute(strSQL)
	    response.write "sql is: " & strSQL & "<br />"
	Next

	Connection.Close


END SELECT

http://lessthandot.com