Microsoft KB Archive/308100

= How to retrieve and display records from an Access database by using ASP.NET, ADO.NET, and Visual C# .NET =

Article ID: 308100

Article Last Modified on 5/2/2006

-

APPLIES TO


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

-



This article was previously published under Q308100





For a Microsoft Visual Basic .NET version of this article, see 308278.

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 C# .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 C# .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, or Windows 2000 Advanced Server, or Windows Server 2003
 * Microsoft Internet Information Services (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 C# .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 C# 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 your 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 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: using System.Data.OleDb; </li>  Replace the Page_Load event handler with the following code: private void Page_Load(object sender, System.EventArgs e) { //Use a string variable to hold the ConnectionString. string connectString = &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. OleDbConnection cn = new OleDbConnection(connectString); //Open the connection. cn.Open; //Use a variable to hold the SQL statement. string selectString = &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 OleDbCommand cmd = new OleDbCommand(selectString,cn);

//Send the CommandText to the connection, and then build an OleDbDataReader. //Note: The OleDbDataReader is forward-only. OleDbDataReader reader = cmd.ExecuteReader;

//Set a table width. DisplayTable.Width = Unit.Percentage(90.00); //Create a new row for adding a table heading. TableRow tableHeading = new TableRow;

//Create and add the cells that contain the Customer ID column heading text. TableHeaderCell customerIDHeading = 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. TableHeaderCell contactNameHeading = 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. TableHeaderCell phoneHeading = 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) {          TableRow detailsRow = new TableRow; TableCell customerIDCell = new TableCell; customerIDCell.Text = reader[&quot;CustomerID&quot;].ToString; detailsRow.Cells.Add(customerIDCell);

TableCell contactNameCell = new TableCell; contactNameCell.Text = reader[&quot;ContactName&quot;].ToString; detailsRow.Cells.Add(contactNameCell);

TableCell phoneCell = new TableCell; phoneCell.Text = reader[&quot;Phone&quot;].ToString; detailsRow.Cells.Add(phoneCell);

DisplayTable.Rows.Add(detailsRow);

}

//Close the reader and the related connection. reader.Close; cn.Close; } </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">