How To Retrieve and Display Records from an Access Database by Using ASP.NET, ADO.NET, and Visual Basic .NET

Requirements

• Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows Server 2003
• Microsoft Internet Information Server (IIS)
• Microsoft .NET Framework 1.0 or Microsoft .NET Framework 1.1
• Microsoft Access Northwind Database

Create an ASP.NET Web Application by Using Visual Basic .NET

1. Start Microsoft Visual Studio .NET.
2. On the File menu, point to New, and then click Project.
3. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Application under Templates.
4. In the Location text box, replace the WebApplication# default name with AccessDemo. If you are using the local server, you can leave the server name set to http://localhost. The resulting Location text box appears as follows: http://localhost/AccessDemo

Create the Sample Web Form

1. Add a new Web Form named DataSample.aspx to your ASP.NET Web application in Visual Studio .NET. To do this, follow these steps:
   
   
   1. In Solution Explorer, right-click the project node, click Add, and then click Add Web Form.
   2. In the Name text box, type DataSample.aspx, and then click Open.
2. From the Web Forms toolbox, drag a Table ASP.NET server control to the .aspx page in design view.
3. In Properties, change the ID to DisplayTable.
4. In Solution Explorer, right-click the .aspx page, and then click View Code.
5. Add the following namespace reference to the top of the code-behind class file:

   Imports System.Data.OleDb

6. Replace the Page_Load event handler with the following code:

   Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       'Use a string variable to hold the ConnectionString.
       Dim connectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                   "Data Source=C:\File Databases\NWIND.MDB"
   
       'Create an OleDbConnection object,
       'and then pass in the ConnectionString to the constructor.
       Dim cn As OleDbConnection = New OleDbConnection(connectString)
   
       'Open the connection.
       cn.Open()
   
       'Use a variable to hold the SQL statement.
       Dim selectString As String = "SELECT CustomerID, ContactName, Phone FROM Customers"
   
       'Create an OleDbCommand object.
       'Notice that this line passes in the SQL statement and the OleDbConnection object.
       Dim cmd As OleDbCommand = New OleDbCommand(selectString, cn)
   
       'Send the CommandText to the connection, and then build an OleDbDataReader.
       'Note: The OleDbDataReader is forward-only.
       Dim reader As OleDbDataReader = cmd.ExecuteReader()
   
       'Set the table width.
       DisplayTable.Width = Unit.Percentage(90.0)
       'Create a new row for adding a table heading
       Dim tableHeading As TableRow = New TableRow()
   
       'Create and add the cells that contain the Customer ID column heading text.
       Dim customerIDHeading As TableHeaderCell = New TableHeaderCell()
       customerIDHeading.Text = "Customer ID"
       customerIDHeading.HorizontalAlign = HorizontalAlign.Left
       tableHeading.Cells.Add(customerIDHeading)
   
       'Create and add the cells that contain the Contact Name column heading text.
       Dim contactNameHeading As TableHeaderCell = New TableHeaderCell()
       contactNameHeading.Text = "Contact Name"
       contactNameHeading.HorizontalAlign = HorizontalAlign.Left
       tableHeading.Cells.Add(contactNameHeading)
   
       'Create and add the cells that contain the Phone column heading text.
       Dim phoneHeading As TableHeaderCell = New TableHeaderCell()
       phoneHeading.Text = "Phone"
       phoneHeading.HorizontalAlign = HorizontalAlign.Left
       tableHeading.Cells.Add(phoneHeading)
   
       DisplayTable.Rows.Add(tableHeading)
   
       'Loop through the resultant data selection and add the data value
       'for each respective column in the table.
       While(reader.Read())
   
           Dim detailsRow As TableRow = New TableRow()
           Dim customerIDCell As TableCell = New TableCell()
           customerIDCell.Text = reader("CustomerID").ToString()
           detailsRow.Cells.Add(customerIDCell)
   
           Dim contactNameCell As TableCell = New TableCell()
           contactNameCell.Text = reader("ContactName").ToString()
           detailsRow.Cells.Add(contactNameCell)
   
           Dim phoneCell As TableCell = New TableCell()
           phoneCell.Text = reader("Phone").ToString()
           detailsRow.Cells.Add(phoneCell)
   
           'Add the new row to the table.
           DisplayTable.Rows.Add(detailsRow)
   
       End While
   
       'Close the reader and the related connection.
       reader.Close()
       cn.Close()
   End Sub
   
   

7. Modify the connectString variable at the beginning of the code to point to the location of your Northwind database.
8. On the File menu, click Save All to save the Web Form and other, associated project files.
9. On the Build menu, click Build Solution to build the project.
10. In Solution Explorer, right-click DataSample.aspx, and then click View in Browser. The page appears in the browser and contains the data from the Northwind database.

Troubleshoot

• At run time, you may receive the following error message (or similar):
  The Microsoft Jet database engine cannot open the file 'C:\File Databases\NWIND.MDB'. It is already opened exclusively by another user, or you need permission to view its data.
  Frequently, this error occurs because you do not have correct permissions to gain access to the database (.mdb) file. By default, ASP.NET runs under the ASPNET account in the .NET Framework 1.0, and under NetworkService in the .NET Framework 1.1. You must have modify permissions on the .mdb file and the folder where the file is located. For more information, see the REFERENCES section.
• Make sure that the components for the Microsoft Jet database engine are installed. Beginning with Microsoft Data Access Components 2.6 (MDAC), the Jet components are not included. However, you can download a redistributable version of the latest Jet 4.0 Service Pack.