Create ASP.NET Web Forms to Use a Microsoft Access Database

This tutorial will guide you through making ASP.NET web forms that query an MS Access database, retrieve records and display them on the browser. This dynamic use of ASP.NET can have enormous importance for web applications that depend on an MS Access database.

Overview

You should have read the introductory tutorial on ASP.NET connecting to an MS Access database. It provides important information on the connection strings that the ASP.NET platform will be using to connect to MS Access.

The demo database uses the Northwind Database, which is a sample/demo database provided by MS Access. Of course, if you do not have MS Access installed on your Windows computer, then you cannot use the Northwind test database (or get Northwind database from other sources).

To maximize the use of this tutorial, it is highly recommended that you have the following system components fully installed on your computer (for test/development purposes only):

  • Windows computer with the Windows XP operating system or better.

  • MS Access installed with the Northwind sample database.

  • Visual Web Developer 2008 Express Edition (read some tutorials for installation details).

Once you have completed developing your ASP.NET web form application that uses an MS Access database, you can then upload your files to a hosting environment that supports the following features:

  • Windows-based server/IIS.

  • ASP.NET 3.5

  • Supports the MS Access database – read some tips for how to locate the “Northwind” database in your existing MS Access installation. 

With all of the above in place, you can share your wonderful web applications with Internet users.

{mospagebreak title=Form design and programming strategy}

Let’s have an example. Say you will develop a web form that looks like this:

 

The Northwind database file (Northwind.mdb) contains a table named “Order Details” which has the following design/fields:

There are five fields available. Say you will need to pull records from this table for all products where ProductID < QueryID.

The first thing you will need to know is how ASP.NET will connect to the existing MS Access database. You can modify the source code discussed in this tutorial since it already contains the connection parameters. All you need to do is add web controls like Textbox and Buttons, which will be web form elements, and write some short server side script to get the Product ID user inputs and do the MS Access Query.

In the ASP.NET-MS Access connection tutorial, there are already form elements that will display the records to the browser (shown below):

<form id="Form1" runat="server">

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

<HeaderTemplate>

<table>

<tr>

<th>Order ID</th>

<th>Product ID</th>

<th>Unit Price</th>

<th>Quantity</th>

<th>Discount</th>

</tr>

</HeaderTemplate>

<ItemTemplate>

<tr>

<td><%#Container.DataItem("OrderID")%></td>

<td><%#Container.DataItem("ProductID")%></td>

<td><%#Container.DataItem("UnitPrice")%></td>

<td><%#Container.DataItem("Quantity")%></td>

<td><%#Container.DataItem("Discount")%></td>

</tr>

</ItemTemplate>

<FooterTemplate>

</table>

</FooterTemplate>

</asp:Repeater>

</form>

 

Since ASP.NET does not allow you to add more than one server side web form control (ERROR: A page can have only one server-side Form tag.), you will need to edit the above existing form to add controls like Textbox to accept user inputs, and Buttons for submitting the values to the server.

You can add a server side text box and button (for the web form) using Visual Web Developer by going to:

ASP.NET textbox:

View à Toolbox à Standard à Textbox

 

ASP.NET button:

 

View à Toolbox à Standard à Button

 

In the source tab, once the toolbox is open, you can click and drag the controls to the appropriate location in the source code. Below is the modified web form source code, which now contains the text box and button controls. (The newly-added controls are in bold).

 

<form id="Form1" runat="server">

Enter Product ID:

<asp:TextBox ID="getid" runat="server"></asp:TextBox>

<br />

<br />

<asp:Button ID="displayrecords" runat="server" Text="Display Records" />

<br />

<br />

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

<HeaderTemplate>

<table>

<tr>

<th>Order ID</th>

<th>Product ID</th>

<th>Unit Price</th>

<th>Quantity</th>

<th>Discount</th>

</tr>

</HeaderTemplate>

<ItemTemplate>

<tr>

<td><%#Container.DataItem("OrderID")%></td>

<td><%#Container.DataItem("ProductID")%></td>

<td><%#Container.DataItem("UnitPrice")%></td>

<td><%#Container.DataItem("Quantity")%></td>

<td><%#Container.DataItem("Discount")%></td>

</tr>

</ItemTemplate>

<FooterTemplate>

</table>

</FooterTemplate>

</asp:Repeater>

</form>

{mospagebreak title=Server side scripting strategy}

The server side script does the main job of connecting to the MS Access database, getting user inputs from the web form, sending the query to the MS Access database and then retrieving the records provided by the MS Access database (and finally displaying them back to the browser).

The original Visual Basic script does not contain the features to get user inputs from the web forms; nor does it contain the correct MS Access database query intended for this application (see the first screen shot):

<script runat="server">

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>

