Save Order with jQuery UI Sortable and ASP/Access

I had a project where the user could save a session (at our annual meeting) from the meeting program to a personal itinerary, which they could then view anywhere, as well as export to Word or Excel. I had the basics set up – it wrote the information to a simple Access database, which the ASPX page then pulled and output as divs in a repeater. However, I really like the Netflix queue interface – the user can drag and drop items and it automatically saved the layout. I figured I could use Jquery UI sortable for the client-side interface, but I was having trouble with the callback function to save the new order to the database. All the examples I found online were in PHP, but I work on an old, crappy server running ASP.NET 1.1, so I needed something in simple ASP.NET or classic ASP. I finally got it working through the callback function calling a GET to an ASP page and thought I would share.

First, the javascript:


<script type="text/javascript" src="jquery-1.4.2.min.js">
<script type="text/javascript" src="jquery-ui-1.8.2.custom.js">

<script type="text/javascript">
$(function() {
	$("#sortable").sortable(    
		{opacity: 0.7,
	 	revert: true,
	 	scroll: true, 
		cursor: 'crosshair', 
		update : function () { 
      			var order = $('#sortable').sortable('serialize'); 
			//alert(order);
      			$("#info").load("update-order.asp?"+order); 
			}
	}); 
});

</script>

Then, the HTML. Note that the container div to be sorted has an id of ‘sortable’. And the divs that are sortable have ids of ‘favItem_#’. The ID is important, as that is what jquery UI uses to serialize the current order and append to the GET call. In this case, I appended the ID from the database (container.dataitem) to the repeater in the div id to keep it unique.


<div id="info"></div> <!--This receives the message from the ASP page -->
<div id="sortable">
<div id="favItem_1" class="dragit">
	<p>This is the Item</p>
</div>
<div id="favItem_2" class="dragit">
	<p>This is the Item</p>
</div>
<div id="favItem_3" class="dragit">
	<p>This is the Item</p>
</div>
</div>

At this point, your page should be able to drag and drop. You can uncomment the ‘//alert(order);’ line in the javascript to see the querystring that is passed to the ASP page after you drop an item.

And finally, I set up the ASP page. This grabs the whole querystring, splits it to create an array, then for each item in the array, executes a SQL statement to insert it into the Access database.


<%
'this takes a querystring (all the same name), splits it into an array
'and then writes the array to the database
'fired from jquery when someone drags-drops the item; this saves the order to the database
'and then on refresh or reload, the page is ordered by the sess_order field

Dim getItems, data_source, con, sql_update
'getting the items from the querystring
getItems = Request.QueryString("favItem[]")

'split them junks into array
getItems = Split(getItems, ",")

'define the data source
   data_source = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=dbpassword;data source=" & server.mappath("yourdb.mdb")

'opening the database
 Set con = Server.CreateObject("ADODB.Connection")
   con.Open data_source

'For each one, define and then execute the sql statement
For i = LBound(getiTems) TO UBound(getItems)
   sql_update = "UPDATE sessions SET sess_order = " & i & " WHERE ID = " & getItems(i)
   'execute this bitch
   con.Execute sql_update
   'below used for debugging - comment out
   'Response.Write("ID " & getItems(i) & " was set to position " & i)
Next

' Done. Close the connection
  con.Close
  Set con = Nothing

Response.Write("Your order has been updated.")

%>

There you have it – a Netflix style, Jquery UI sortable page with persist, via AJAX GET call to an ASP page that writes to an Access database. Shit yeah!

Published by

Hal

Aside from being a champion yo-yoer, I am the full-time computer geek at the American Society of Nephrology. I recently completed my MBA from George Washington University which I am hoping will enable me to finally afford my own bad habits. I also do freelance design, specializing in Flash, PHP, and ASP/ASP.NET.

