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
Create an ASP.NET Web
application by using Visual C# .NET
Start Microsoft Visual Studio .NET.
On the File menu, point to New, and then click Project.
In the New Project dialog box, click Visual C# Projects under Project Types, and then click ASP.NET Web Application under Templates.
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
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
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
From the Web Forms toolbox, drag a Table control to the .aspx page in design view.
In Properties, change the ID to DisplayTable.
In Solution Explorer, right-click the .aspx page, and
then click View Code.
Add the following namespace reference to the top
of the code-behind class file:
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 = "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.
OleDbConnection cn = new OleDbConnection(connectString);
//Open the connection.
//Use a variable to hold the SQL statement.
string selectString = "SELECT CustomerID, ContactName, Phone FROM Customers";
//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 = "Customer ID";
customerIDHeading.HorizontalAlign = HorizontalAlign.Left;
//Create and add the cells that contain the Contact Name column heading text.
TableHeaderCell contactNameHeading = new TableHeaderCell();
contactNameHeading.Text = "Contact Name";
contactNameHeading.HorizontalAlign = HorizontalAlign.Left;
//Create and add the cells that contain the Phone column heading text.
TableHeaderCell phoneHeading = new TableHeaderCell();
phoneHeading.Text = "Phone";
phoneHeading.HorizontalAlign = HorizontalAlign.Left;
//Loop through the resultant data selection and add the data value
//for each respective column in the table.
TableRow detailsRow = new TableRow();
TableCell customerIDCell = new TableCell();
customerIDCell.Text = reader["CustomerID"].ToString();
TableCell contactNameCell = new TableCell();
contactNameCell.Text = reader["ContactName"].ToString();
TableCell phoneCell = new TableCell();
phoneCell.Text = reader["Phone"].ToString();
//Close the reader and the related connection.
Modify the connectString variable at the beginning of the code to point to the
location of your Northwind database.
On the File menu, click Save All to save the Web Form and other, associated project files.
On the Build menu, click Build Solution to build the project.
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
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.
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.