When displaying records from a database table it is often convenient to be able to select particular records for display rather than displaying the entire recordset. This selection involves specifying some criterion value for one of the fields and then extracting only those records which meet that criterion.

Selecting Records with the Repeater

The following Repeater permits record sorting by clicking buttons for those sort fields. In addition, selections are provided to choose subsets of records from the Books table of the BooksDB.mdb database which meet specified search criteria. For instance, the following subset of records shows all books for which the BookType field equals "Database", sorted in ascending sequence by BookID. Different selection fields and comparison values can be chosen from drop-down lists and displayed in different sort orders.

Coding the Repeater

In order to make a selection and specify a sort order, an SQL statement must be composed to read like the following statement used in the AccessDataSource control to retrieve the initial set of "Database" BookTypes in ascending BookID order.

SELECT * FROM Books WHERE BookType='Database' ORDER BY BookID ASC

Controls are appended to the Repeater to collect information to create this statement. A DropDownList permits selection of a field name, a second DropDownList selects a conditional operator, and a TextBox provides the value for comparison. These three controls permit creation of an SQL WHERE clause giving the criterion for selecting records from the Books table. A RadioButtonList is also added to specify the sort order of selected records and to compose the SQL statement's ORDER BY clause.

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

<asp:Panel id="FieldPanel" BackColor="#707070" ForeColor="#FFFFFF" 
Width="530" Runat="Server">
  <b> Select Field: </b>
  <asp:DropDownList id="FieldName" Runat="Server">
    <asp:ListItem Value="BookID" Text="ID"/>
    <asp:ListItem Value="BookType" Text="Type" Selected="True"/>
    <asp:ListItem Value="BookTitle" Text="Title"/>
    <asp:ListItem Value="BookAuthor" Text="Author"/>
    <asp:ListItem Value="BookDescription" Text="Description"/>
    <asp:ListItem Value="BookPrice" Text="Price" />
    <asp:ListItem Value="BookQty" Text="Quantity"/>
  </asp:DropDownList>
  <asp:DropDownList id="TheOperator" Runat="Server">
    <asp:ListItem Value=" LIKE " Text="Contains"/>
    <asp:ListItem Value=" Not LIKE " Text="Does Not Contain"/>
    <asp:ListItem Value=" < " Text="Less Than"/>
    <asp:ListItem Value=" = " Text="Equal To" Selected="True"/>
    <asp:ListItem Value=" > " Text="Greater Than"/>
    <asp:ListItem Value=" <> " Text="Not Equal To"/>
  </asp:DropDownList>
  <asp:TextBox id="FieldValue" Text="Database" Runat="Server"/><br/>
  <b> Sort: </b>
  <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>
</asp:Panel>

<asp:Repeater id="RepeaterDisplay" DataSourceID="BookSource" Runat="Server">
  <HeaderTemplate>
    <table id="RepeaterTable" border="1" style="width:530px; 
    border-collapse:collapse">
    <tr style="background-color:#707070; color:#FFFFFF">
      <th>
        <asp:Button Text="ID" Font-SIze="8pt" Runat="Server"
          OnCommand="Sort_Repeater"
          CommandName="BookID"/>
      </th>
      <th>
        <asp:Button Text="Type" Font-Size="8pt" Runat="Server"
          OnCommand="Sort_Repeater"
          CommandName="BookType"/>
      </th>
      <th>
        <asp:Button Text="Title" Font-Size="8pt" Runat="Server"
          OnCommand="Sort_Repeater"
          CommandName="BookTitle"/>
      </th>
      <th>
        <asp:Button Text="Author" Font-Size="8pt" Runat="Server"
          OnCommand="Sort_Repeater"
          CommandName="BookAuthor"/>
      </th>
      <th>Description</th>
      <th>
        <asp:Button Text="Price" Font-Size="8pt" Runat="Server"
          OnCommand="Sort_Repeater"
          CommandName="BookPrice"/>
      </th>
      <th>
        <asp:Button Text="Qty" Font-Size="8pt" Runat="Server"
          OnCommand="Sort_Repeater"
          CommandName="BookQty"/>
      </th>
    </tr>
  </HeaderTemplate>
  <ItemTemplate>
    <tr style="vertical-align:top">
      <td style="text-align:center">
          <asp:Label Text='<%# Eval("BookID") %>' Runat="Server"/></td>
      <td><asp:Label Text='<%# Eval("BookType") %>' Runat="Server"/></td>
      <td><asp:Label Text='<%# Eval("BookTitle") %>' Runat="Server"/></td>
      <td><asp:Label Text='<%# Eval("BookAuthor") %>' Runat="Server"/></td>
      <td><asp:Panel ScrollBars="Auto" Width="180" Height="60" Runat="Server">
            <asp:Label Font-Size="9" Style="line-height:10pt" Runat="Server"
            Text='<%# Eval("BookDescription") %>'/><br/>
          </asp:Panel></td>
      <td style="text-align:right">
          <asp:Label Text='<%# Eval("BookPrice") %>' 
          Runat="Server"/></td>
      <td style="text-align:right">
          <asp:Label Text='<%# Eval("BookQty") %>' 
          Runat="Server"/></td>
    </tr>
  </ItemTemplate>
  <FooterTemplate>
    </table>
  </FooterTemplate>
</asp:Repeater>

Field name selection is made through an <asp:DropDownList> supplying field names from the Books table as item Value properties. Field names are hard-coded as ListItem values; however, an AccessDataSource control could have been associated with this DropDownList to extract names from the database.

