A common need when displaying a set of database records is the ability to order them by various of their data fields. Although the GridView and DataGrid controls include declarative statements to sort on data fields, the Repeater and DataList do not include this feature. Sorting for these controls must be scripted.

Sorting with a Repeater

In the following example, records from the BooksDB.mdb database are sorted for display in a Repeater. Sorting is triggered by clicking a button that serves as the column heading.

Coding for the Repeater includes a row of buttons replacing the column titles in the <HeaderTemplate>. These are <asp:Button> controls using OnCommand and CommandName properties to pass field names to a subprogram that retrieves a recordset in ascending order by that field value.

<SCRIPT Runat="Server">

Sub SortRepeater (Src As Object, Args As CommandEventArgs)

  Dim SQLString As String 
  SQLString = "SELECT * FROM Books ORDER BY " & Args.CommandName
  BookSource.SelectCommand = SQLString
	
End Sub

</SCRIPT>

<form Runat="Server">

<asp:AccessDataSource id="BookSource" Runat="Server"
  DataFile="../Databases/BooksDB.mdb"
  SelectCommand="SELECT * FROM Books ORDER BY BookID"/>

<asp:Repeater id="BookTable" DataSourceID="BookSource" Runat="Server">
  <HeaderTemplate>
    <table id="RepeaterTable" border="1" style="border-collapse:collapse">
    <tr style="background-color:#707070; color:#FFFFFF">
      <th>
        <asp:Button Text="ID" Runat="Server"
        OnCommand="SortRepeater"
        CommandName="BookID"
        Font-Size="8pt"/>
      </th>
      <th>
        <asp:Button Text="Type" Runat="Server"
        OnCommand="SortRepeater"
        CommandName="BookType"
        Font-Size="8pt"/>
      </th>
      <th>
        <asp:Button Text="Title" Runat="Server"
        OnCommand="SortRepeater"
        CommandName="BookTitle"
        Font-Size="8pt"/>
      </th>
      <th>
        <asp:Button Text="Author" Runat="Server"
        OnCommand="SortRepeater"
        CommandName="BookAuthor"
        Font-Size="8pt"/>
      </th>
      <th>
        <asp:Button Text="Price" Runat="Server"
        OnCommand="SortRepeater"
        CommandName="BookPrice"
        Font-Size="8pt"/>
      </th>
      <th>
        <asp:Button Text="Qty" Runat="Server"
        OnCommand="SortRepeater"
        CommandName="BookQty"
        Font-Size="8pt"/>
      </th>
    </tr>
  </HeaderTemplate>
  <ItemTemplate>
    <tr style="vertical-align:top">
      <td style="text-align:center"><%# Eval("BookID") %></td>
      <td><%# Eval("BookType") %></td>
      <td><%# Eval("BookTitle") %></td>
      <td><%# Eval("BookAuthor") %></td>
      <td style="text-align:right"><%# Eval("BookPrice") %></td>
      <td style="text-align:right"><%# Eval("BookQty") %></td>
    </tr>
  </ItemTemplate>
  <FooterTemplate>
    </table>
  </FooterTemplate>
</asp:Repeater>

</form>

You should note that the OnCommand event handlers in the buttons are not command events associated with the Repeater itself, as is the case when AllowSorting="True" is specified for a GridView or DataGrid and command events are issued through these controls. These buttons are independent sources of command events that individually call the Sort_Repeater subprogram. Sorting requires no more than composing an SQL statement to retrieve records in a particular field order. That order is given by the CommandName argument passed to the subprogram by a button click. The ordered recordset is re-bound to the Repeater by assigning the SELECT statement to the SelectCommand property of the AccessDataSource associated with the Repeater.

Sorting with a DataList

A DataList control does not have a built-in sorting feature. In the following example, a set of radio buttons selects a sort field and indicates ascending or descending sequence. A button is added to call the Sort_DataList subprogram.

