Much of the work of displaying information from external data sources can be accomplished by binding data source controls to information display controls. Page output is produced declaratively without having to write much, if any, script. On occasion, though, you may wish to have more control over display of this information, especially if the data require processing prior to display. In this case, you may need to access data sources directly through your scripts.

Data Providers

The ASP.NET framework includes the ADO.NET (ActiveX Data Objects.NET) data access technology for working with databases and other external data sources. ADO.NET supplies a data Provider, a set of software objects for accessing a data source. A Provider sits between the Web page and the external data source, permitting script access while hiding the physical details of the source. Providers are supplied for Microsoft Access, Microsoft SQL Server, and Oracle databases, as well as for other relational and hierarchical data sources. In effect, all external data sources regardless of their physical structure are accessible through a common set of scriptable objects.

There are four core software objects that make up the ADO.NET data Provider. These objects are described in the following table.

For this introduction to scripted data access, only the Connection, Command, and DataReader objects are needed and described. Working with the DataAdapter is reserved for later discussion. Also, focus is on accessing Microsoft Access databases although the techniques are very similar for other database products and other data sources.

Database Namespace Directive

ASP.NET pages that connect to data sources through scripts must gain access to the system classes that provide data access functionality. For working with Microsoft Access and other databases that use OLE DB (Object Linking and Embedding - Database) Providers, the page must import the System.Data.OleDb namespace by including the following directive on the page.

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

This namespace includes the following four basic objects needed to work with Access databases. Again, the OleDbDataAdapter is reserved for later consideration.

Opening a Database Connection

A script connection to a database is made by creating an OleDbConnection object. Then, this connection's Open() method opens the database. General formats for creating and opening database connections are shown in Figure 3-15.

Dim Connection As OleDbConnection
Connection = New OleDbConnection(ConnectionString)
Connection.Open()

or

Dim Connection As OleDbConnection = New OleDbConnection(ConnectionString)
Connection.Open()

or

Dim Connection = New OleDbConnection(ConnectionString)
Connection.Open()

Connection is a programmer-supplied reference for the object; ConnectionString specifies the OLE DB Provider and the path to the database; the Open() method opens the connection for script access.

A ConnectionString is composed of two parts. A Provider string specifies the name of the data Provider associated with the database type. For Access databases the Provider is "Microsoft.Jet.OLEDB.4.0". The Data Source string gives the physical path to the database on the server. These two clauses are separated by a semicolon and compose a single connection string.

The example BooksDB.mdb database, for example, is located on the server path c:\eCommerce\Databases\BooksDB.mdb. For the BooksDB.mdb database the connection string is coded in either of the two following formats.

