Microsoft KB Archive/308278

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

Article ID: 308278

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft ADO.NET 1.1
 * Microsoft ADO.NET 1.0
 * Microsoft ASP.NET 1.1
 * Microsoft ASP.NET 1.0
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic .NET 2002 Standard Edition

-



This article was previously published under Q308278





For a Microsoft Visual C# .NET version of this article, see 308100.

This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Data
 * System.Data.OleDb

IN THIS TASK

 * SUMMARY
 * Requirements
 * Create an ASP.NET Web Application by Using Visual Basic .NET
 * Create the Sample Web Form
 * Troubleshoot
 * REFERENCES



SUMMARY
This step-by-step article describes how to programmatically retrieve data from a Microsoft Access database, and then present the data to the user. The goal of this article is not to provide every conceivable approach to the issue. It is intended to offer a simplified solution that uses ASP.NET, ADO.NET, and Visual Basic .NET as an introduction to the related technologies.

The sample in this article uses the OleDbConnection, OleDbCommand, and OleDbDataReader classes. These classes are part of ADO.NET and the Microsoft .NET Framework. The data is presented by using the Table ASP.NET server control.

For additional resources about related approaches, see the REFERENCES section.

back to the top



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

back to the top



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

back to the top



Create the Sample Web Form
The sample code in this section uses a Table ASP.NET server control to dynamically build a simplified presentation of the retrieved data. ASP.NET offers a variety of flexible controls that you can use to provide alternate approaches for rendering the data. For additional information about the controls that ASP.NET supports, see the REFERENCES section at the end of this article.  Add a new Web Form named DataSample.aspx to your ASP.NET Web application in Visual Studio .NET. To do this, follow these steps:  In Solution Explorer, right-click the project node, click Add, and then click Add Web Form. In the Name text box, type DataSample.aspx, and then click Open.  From the Web Forms toolbox, drag a Table ASP.NET server control to the .aspx page in design view.</li> In Properties, change the ID to DisplayTable.</li> In Solution Explorer, right-click the .aspx page, and then click View Code.</li>  Add the following namespace reference to the top of the code-behind class file: Imports System.Data.OleDb </li>  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 = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=C:\File Databases\NWIND.MDB&quot;

'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 = &quot;SELECT CustomerID, ContactName, Phone FROM Customers&quot;

'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 = &quot;Customer ID&quot; 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 = &quot;Contact Name&quot; 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 = &quot;Phone&quot; 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(&quot;CustomerID&quot;).ToString detailsRow.Cells.Add(customerIDCell)

Dim contactNameCell As TableCell = New TableCell contactNameCell.Text = reader(&quot;ContactName&quot;).ToString detailsRow.Cells.Add(contactNameCell)

Dim phoneCell As TableCell = New TableCell phoneCell.Text = reader(&quot;Phone&quot;).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 </li> Modify the connectString variable at the beginning of the code to point to the location of your Northwind database.</li> On the File menu, click Save All to save the Web Form and other, associated project files.</li> On the Build menu, click Build Solution to build the project.</li> 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.</li></ol>

back to the top

<div class="moreinformation_section">

Troubleshoot
<ul> 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.</li> 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.</li></ul>

back to the top

<div class="references_section">