What have I done wrong with this re-order list control?

Question

This is driving me potty and I can't see anything wrong with it. The control displays as expected, but you can't reorder the items. Whenever you try, you get an alert saying "Reorder failed, see details below.\r\n\r\nFailed to reorder.", which isn't particularly helpful and I can't see any issues on the console or on the network traffic.

I have checked all the table and field names and they all match up, including case. Would appreciate people having a look at it and pointing out my mistake (which I have a nasty feeling is on the side of stupid).

ASP Page

    <form id="form1" runat="server">
        <asp:ScriptManager runat="server" ID="ScriptManager1" />

        <div style="width: 90%; margin-left: 2.5%; padding-top: 5px;">
            <ajax:ReorderList ID="rlActiveItems" runat="server" SortOrderField="Ordering" AllowReorder="true"
                DataSourceID="sdsActiveItems" DataKeyField="ID" LayoutType="table" ItemInsertLocation="End" ShowInsertItem="True" Width="100%">
                <DragHandleTemplate>
                    <span class="DragHandleClass">&nbsp;</span>
                </DragHandleTemplate>
                <ReorderTemplate>
                    <div class="DragClass">&nbsp;</div>
                </ReorderTemplate>
                <ItemTemplate>
                    <div class="etched bgBlue itemArea" data-item='<%#Eval("ID")%>' style="float: left; width: 100%; cursor: pointer;">
                        <div class="inner">
                            <div style="float: right; width: 30px; text-align: center;">
                                <asp:Image ID="iDelete" runat="server" ImageUrl="~/images/icon_delete.png" />
                            </div>
                            <div style="float: left; width: calc(100% - 40px); font-weight: bold;">
                                <asp:Label ID="lID" runat="server" Visible="false" Text='<%#Eval("ID")%>'></asp:Label>
                                <asp:Label ID="lOrder" runat="server" Visible="false" Text='<%#Eval("Ordering")%>'></asp:Label>
                                <%#Eval("Name")%>
                            </div>
                        </div>
                    </div>
                </ItemTemplate>
            </ajax:ReorderList>
        </div>

        <asp:SqlDataSource ID="sdsActiveItems" runat="server"
            ConnectionString="<%$ ConnectionStrings:ApplicationConnectionString %>"
            OldValuesParameterFormatString="original_{0}"
            SelectCommand="SELECT ID, Name, Ordering FROM LOGREQItems WHERE Active = 1 AND FkGroup = 3 ORDER BY Ordering"
            UpdateCommand="UPDATE [LOGREQItems] SET Ordering = @Ordering WHERE ID = @original_ID">
            
            <UpdateParameters>
                <asp:Parameter Name="Ordering" Type="Int32" />
                <asp:Parameter Name="original_ID" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>
    </form>

Table