<b> Select Field: </b>
<asp:DropDownList id="FieldName" Runat="Server">
  <asp:ListItem Value="BookID" Text="ID"/>
  <asp:ListItem Value="BookType" Text="Type" Selected="True"/>
  <asp:ListItem Value="BookTitle" Text="Title"/>
  <asp:ListItem Value="BookAuthor" Text="Author"/>
  <asp:ListItem Value="BookDescription" Text="Description"/>
  <asp:ListItem Value="BookPrice" Text="Price" />
  <asp:ListItem Value="BookQty" Text="Quantity"/>
</asp:DropDownList>

Conditional operators are selected from a second DropDownList whose Values are actual conditional operators and whose Text properties are verbal equivalents. (If you are not familiar with conditional operators used in SQL statements for Access databases, refer to the SQL appendix.)

<asp:DropDownList id="TheOperator" Runat="Server">
  <asp:ListItem Value=" LIKE " Text="Contains"/>
  <asp:ListItem Value=" Not LIKE " Text="Does Not Contain"/>
  <asp:ListItem Value=" < " Text="Less Than"/>
  <asp:ListItem Value=" = " Text="Equal To" Selected="True"/>
  <asp:ListItem Value=" > " Text="Greater Than"/>
  <asp:ListItem Value=" <> " Text="Not Equal To"/>
</asp:DropDownList>

Finally, the search criterion is given in a TextBox control. Values appearing in SQL statements are not case sensitive, so either lower-case, upper-case, or mixed-case characters can be entered.

<asp:TextBox id="FieldValue" Text="Database" Runat="Server"/>

Composing an SQL Statement

The values from the three controls can be used to construct an SQL WHERE clause for selecting records that meet the criterion. For instance, when "BookType" is selected from the FieldName DropDownList, " = " is selected from the TheOperator list, and "Database" is entered in the FieldValue textbox, then concatenating the strings

" WHERE "
& FieldName.SelectedValue
& TheOperator.SelectedValue
& "'"
& FieldValue.Text
& "'"

produces the string

WHERE BookType = 'Database'

which can be appended to a SELECT statement to extract records based on this condition test. Of course, slightly different concatenations are needed for different operators and different field types, but the idea works the same.

Composition of the SELECT statement takes place in the Sort_Repeater subprogram that was used previously and is now expanded to include additional steps to create an SQL statement with a WHERE clause composed from selected drop-down values. The column header buttons call this subprogram which assigns the composed statement to the SelectCommand property of the AccessDataSource.

Sub Sort_Repeater (Src As Object, Args As CommandEventArgs)

  Dim SQLString As String = "SELECT * FROM Books"
  
  If FieldValue.Text <> "" Then
    
    If TheOperator.SelectedValue = " LIKE " _
       OR TheOperator.SelectedValue = " Not LIKE " Then
         '-- "Contains" comparison, e.g.,
         '-- WHERE field LIKE 'value'
      SQLString &= " WHERE " & FieldName.SelectedValue & _
      TheOperator.SelectedValue & "'%" & FieldValue.Text & "%'"
      
    Else
      
      If FieldName.SelectedValue <> "BookPrice" AND _
         FieldName.SelectedValue <> "BookQty" Then
           '-- Alphanumeric comparising, e.g.,
           '-- WHERE field = 'value'
        SQLString &= " WHERE " & FieldName.SelectedValue & _
        TheOperator.SelectedValue & "'" & FieldValue.Text & "'"
      Else
           '-- Numeric comparison, e.g.,
           '-- WHERE field = value
        SQLString &= " WHERE " & FieldName.SelectedValue & _
        TheOperator.SelectedValue & FieldValue.Text
      End If
      
    End If
    
  End If
  
  SQLString &= " ORDER BY " & Args.CommandName
  SQLString &= " " & DirectionButtons.SelectedValue
  
  BookSource.SelectCommand = SQLString
  
End Sub

Composing the WHERE clause depends on the user having entered a search criterion value in the textbox. If the textbox is empty, this portion of the subprogram is not run and no WHERE clause is appended to the SQLString.

The format of the WHERE clause differs slightly when specifying string versus numeric comparisons. When the comparison value is a string, it must be enclosed in single quotes (apostrophes); when the comparison value is a number, no quotes are used. For example,

WHERE BookType = 'Database'
WHERE BookPrice > 50

So, the script supplies different SQL coding for the BookID, BookType, BookTitle, BookAuthor, and BookDescription fields (which are strings whose comparison values are enclosed in apostrophes) versus the BookPrice and BookQty fields (which are numbers whose comparison values are not enclosed in single quotes).

When using LIKE or  Not LIKE comparisons, all values are treated as strings and no differentiation in coding is made. The script uses the general search format  LIKE '%value%' to locate the entered value anywhere in the field. This type of search is used in the BookDescription field where any word or partial word can be found anywhere in the field.

After the WHERE clause is composed and appended to the SQLString variable, the ORDER BY clause is added.

SQLString &= " ORDER BY " & Args.CommandName

As before, the sort field is given by the CommandName associated with the button clicked in the column header. Next, either ASC or DESC is appended to the SQLString depending on which of the radio buttons is checked.

SQLString &= " " & DirectionButtons.SelectedValue

Finally, the completed SQLString is issued by assigning it to the SelectCommand property of the AccessDataSource associated with the Repeater.

BookSource.SelectCommand = SQLString

The data source is bound to the Repeater with records matching the search criterion and sorted according to the button click.

Selecting Records with the DataList and DataGrid

It is not necessary to show coding for either the DataList or DataGrid control. A selection panel can be added to these controls to work identically to the Repeater. The Sort_DataList and Sort_DataGrid subprograms used in previous examples are modified in the same way as is done for the Repeater.