Using ASP.NET with an MS Access Database

If you're planning to build a dynamic web site using Microsoft-based technologies, MS Access and ASP.NET go together very well. This article will get you started with the basics.

Contributed by Codex-M
Rating:

/ 11
December 16, 2009

Rate this Article:
MEH MEH++

SEARCH ASP FREE

Creating your ASP.NET website

In this step, you need to open your Visual Web Developer 2008. Under "Recent Projects," create "Website" and follow the rest of the steps:

1. Under Visual Studio installed templates, click "ASP.NET Web site."

2. Under "Location," select "File System."

3. In "Language," choose "Visual Basic."

4. Select the path where you would like your website files to be saved. In this example I chose to save them in Drive E of my Windows computer, using "samplewebsite" as the directory name. See the screenshot below illustrating the above steps:

5. After clicking "OK," Windows automatically creates the folder name "samplewebsite" in drive E.

6. Copy the "Northwind.mdb" to this folder. After copying the file, it should like the one below (along with other ASP.NET files):

7. At this point your ASP.NET website is still not connected to MS Access; you need to edit your entire Default.aspx script to connect to an MS Access database.

ASP.Net based scripts to connect to MS Access

The script below will connect to the Northwind.mdb database and then output all of the data from the "Order details" table.

To implement this, delete ALL of your current Default.aspx's code (generated from the previous section using Visual Web Developer), and replace it with the code below:

'First import name space called system.data.OleDB,this is a required ADO.NET API for accessing MS Access database

<%@ Import Namespace="System.Data.OleDb" %>

<% 'Second, connect to MS Access database %>

sub Page_Load

dim msaccessconnect,query,databasecommunication,readdatabase

msaccessconnect = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("northwind.mdb"))

msaccessconnect.Open()

query = "SELECT * FROM `Order Details`"

databasecommunication=New OleDbCommand(query,msaccessconnect)

readdatabase=databasecommunication.ExecuteReader()

Orderdetails.DataSource = readdatabase

Orderdetails.DataBind()

readdatabase.Close()

msaccessconnect.Close()

end sub

</script>

<html>

<body>

<% 'Third, display the database output to HTML using tables %>

<asp:Repeater id="Orderdetails" runat="server">

<table>

<tr>

<th>Order ID</th>

<th>Product ID</th>

<th>Unit Price</th>

<th>Quantity</th>

<th>Discount</th>

</tr>

</HeaderTemplate>

<tr>

</tr>

</ItemTemplate>

</table>

</FooterTemplate>

</asp:Repeater>

</form>

</body>

</html>

One of the tricky parts in the above script is the following:

1. Declaration of correct field name under <%#Container.DataItem("Thisisafieldname")%>

If this is not correct, ASP.NET won't output the database table to the browser (an error will be shown instead). To make sure you have used the correct field name, open MS Access, and then open the Northwind.mdb. When you see the "Northwind: Database (Access 2000 file format)" window, click "Order details" and then click Design. The field names of the table will be shown. You should be using these in the Container.DataItem field

See the screenshot below. The "Order Details" table is selected first; clicking the "Design" shows the list of correct field names used by that table:

2. Declaration of correct database query

If you are querying a two-word database table name, like Order Details, you should placed it within back ticks, as shown in the example query below:

query = "SELECT * FROM `Order Details`"

3. Simplification of database table names used in ASP.NET procedures.

For example, since the database table name is more than one word, it is suggested to make it one word to simplify database table names used in ASP.NET (in bold)

Example 1:

Orderdetails.DataSource = readdatabase

Orderdetails.DataBind()

Example 2:

<asp:Repeater id="Orderdetails" runat="server">

After pasting the code above, go to File -> Save all. To view the output in the browser, go to File -> View in Browser

The ASP.NET local host server automatically starts up and displays the entire content of the database table in an HTML browser:

Adjusting database queries

The previous page illustrates a very basic but important aspect of retrieving records from an MS Access database and displaying them in a browser using ASP.NET. You can modify queries and even how the HTML output will be displayed on the browser.

For example, below is the query that will display the Top 10 records of the MS Access database:

query = "SELECT top 10 * from(SELECT * from `Order Details`)"

In addition, if you would like to retrieve all entries with a Product ID less than 20, you can revise the query so it reads like this:

query = "SELECT * FROM `Order Details` WHERE `ProductID`< 20"

There are endless possibilities for how you can formulate the queries, as well as how the information will be displayed in the browser. The important thing is that you know the basics before attempting to make things too complex. Note that you need to look for a web hosting company that supports ASP.NET with MS Access to implement your website powered with an MS Access database.

Warning: if you are able to use web forms (which means accepting user inputs) or variable queries visible in the URL, it is highly recommended that you take precautionary measures against SQL injection.

Read this official Microsoft tutorial or check out some other references that explain how to connect an MS Access database in ASP.NET.