20 thoughts on “Save Order with jQuery UI Sortable and ASP/Access”

  1. This saved me a lot of time and effort, a big thanx for that!

    And for the next person how likes to copy/paste like I did, there is an quotation mark missing ” in one of the divs

    Tanks!!!!

  2. Thank you very much Hal! I’ve been searching all over for this type of code example and could not find it anywhere.

    If I was programming in .NET or PHP I would have had a ton of options to choose from. But those of us who want to update legacy Classic ASP code with some new features always have to struggle.

    Your efforts are much appreciated…they saved me a ton of time and a huge learning curve.

    All the best.

    Tom

  3. Sorry if this sounds dumb, but where do I get jquery-1.4.2.min.js and jquery-ui-1.8.2.custom.js from?

  4. NJ – just sent you an email. You can get older versions hosted on Google CDN. However, the code is basic, so it should work with the latest jquery/jqueryUI, which is what I recommend. Shoot me an email if you would like some help debugging.

  5. Hi,

    This is working real fine 😀
    Only one question:
    if i sort an image it gets updated on the database and i get the success message. But if i sort another image right after the other one it wont get updated.
    To do this, i have to refresh the page and then it works again.
    I can refresh the page automatically but thats not good..

    Any suggestion?

    Sorry for my english, im portuguese 😛

    Cheers,
    Daniel

  6. Daniel – when you say it won’t get updated, do you mean that the serialization order isn’t being updated (which you can check by uncommenting out the alert in the ‘update’ callback)? Or the serialization is correct, but your server side script isn’t updating the data in the database? If you’ll let me know and post some code snippets (or a link to a jsfiddle), I’ll check it out.

  7. Hal, that’s great.
    I’d like to display records from the database in a table.
    I can’t seem to find a way to do this, could you help please?
    Here’s the code and I’ve tried different combinations of where to place the sortable div

    <div id="favItem_” class=”dragit”>

  8. Snappy – if you want to output as a table (and make the rows sortable), then instead of using divs, you want to apply the serialized id (favItem_#) to the tr tag for each row, and then use the table ID > tbody as the selector. However, with a table, as soon as the row is dragged outside of the parent, its width will adjust to the width of the content, rather than the original parent. To prevent this, you want to apply a helper to the object that sets the correct width. I have dummied up a complete working sample; see below:

    
    
    <!DOCTYPE html>
    <head>
    <title>jQueryUI Sortable on Table</title>
    <style type="text/css">
    	table { width: 100%; }
    	table, th, td { border: 1px solid #666666; border-collapse: collapse; padding: 30px;}
    </style>
    <script type="text/javascript" src="//code.jquery.com/jquery-latest.min.js"></script>
    <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/jquery-ui.min.js"></script>
    <script type="text/javascript">
    $(document).ready(function(){
    
    	var fixHelper = function(e, ui) {
    		ui.children().each(function() {
    			$(this).width($(this).width());
    		});
    		return ui;
    	};
    
    	$("#sortable tbody").sortable({
    		helper: fixHelper,
    		opacity: 0.9,
    		revert: true,
    		cursor: 'move',
    		update: function() {
    			var order = $('#sortable tbody').sortable('serialize');
    			$("#info").html('The updated order is: ' + order);
    		}
    	});
    });
    </script>
    </head>
    <body>
    <div id="info"></div>
    <table id="sortable">
    	<thead>
        		<tr>
            		<th>Items</th>        
        		</tr>
    	</thead>
    	<tbody>
    		<tr id="favItem_1">
    			<td>Item 1</td>
    		</tr>
    		<tr id="favItem_2">
    			<td>Item 2</td>
    		</tr>
    		<tr id="favItem_3">
    			<td>Item 3</td>
    		</tr>
    	</tbody>
    </table>
    </body>
    </html>
    
    
    
  9. Hello,

    this is really great job. The drag and drop is working fine, but I am wondering how it calls the ‘update-order.asp’ in order to update the records in database?

    Thanks a lot,

  10. Hi Fay – it calls the update-order.asp in the “update” callback function with this line:

    $(“#info”).load(“update-order.asp?”+order);

    the jQuery “load” function is equivalent to $.get(url, data, success) – it passes the ordered items (held in the “order” variable) to the “update-order.asp” page and the response from that ASP page is then loaded into the #info div in your calling page.

    Does that make sense? If not, shoot me an email at hal@halnesbitt.com, and I’d be happy to help.

  11. jquery-ui-1.8.2.custom.js – cannot find anywhere 🙁

    I have been looking for a solution to do this in a classic ASP page, but writing to MySQL which i ought to be able to figure out when I get it working!

    Do you still have a copy of ‘jquery-ui-1.8.2.custom.js’ you can email me?

  12. Thanks Hal, I incoorperated into MSSQL and classic asp and worked perfect.

    Is there a way to lock certain rows from being dragged & dropped. I tried a around the heading rows which stopped it from being moved however the other allowable D/D rows did not work or update

  13. Hi Denis – you sure can. When calling sortable() in your javascript, add the “cancel” option to your initializer, and specify the element or elements that you want to prevent from being sortable. For example:

    
    $( "#sortable" ).sortable({
      cancel: ".classToExclude, #idToExclude"
    });
    
    

Leave a Reply

Your email address will not be published. Required fields are marked *