A table to format the radio buttons is added immediately before the DataList. The first set of buttons permits choice of a sort field; the second set is for choosing the direction of sort. A standard button calls the Sort_DataList subprogram.

<SCRIPT Runat="Server">

Sub Sort_DataList (Src As Object, Args As EventArgs)

  Dim SQLString As String = "SELECT * FROM Books ORDER BY " & _
    SortButtons.SelectedValue & " " & DirectionButtons.SelectedValue
  BookSource.SelectCommand = SQLString

End Sub

</SCRIPT>

<form Runat="Server">

<asp:AccessDataSource id="BookSource" Runat="Server"
  DataFile="../Databases/BooksDB.mdb"
  SelectCommand="SELECT * FROM Books ORDER BY BookID"/>

  <table border="1" width="550" style="border-collapse:collapse">
  <tr style="vertical-align:bottom; background-color:#707070; color:#FFFFFF">
    <td style="font-size:10pt">
      <b> Order By: </b><br/>
      <asp:RadioButtonList id="SortButtons" Runat="Server"
        RepeatDirection="Horizontal"
        RepeatLayout="Flow">
        <asp:ListItem Text="ID " Value="BookID" Selected="True"/>
        <asp:ListItem Text="Type " Value="BookType"/>
        <asp:ListItem Text="Title " Value="BookTitle"/>
        <asp:ListItem Text="Author " Value="BookAuthor"/>
        <asp:ListItem Text="Price " Value="BookPrice"/>
        <asp:ListItem Text="Quantity " Value="BookQty"/>
      </asp:RadioButtonList>
    </td>
    <td style="font-size:10pt">
      <b>Direction: </b><br/>
      <asp:RadioButtonList id="DirectionButtons" Runat="Server"
        RepeatDirection="Horizontal"
        RepeatLayout="Flow">
        <asp:ListItem Text="ASC" Value="ASC" Selected="True"/>
        <asp:ListItem Text="DESC" Value="DESC"/>
      </asp:RadioButtonList>
    </td>
    <td>
      <asp:Button Text="Sort" OnClick="Sort_DataList" Runat="Server" />
    </td>
  </tr>
  </table>

<asp:DataList id="BookList" DataSourceID="BookSource" Runat="Server"
  Width="550"
  CellSpacing="3"
  CellPadding="5"
  RepeatColumns="2"
   RepeatDirection="Horizontal"
  GridLines="Both"
  ItemStyle-BackColor="#F9F9F9"
  ItemStyle-Font-Names="Times New Roman"
  ItemStyle-Font-Size="9pt">
  
  <ItemTemplate>
    <asp:Image Width="100" Style="float:left" Runat="Server"
    ImageUrl='<%# "../eCommerce/BookPictures/" & Eval("BookID") & ".jpg" %>'/>
    <b>ID: </b>
      <asp:Label Text='<%# Eval("BookID") %>' Runat="Server"/><br/>
    <b>Type: </b>
      <asp:Label Text='<%# Eval("BookType") %>' Runat="Server"/><br/>
    <b>Title: </b>
      <asp:Label Text='<%# Eval("BookTitle") %>' Runat="Server"/><br/>
    <b>Author: </b>
      <asp:Label Text='<%# Eval("BookAuthor") %>' Runat="Server"/><br/>
    <b>Price: </b>
      <asp:Label Text='<%# Eval("BookPrice") %>' Runat="Server"/><br/>
    <b>Quantity: </b>
      <asp:Label Text='<%# Eval("BookQty") %>' Runat="Server"/><br/>
  </ItemTemplate>

</asp:DataList>

</form>

As in the previous example, the Sort_DataList subprogram creates an SQL statement that is assigned to the AccessDataSource's SelectCommand property. In this case, two values are appended to the statement. The field name is given by the value of the checked radio button in the first set; the value ASC or DESC is given by the second set. The resulting SQL statement is in the format SELECT * FROM Books ORDER BY field ASC (or DESC). This same sorting technique, of course, can be applied to a Repeater control.