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

<b>SQL Statement:</b><br/>
<asp:TextBox id="SQLStatement" TextMode="MultiLine" Columns="60" 
Font-Size="9pt" Runat="Server"
Text="SELECT * FROM Books WHERE BookType='Graphics' " & _
     "ORDER BY BookPrice DESC"/>
<asp:Button Text="Select" Runat="Server" OnClick="Display_Repeater"/><br/>
<asp:Label id="ERRMsg" EnableViewState="False" ForeColor="#FF0000" 
  Runat="Server"/>

<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>ID</th>
      <th>Type</th>
      <th>Title</th>
      <th>Author</th>
      <th>Description</th>
      <th>Price</th>
      <th>Qty</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>

Notice that the textbox has the property setting TextMode="MultiLine" with 60 columns and a default of 2 rows. The label for the error message is coded with EnableViewState="False" so that error messages are cleared when re-posting the form.

Executing the SQL Command

When the "Submit" button is clicked, the Display_Repeater subprogram is called. As long as a valid SQL statement is entered into the textbox, coding of this subprogram is as simple as assigning the statement to the SelectCommand property of the Access Data Source.

Sub Display_Repeater (Src As Object, Args As EventArgs)
  BookSource.SelectCommand = SQLStatement.Text
End Sub

However, if an invalid statement is entered, an execution error occurs. When using an AccessDataSource to populate the Repeater, there is no way to test for this error to handle it elegantly. The subprogram simply aborts and a system error is generated, leaving the user unsure of what happened. Therefore, a different tactic must be used to trap these errors and recover from them.

A run-time error can be trapped only by using a data access script to discover the error. This amounts to issuing the SQL statement through an OleDbCommand object, not through an AccessDataSource. Coding to accomplish this error checking in the Display_Repeater subprogram is shown below. It checks for an invalid SQL statement and displays a "friendly" error message if the statement cannot be properly executed.

<%@ Import Namespace="System.Data.OleDb" %>

<SCRIPT Runat="Server">

Sub Display_Repeater (Src As Object, Args As EventArgs)

  Try
    Dim DBConnection As OleDbConnection = New OleDbConnection( _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb"))
    DBConnection.Open()
    Dim DBCommand = New OleDbCommand(SQLStatement.Text, DBConnection)
    DBCommand.ExecuteReader()
    DBConnection.Close()
    BookSourceSource.SelectCommand = SQLStatement.Text
  Catch
    ERRMsg.Text &= "-- Error in SQL statement --"
    ERRMsg.Visible = True
  End Try
  
End Sub

</SCRIPT>

Since a script is used to access the BooksDB.mdb database, the System.Data.OleDb namespace must be imported to the page.

Using Try...Catch

The Visual Basic Try...Catch structure is used to encapsulate code that can cause execution errors. Its general format is shown below.

Try
	
  ...execution code
	
Catch
	
  ...error code
	
End Try

Code that can cause run-time execution errors is placed inside the Try section; code to respond to an error is placed inside the Catch section. If there is execution failure in the Try code, the Catch code is run instead. In the above example, database access with the SQL statement is in the Try section; display of an error message appears in the Catch section.

Notice that the OleDbCommand directly executes the entered SQL statement, that is, the Text property of the SQLStatement TextBox. The Command's ExecuteReader() method is the one that can cause a run-time error which is trapped by the Try section of code.

No recordset is returned by the ExecuteReader() method. It is used only to test whether or not the entered SQL statement is valid and can be issued without error. If an error occurs, the remaining statements in the Try section of script are skipped and the Catch section of code is run. If no error is generated, then the SQL statement is valid. The final statement in the Try section assigns the entered SQL command to the SelectCommand property of the AccessDataSource and the subset of records are returned for display.

Similar techniques can be used with any of the information display controls.