CREATE TABLE [dbo].[LOGREQItems](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [FkGroup] [int] NOT NULL,
    [Ordering] [int] NOT NULL,
    [Active] [bit] NOT NULL

Answer 1

Well, the main issue is that you have the datasource on the page. So, it kind of persisting as a sorted data source. If you do NOT post-back, then it probably can work, but with your datasource, and the sort order fixed in that data source, then in theory you would have to update the table data.

So, I suggest you dump the on-page data source. I tend to not use them very much. (in fact, I will often use the wizards for a gridview or whatever, let the markup be generated and THEN delete the datasource, and the datasourceID from the given control.

so, lets do this in-memory, and without post-backs. This will allow you to see, try, play with the re-ordering.

(but, without updating the data source driving this, then we can save (but, I'll post a 2nd example that does).

Ok, so, our simple markup:

<ajaxToolkit:ReorderList ID="ReorderList1" runat="server"
    AllowReorder="True"      
    DataKeyField="ID" 
    SortOrderField="MyOrder" >

    <DragHandleTemplate>
        <span style="cursor:move"><i class="glyphicon glyphicon-move"></i></span>
    </DragHandleTemplate>

    <ReorderTemplate><div></div></ReorderTemplate>

    <ItemTemplate>
        <asp:TextBox ID="txtHotel" runat="server"
            Text='<%# Eval("HotelName") %>'></asp:TextBox>
    </ItemTemplate>
</ajaxToolkit:ReorderList>
<br />

<asp:Button ID="cmdSave" runat="server" Text="Save Changes" CssClass="btn" />

And our code to load:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadData()
    End If

End Sub

Sub LoadData()

    ReorderList1.DataSource =
        MyRst("SELECT ID, HotelName, MyOrder FROM tblHotelsA ORDER BY MyOrder")
    ReorderList1.DataBind()

End Sub

And we now get this:

Now, above works just fine for me.

(not a whole lot use, since we DO NOT update the table, nor order here).

However, lets now do this, and PERSIT our data table, and ALSO update that table.

So, now, when we drag + drop (move items around), we ALSO will update the MyOrder column in our data table. To do this, we now MUST post-back, and that ALSO means we WILL have to re-bind the re-order list control.

So, now we have this for the markup. (and I can stress that the MyOrder column setting MUST be set - for all of these examples).

Ok, so, now the markup is this:

<ajaxToolkit:ReorderList ID="ReorderList1" runat="server"
    AllowReorder="True"      
    DataKeyField="ID" 
    SortOrderField="MyOrder"
    PostBackOnReorder ="true"
    >

(no other changes).

but, our code NOW has to maintain, persist, and keep a copy of the data - we will use session (or, you could I suppose directly up the table data - but lets do that last).

So, now our code is this:

Dim rstData As New DataTable Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadData()
        Session("rstData") = rstData
    Else
        rstData = Session("rstData")
    End If

End Sub

Sub LoadData()

    rstData = MyRst("SELECT ID, HotelName, MyOrder FROM tblHotelsA ORDER BY MyOrder")
    ReorderList1.DataSource = rstData
    ReorderList1.DataBind()

End Sub

Note VERY careful - we maintain a copy of that table (in session()).

So, note how we declare rstData at the page class level - and not the page.load event, we load first time, shove into session (or for any other post-back, we restore from session() to that page class rstData var.

But, we now also have the event for the post-back, and that code has to update the rstData table with the results of the drag + drop.

We have this code:

Protected Sub ReorderList1_ItemReorder(sender As Object, e As AjaxControlToolkit.ReorderListItemReorderEventArgs) Handles ReorderList1.ItemReorder

    If e.OldIndex < e.NewIndex Then
        ' move everything up (towards top) in list
        For Each OneRow As DataRow In rstData.Rows
            If OneRow("MyOrder") = e.OldIndex Then
                OneRow("MyOrder") = e.NewIndex
            Else
                If OneRow("MyOrder") >= e.OldIndex And OneRow("MyOrder") <= e.NewIndex Then
                    OneRow("MyOrder") -= 1
                End If
            End If
        Next
    Else
        For Each OneRow As DataRow In rstData.Rows
            If OneRow("MyOrder") = e.OldIndex Then
                OneRow("MyOrder") = e.NewIndex
            Else
                If OneRow("MyOrder") >= e.NewIndex And OneRow("MyOrder") <= e.OldIndex Then
                    OneRow("MyOrder") += 1
                End If
            End If
        Next
    End If
    Session("rstData") = rstData

    Dim Myview As New DataView(rstData)
    Myview.Sort = "MyOrder"
    Call MyDisplay(Myview)
    ReorderList1.DataSource = Myview
    ReorderList1.DataBind()

End Sub

Now, again, the above works fine. Thus, as the user re-orders items, we post-back, and update that table. But, I have to actually re-save the table as sorted. (its in memory).

Ok, so now, what about actually saving back to the database?

Well, we have that save button.

The code to write back changes to the database is this:

Protected Sub cmdSave_Click(sender As Object, e As EventArgs) Handles cmdSave.Click

    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand("SELECT ID, HotelName, MyOrder FROM tblHotelsA", conn)
            Dim da As New SqlDataAdapter(cmdSQL)
            Dim daU As New SqlCommandBuilder(da)
            conn.Open()
            da.Update(rstData)
        End Using
    End Using

End Sub

The above will write back To the database any changes To rstData (In fact, As written, any inserts, delete(s), Or updates that occurred over time To the persisted table will ALL be updated back To the source (sql server) database. Note VERY close In that cute above routine - we Do Not re-pull the data With that sqlCMD Object, we ONLY send/write back the rstData table. And in in fact, only rows actually updated generate update commands back to sql server.