The same is true of the web form strategy on the previous section. You can improve the script above by adding the features mentioned earlier. In the above form, since the ID for Textbox form is “getid,” the Visual Basic script to get the user input from the web form is:

Dim userid As Integer = getid.Text

The value of getid will be assigned to a “userid” variable. Since you need ASP.NET to  display all the records to the browser with product IDs less than the one entered by the user (using Northwind Database Order Details table), you will need to edit the query: SELECT * FROM `Order Details

 

The correct query to MS Access should be:

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

Note that  Visual Basic uses the concatenation “&” character, so the above query displays all records with product IDs less than the userid variable (the userid is the user inputted ID from the web form).

Therefore the modified server side code is:

<script runat="server">

    Sub displayrecords_Click(ByVal Source As Object, ByVal e As EventArgs) Handles displayrecords.Click

        Dim msaccessconnect, query, databasecommunication, readdatabase As Object

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

        msaccessconnect.Open()

        Dim userid As Integer = getid.Text

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

        databasecommunication = New OleDbCommand(query, msaccessconnect)

        readdatabase = databasecommunication.ExecuteReader()

        Orderdetails.DataSource = readdatabase

        Orderdetails.DataBind()

        readdatabase.Close()

        msaccessconnect.Close()

    End Sub

</script>

 

You can read a discussion of the MS Access connection parameters

{mospagebreak title=Implementation strategy}

First you need to create a blank ASP.NET project in the Visual Web Developer Express dashboard (Create à Website à then select “ASP.NET Website” under Visual Studio installed templates). Set location à File system, Language à Visual Basic and then type a foldername as a path in your Windows computer, e.g:

E:aspnetaccess

After clicking OK, Visual Web Developer automatically creates the file system, and then deletes all of the Default.aspx code. Copy and paste the code below (this is the complete form and server side code discussed in the previous section):

 <head>

    <style type="text/css">

        #Form1

        {

            font-family: Verdana;

            font-size: small;

            margin-top: 15px;

        }

        .style1

        {

            font-family: Verdana;

        }

        .style2

        {

            font-family: Verdana;

            font-size: medium;

        }

        .style3

        {

            font-family: Verdana;

            font-size: small;

            width: 569px;

        }

    </style>

</head>

<%

    ‘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 %>

<script runat="server">

    Sub displayrecords_Click(ByVal Source As Object, ByVal e As EventArgs) Handles displayrecords.Click

        Dim msaccessconnect, query, databasecommunication, readdatabase As Object

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

        msaccessconnect.Open()

        Dim userid As Integer = getid.Text

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

        databasecommunication = New OleDbCommand(query, msaccessconnect)

        readdatabase = databasecommunication.ExecuteReader()

        Orderdetails.DataSource = readdatabase

        Orderdetails.DataBind()

        readdatabase.Close()

        msaccessconnect.Close()

    End Sub

</script>

<html>

<body>

    <h3 class="style1">

        ASP.NET webform that uses MS Access database</h3>

    <p class="style2">

        Application Description:

    </p>

    <p class="style3">

        This form will asked the Product ID from the user and then ASP.NET will display

        all records with product ID less than the one entered by the user using

        Northwind Database Order Details table.</p>

<form id="Form1" runat="server">

Enter Product ID:

<asp:TextBox ID="getid" runat="server"></asp:TextBox>

<br />

<br />

<asp:Button ID="displayrecords" runat="server" Text="Display Records" />

<br />

<br />

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

<HeaderTemplate>

<table>

<tr>

<th>Order ID</th>

<th>Product ID</th>

<th>Unit Price</th>

<th>Quantity</th>

<th>Discount</th>

</tr>

</HeaderTemplate>

<ItemTemplate>

<tr>

<td><%#Container.DataItem("OrderID")%></td>

<td><%#Container.DataItem("ProductID")%></td>

<td><%#Container.DataItem("UnitPrice")%></td>

<td><%#Container.DataItem("Quantity")%></td>

<td><%#Container.DataItem("Discount")%></td>

</tr>

</ItemTemplate>

<FooterTemplate>

</table>

</FooterTemplate>

</asp:Repeater>

</form>

&nbsp;

</body>

</html>

 

You also need to copy and paste the Northwind.mdb database inside the file system folder. For example, see the screen shot below (encircled in red):

 

When you view the web application in the browser and enter a test Product ID, such as 23, after clicking “Display Records,” the form result should look like the screen shot below:

You will notice that all Product IDs shown (in the second column) are less than 23. You can use the basic concept illustrated in this article to create more complex ASP.NET web applications using MS Access.

One thought on “Create ASP.NET Web Forms to Use a Microsoft Access Database

[gp-comments width="770" linklove="off" ]