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

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

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

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 your 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 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:

   using System.Data.OleDb;

6. 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.
   	cn.Open();
   	
   	//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;
   	tableHeading.Cells.Add(customerIDHeading);
   			
   	//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;
   	tableHeading.Cells.Add(contactNameHeading);
   			
   	//Create and add the cells that contain the Phone column heading text.
   	TableHeaderCell phoneHeading = 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())
   	{	
   		TableRow detailsRow = new TableRow();
   		TableCell customerIDCell = new TableCell();
   		customerIDCell.Text = reader["CustomerID"].ToString();
   		detailsRow.Cells.Add(customerIDCell);
   
   		TableCell contactNameCell = new TableCell();
   		contactNameCell.Text = reader["ContactName"].ToString();
   		detailsRow.Cells.Add(contactNameCell);
   
   		TableCell phoneCell = new TableCell();
   		phoneCell.Text = reader["Phone"].ToString();
   		detailsRow.Cells.Add(phoneCell);
   
   		DisplayTable.Rows.Add(detailsRow);
   
   	}
   
            //Close the reader and the related connection.
   	reader.Close();
   	cn.Close();
   }
   

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.