Dim DBConnection = New OleDbConnection( _
  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=c:\eCommerce\Databases\BooksDB.mdb")

or

Dim DBConnection = New OleDbConnection( _
  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb"))

Here, referent DBConnection is assigned to the connection object. You can supply the physical path to the database, or you can code the path relative to the current Web page. In the latter case, the System.MapPath() method must be applied to the relative path to convert it to a physical path.

The following code begins a database access script by opening a connection to the BooksDB.mdb database. Here, the database is opened in the Page_Load subprogram. A database can be opened in this subprogram for accessibility when the page opens, or it can be opened in any other subprogram requiring access to the database.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@ Page Language="vb" Debug="True" %>
<%@ Import Namespace="System.Data.OleDb" %>

<SCRIPT Runat="Server">

Sub Page_Load

  '-- Open a database connection
  Dim DBConnection = New OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb"))
  DBConnection.Open()

End Sub

</SCRIPT>

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
  <title>Database Scripting</title>
</head>
<body>
<form Runat="Server">

<asp:GridView id="BookGrid" Runat="Server"/>

</form>
</body>
</html>

Note that this is only the beginning of the database access script. Additional code is explained throughout this tutorial. Also notice that a GridView control appears on the page. The end result of the script is binding to this control for database display.

Creating a Database Command

Selection of records from a database table is made through the OleDbCommand object. The command supplied can be an SQL statement or the name of a stored procedure that is issued through the previously opened connection to the database. The general formats for creating a Command object are shown below.

Dim Command As OleDbCommand
Command = New OleDbCommand(CommandString, Connection)

or

Dim Command As OleDbCommand = New OleDbCommand(CommandString, Connection)

or

Dim Command = New OleDbCommand(CommandString, Connection)

-- Using Stored Procedures --
Command.CommandType = CommandType.StoredProcedure

Command is a programmer-supplied reference for the object; CommandString is an SQL statement or named stored procedure; Connection is a reference to a database connection previously opened. Any of the SQL statement types (SELECT, INSERT, UPDATE, DELETE, and others) can be issued through the Command object. For display purposes, a SELECT statement retrieves a set of records from a database table.

If the CommandString is the name of a stored procedure, or Query, then a CommandType property must be set for the Command object. Its value is CommandType.StoredProcedure.

In the following continuation of the above script, an SQL SELECT statement is composed and assigned to variable SQLString. Then a new OleDbCommand object is created and assigned the reference DBCommand. This object issues the SQL statement through the previously opened database connection.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@ Page Language="vb" Debug="True" %>
<%@ Import Namespace="System.Data.OleDb" %>

<SCRIPT Runat="Server">

Sub Page_Load

  '-- Open a database connection
  Dim DBConnection = New OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb"))
  DBConnection.Open()

  '-- Issue an SQL command through the database connection
  Dim SQLString As String
  SQLString = "SELECT BookID, BookType, BookTitle, BookPrice, BookQty " & _
              "FROM Books ORDER BY BookID"
  Dim DBCommand = New OleDbCommand(SQLString, DBConnection)

End Sub

</SCRIPT>

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
  <title>Database Scripting</title>
</head>
<body>
<form Runat="Server">

<asp:GridView id="BookGrid" Runat="Server"/>

</form>
</body>
</html>

An SQL statement can be coded inside the Command object (as the CommandString) rather than assigning it to a separate string variable that is referenced inside the command. The above code can be recast as shown below.

Dim DBCommand = New OleDbCommand("SELECT BookID, BookType, BookTitle, " & _
  "BookPrice, BookQty FROM Books ORDER BY BookID", DBConnection)

However, complexities in constructing certain SQL statements make it convenient to construct it separately as a variable and to use the variable reference when creating the OleDbCommand object.

For database display purposes, a SELECT statement returns a set of records from the database and makes it available to the script. You can visualize the returned recordset as a table of rows and columns. There are as many rows as there are records selected from the database table; there are as many columns of values as there are data fields selected from the table.

This returned recordset is assigned to a DataReader object to permit iteration through the rows and access to the columns (fields) of returned data. An OleDbDataReader object is created by using the Command object's ExecuteReader() method in the formats shown in Figure 3-17.

Dim DataReader As OleDbDataReader
DataReader = Command.ExecuteReader()

or

Dim DataReader As OleDbDataReader = Command.ExecuteReader()

or

Dim DataReader = Command.ExecuteReader()

DataReader is a programmer-supplied reference; Command is a reference to the OleDbCommand object previously created to issue the SQL statement.

Continuing with the example script, statements are added to create an OleDbDataReader object representing the collection of database records retrieved by the SQL statement issued through the Command object. The DataReader is assigned the name DBReader in this example.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@ Page Language="vb" Debug="True" %>
<%@ Import Namespace="System.Data.OleDb" %>

<SCRIPT Runat="Server">

Sub Page_Load

  '-- Open a database connection
  Dim DBConnection = New OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb"))
  DBConnection.Open()

  '-- Issue an SQL command through the database connection
  Dim SQLString As String
  SQLString = "SELECT BookID, BookType, BookTitle, BookPrice, BookQty " & _
              "FROM Books ORDER BY BookID"
  Dim DBCommand = New OleDbCommand(SQLString, DBConnection)

  '-- Retrieve a recordset of selected records from the database
  Dim DBReader = DBCommand.ExecuteReader()

End Sub

</SCRIPT>

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
  <title>Database Scripting</title>
</head>
<body>
<form Runat="Server">

<asp:GridView id="BookGrid" Runat="Server"/>

</form>
</body>
</html>

The DataReader is a collection of records extracted from a database. This recordset can be used in one of two ways. As in the case with data source controls, the recordset can be bound to an information display control such as a GridView for automatic display. An alternative is to write code to iterate the records making up the recordset, processing individual records and individual fields through script. Both of these processing methods are described below.

Binding a DataReader to a Control

A DataReader presents a set of records drawn from a data source. In this regard it is no different from a recordset provided through a data source control such as an AccessDataSource. The AccessDataSource simply incorporates the above scripting in the control, creating the Connection and Command objects, issuing the SQL statement, and creating a DataReader behind the scenes. Therefore, the scripted DataReader, like the AccessDataSource, can be bound to a control to display its contents.

Binding a DataReader to an display control is accomplished with the two statements shown in Figure 3-18.

ControlId.DataSource = DataReader
ControlId.DataBind()

ControlId refers to the id assigned to the display control to which the recordset is bound. DataReader refers to the DataReader assigned through the Command object's ExecuteReader() method. First, the DataReader is assigned to the DataSource property of the control; then the control's DataBind() method is applied.

In the current example, the DBReader recordset is bound to an <asp:GridView> control, similar to a previous example with an AccessDataSource. Relevant coding is shown below in the continuing script.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@ Page Language="vb" Debug="True" %>
<%@ Import Namespace="System.Data.OleDb" %>

<SCRIPT Runat="Server">

Sub Page_Load

  '-- Open a database connection
  Dim DBConnection = New OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb"))
  DBConnection.Open()

  '-- Issue an SQL command through the database connection
  Dim SQLString As String
  SQLString = "SELECT BookID, BookType, BookTitle, BookPrice, BookQty " & _
              "FROM Books ORDER BY BookID"
  Dim DBCommand = New OleDbCommand(SQLString, DBConnection)

  '-- Retrieve a recordset of selected records from the database
  Dim DBReader = DBCommand.ExecuteReader()

  '-- Bind data reader to GridView control
  BookGrid.DataSource = DBReader
  BookGrid.DataBind()

End Sub

</SCRIPT>

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
  <title>Database Scripting</title>
</head>
<body>
<form Runat="Server">

<asp:GridView id="BookGrid" Runat="Server"/>

</form>
</body>
</html>

The script and HTML coding shown above produce the following output table. Columns of data are created automatically for each database field retrieved through the SQL statement. Column headings are given by the field names in the database. In this example, no special formatting or styling is applied to the GridView.

As noted, this same output can be produced with an AccessDataSource control bound to a GridView control, avoiding the need for any scripting. When binding a recordset to a display control, this would be the preferred method in most circumstances. Still, you have the option of binding to a control from a scripted DataReader if necessary. A situation might be that you do not know in advance whether database display is needed or which user-selected recordset might be displayed. Therefore, you may not be able to pre-code an AccessDataSource with a known DataFile or SelectCommand. A script may be needed to dynamically create a Connection, Command, and DataReader to populate the output control based on changing user circumstances.

Final housekeeping tasks must be performed before ending a database access script. Both the DataReader and database Connection should be closed using their respective Close() method. These steps are added below to produce the final example page.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@ Page Language="vb" Debug="True" %>
<%@ Import Namespace="System.Data.OleDb" %>

<SCRIPT Runat="Server">

Sub Page_Load

  '-- Open a database connection
  Dim DBConnection = New OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb"))
  DBConnection.Open()

  '-- Issue an SQL command through the database connection
  Dim SQLString As String
  SQLString = "SELECT BookID, BookType, BookTitle, BookPrice, BookQty " & _
              "FROM Books ORDER BY BookID"
  Dim DBCommand = New OleDbCommand(SQLString, DBConnection)

  '-- Retrieve a recordset of selected records from the database
  Dim DBReader = DBCommand.ExecuteReader()

  '-- Bind data reader to GridView control
  BookGrid.DataSource = DBReader
  BookGrid.DataBind()

  '-- Close the data reader and database connection
  DBReader.Close()
  DBConnection.Close()

End Sub

</SCRIPT>

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
  <title>Database Scripting</title>
</head>
<body>
<form Runat="Server">

<asp:GridView id="BookGrid" Runat="Server"/>

</form>
</body>
</html>

Iterating Records of a DataReader

A DataReader represents a collection of records that are made available to a script. You can visualize this collection as a table, similar in structure to the database table from which the information is drawn. Its contents depends on which records and which fields are extracted from the database by the SELECT statement issued against it.

For instance, suppose the following SELECT statement is issued to retrieve information from the Books table of the BooksDB.mdb database.

SELECT BookID, BookType, BookTitle, BookPrice FROM Books 
  WHERE BookType = 'Web'
  ORDER By BookID

The DataReader returned by this statement is in the format shown in Figure 3-20. There are as many rows returned as there are records retrieved; there are as many columns returned as there are data fields named in the SELECT statement.

The returned recordset maintains a pointer (shown in the above table by the » character) that keeps track of the current, active record. When the DataReader is first opened, this pointer is sitting immediate before the first row. By incrementing this pointer through the rows of the table, each record, in turn, becomes accessible to a script.

A DataReader makes a recordset available one record at a time using its Read() method. When the Read() method is called, the DataReader advances the pointer to the next row in the recordset. It returns True if a record is available at that location; it returns False if no record is present. By issuing the Read() method within a Visual Basic While...End While loop, you can increment through the DateReader one record at a time for all records in the recordset. The following outline shows the coding structure for iterating a DataReader.

While DataReader.Read()

  ...process next record in recordset

End While

A DataReader's Read() method supplies, one at a time, a complete data record (an entire row of fields) from the recordset. Normally, the interest is in working with individual data fields within the record. In order to specify a particular data field from a row, the following reference notation is used.

DataReader("FieldName")

FieldName is the name of one of the fields extracted from the database table by the SELECT statement that returns the recordset. For the recordset returned in the previous example, individual data fields are referenced through DBReader("BookID"), DBReader("BookType"), DBReader("BookTitle"), and DBReader("BookPrice"). These references are used inside the While...End While loop to display or process individual data items from one of the recordset rows. These references are shown below in a generalized processing loop for the example DataReader returned from the BooksDB.mdb database.

'-- Read through the recordset one record at a time 
'-- and access individual data fields in that record
While DBReader.Read()
  ...display or process DBReader("BookID")
  ...display or process DBReader("BookType")
  ...display or process DBReader("BookTitle")
  ...display or process DBReader("BookPrice")
End While

Note that a reference to a recordset field—DBReader("BookID"), for instance—is a reference to its associated value, that is, to the data value in that particular row and column. When the pointer is sitting at the first row of the example recordset, the reference DBReader("BookID") returns the value "WB111"; when the pointer increments to the second row, the value returned is "WB222", and so forth. This value can be used for display purposes or for other processing in much the same way as a script variable is used.

Iterating and Displaying a Recordset

As shown above, a script can bind a recordset to an output control for automatic display. An alternative to binding is to write original code for more direct control over a recordset's display.

In the following example, the BooksDB.mdb database provides input to a script for database display. The script produces an XHTML table of information similar to that of a GridView. In this case, however, the script creates its own output table by writing XHTML table tags with embedded recordset values. Notice that all script output is written to a single <asp:Label> control, the only control on the page.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@ Page Language="vb" Debug="True" %>
<%@ Import Namespace="System.Data.OleDb" %>

<SCRIPT Runat="Server">

Sub Page_Load

  '-- Open a database connection
  Dim DBConnection = New OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb"))
  DBConnection.Open()

  '-- Issue an SQL command through the database connection
  Dim SQLString As String
  SQLString = "SELECT BookID, BookType, BookTitle, BookPrice, BookQty " & _
              "FROM Books ORDER BY BookID"
  Dim DBCommand = New OleDbCommand(SQLString, DBConnection)

  '-- Retrieve a recordset of selected records from the database
  Dim DBReader As OleDbDataReader = DBCommand.ExecuteReader()

  '-- Display table header
  Output.Text  = "<table border=""1"">"
  Output.Text &= "<caption><b>Book Listing</b></caption>"
  Output.Text &= "<tr style=""background-color:#F0F0F0"">"
  Output.Text &= "<th>ID</th>"
  Output.Text &= "<th>Type</th>"
  Output.Text &= "<th>Title</th>"
  Output.Text &= "<th>Price</th>"
  Output.Text &= "<th>Qty.</th>"
  Output.Text &= "</tr>"

  '-- Read through the recordset and display table rows
  While DBReader.Read()
    Output.Text &= "<tr>"
    Output.Text &= "<td>" & DBReader("BookID") & "</td>"
    Output.Text &= "<td>" & DBReader("BookType") & "</td>"
    Output.Text &= "<td>" & DBReader("BookTitle") & "</td>"
    Output.Text &= "<td>" & DBReader("BookPrice") & "</td>"
    Output.Text &= "<td>" & DBReader("BookQty") & "</td>"
    Output.Text &= "</tr>"
  End While

  '-- Close the table
  Output.Text &= "</table>"

  '-- Close the data reader and database connection
  DBReader.Close()
  DBConnection.Close()

End Sub

</SCRIPT>

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
  <title>Scripted Database Display</title>
</head>
<body>
<form Runat="Server">

<asp:Label id="Output" Runat="Server"/>

</form>
</body>
</html>

The script is coded in the Page_Load subprogram to display the table when the page opens. Output produced by this page is shown below.

Although there is quite a bit of code, it is fairly easy to understand. After the database is opened, but prior to reading through the recordset, XHTML tags for the table header lines are concatenated and assigned to the Text property of the Label. The process begins for building a complete XHTML table in the Label control.

'-- Display table header
Output.Text  = "<table border=""1"">"
Output.Text &= "<caption><b>Book Listing</b></caption>"
Output.Text &= "<tr style=""background-color:#F0F0F0"">"
Output.Text &= "<th>ID</th>"
Output.Text &= "<th>Type</th>"
Output.Text &= "<th>Title</th>"
Output.Text &= "<th>Price</th>"
Output.Text &= "<th>Qty.</th>"
Output.Text &= "</tr>"

Next, a While...End While loop is set up to iterate all records in the recordset. Each record produces a table row; therefore, the code appends XHTML tags for the row to the output Label, interspersing them with references to recordset fields appearing on that row.

'-- Read through the recordset and display table rows
While DBReader.Read()
  Output.Text &= "<tr>"
  Output.Text &= "<td>" & DBReader("BookID") & "</td>"
  Output.Text &= "<td>" & DBReader("BookType") & "</td>"
  Output.Text &= "<td>" & DBReader("BookTitle") & "</td>"
  Output.Text &= "<td>" & DBReader("BookPrice") & "</td>"
  Output.Text &= "<td>" & DBReader("BookQty") & "</td>"
  Output.Text &= "</tr>"
End While

Notice, for example, that the BookID value appears in the first column of the output table. This value needs to appear between the first set of <td> and </td> tags in the table row. So the string "<td>" is concatenated with the field DBReader("BookID") with the string "</td>" to produce this table cell.

Each time through the loop the next table row is formattted with information from the next record. After all rows have been concatenated to the Label control, the closing </table> tag is written and the DataReader and Connection are closed. A complete XHTML table with embedded database information has been assigned to the Label and appears on the page.

'-- Close the table
Output.Text &= "</table>"

'-- Close the data reader and database connection
DBReader.Close()
DBConnection.Close()

Notice that some of the XHTML strings assigned to the Label, which themselves are enclosed in quotes, include quoted attributes. The following <table> tag is a case in point with its border="1" attribute.

Output.Text = "<table border=""1"">"

When quoted values appear inside quoted strings, they must be enclosed in pairs of quotes to display properly (border=""1""). An alternative is to use single quotes (') to enclose quoted values inside quoted strings.

Output.Text = "<table border='1'>"

Single quotes, however, cannot be used to enclose the quoted string itself. ASP.NET scripting only permits double quotes surrounding outer text strings, with either pairs of double quotes or single quotes inside the strings.

Returning a Single Record

The previous script works for any number of returned records. If no records are returned, then the While...End While loop does not get executed and an empty table is presented; if one or more records are returned, these are displayed as one or more table rows.

For certain applications, though, the expectation might be for return of only a single record. This would be the case if the SQL statement contains a WHERE clause to look for a single matching record, for instance, "SELECT * FROM Books WHERE BookID = 'WB111'".

In the case of a single returned record it is not necessary, of course, to iterate the recordset. You can just read the single record with the DataReader's Read() method.

Dim DBConnection = New OleDbConnection( _
  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb"))
DBConnection.Open()
Dim SQLString As String = "SELECT * FROM Books WHERE BookID = 'WB111'"
Dim DBCommand = New OleDbCommand(SQLString, DBConnection)
Dim DBReader As OleDbDataReader = DBCommand.ExecuteReader()
DBReader.Read()
...process this single record
DBReader.Close()
DBConnection.Close()

The above example shows an SQL statement with a matching value explicitly coded (WHERE BookID = 'WB111'). It is more likely that the statement is issued to find a matching value supplied by the user in some type of database search. For example, the page might supply a TextBox in which the user enters a search value, and only the matching record is displayed.

Assuming the id of this TextBox is SearchID, the SQL statement concatenates the value from the TextBox inside the SQL string to locate the single record with the matching BookID.

Dim SQLString As String
SQLString = "SELECT BookTitle, BookAuthor, BookDescription, BookPrice " & _
            "FROM Books WHERE BookID = '" & SearchID.Text & "'"

Testing for a Returned Recordset

When relying upon users to enter search criteria it can easily result that entered values are not matched in the database, either because of a data entry error or because there is no matching record. When attempting to display DataReader("fieldname") values when no record is returned in the recordset, a script error results. Since you do not wish to risk script errors appearing on your pages, you need to test for the existence of a returned record prior to displaying it.

You can easily test whether a record is returned from an SQL statement with the DataReader's HasRows property. The following code tests for an existing record as the condition for displaying its data fields. If no record is returned, a "not found" message is displayed rather than an embarassing script error. This technique is used in the above example to trap missing or incorrectly entered BookID values.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@ Page Language="vb" Debug="True" %>
<%@ Import Namespace="System.Data.OleDb" %>

<SCRIPT Runat="Server">

Sub Find_Record (Src As Object, Args As EventArgs)

  Dim DBConnection = New OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb"))
  DBConnection.Open()
  Dim SQLString As String
  SQLString = "SELECT BookTitle, BookAuthor, BookDescription, BookPrice" & _
              " FROM Books WHERE BookID = '" & SearchID.Text & "'"
  Dim DBCommand = New OleDbCommand(SQLString, DBConnection)
  Dim DBReader As OleDbDataReader = DBCommand.ExecuteReader()
  If DBReader.HasRows Then
    DBReader.Read()
    SearchOutput.Text  = "<style type=""text/css"">"
    SearchOutput.Text &= "table#SearchTable th"
    SearchOutput.Text &= "  {font-weight:bold; text-align:left; "
    SearchOutput.Text &= "  background-color:#E0E0E0}"
    SearchOutput.Text &= "table#SearchTable td"
    SearchOutput.Text &= "  {font-family:times new roman; font-size:11pt}"
    SearchOutput.Text &= "table#SearchTable tr"
    SearchOutput.Text &= "  {vertical-align:top}"
    SearchOutput.Text &= "</style>"

    SearchOutput.Text &= "<table id=""SearchTable"" cellpadding=""1"">"
    SearchOutput.Text &= "<tr>"
    SearchOutput.Text &= "  <th>Title</th>"
    SearchOutput.Text &= "  <td>" & DBReader("BookTitle") & "</td>"
    SearchOutput.Text &= "</tr>"
    SearchOutput.Text &= "<tr>"
    SearchOutput.Text &= "  <th>Author</th>"
    SearchOutput.Text &= "  <td>" & DBReader("BookAuthor") & "</td>"
    SearchOutput.Text &= "</tr>"
    SearchOutput.Text &= "<tr>"
    SearchOutput.Text &= "  <th>Price</th>"
    SearchOutput.Text &= "  <td>" & DBReader("BookPrice") & "</td>"
    SearchOutput.Text &= "</tr>"
    SearchOutput.Text &= "<tr>"
    SearchOutput.Text &= "  <th>Description</th>"
    SearchOutput.Text &= "  <td>" & DBReader("BookDescription") & "</td>"
    SearchOutput.Text &= "</tr>"
    SearchOutput.Text &= "</table>"
  Else
    SearchOutput.Text = "No matching record"
  End If
  DBReader.Close()
  DBConnection.Close()
	
End Sub

</SCRIPT>

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
  <title>Scripted Database Search</title>
</head>
<body>
<form Runat="Server">

<h3>Book Search</h3>

Enter Book ID: 
<asp:TextBox id="SearchID" Width="70" Text="WB111" Runat="Server"/>
<asp:Button Text="Search" OnClick="Find_Record" Runat="Server"/>
<p><asp:Label id="SearchOutput" Runat="Server"/></p>

</form>
</body>
</html>

The script writes an XHTML table to the output Label, embedding data values from the retrieved record. If no record is returned, a not-found message is displayed in its place. Notice in this script that an embedded CSS style sheet is output to the page along with the table to which its styles apply.

The above scripts are a few examples of accessing an external data source, processing the returned data, and producing an output display. There are many of these and similar examples presented throughout the tutorials as well as variations in using data source controls for information processing and display.