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.
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>
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!