Connect to Access Database with ASP.Net

This tutorial illustrates different methods that you can use to connect to an MS Access database using ASP.NET and display the results in a web browser. Complete downloadable source code will be available at the end of this tutorial for your reference.

Connecting directly from an ASP.NET page (.aspx) – Direct File

With this method, we will suppose you have an ASP.NET page (with an .aspx extension), and you want to place some connection scripts (either using VB.NET or C#) into this page in order to connect to an MS Access database. This example does not use any code behind the scenes (such as Default.aspx.vb or Default.aspx.cs). Here is an example of how you would accomplish this:

Step 1: You need a sample test MS Access database. For this tutorial, let’s use the sample database included in Microsoft Access called, “Northwind.mdb”. You can download it here: http://www.microsoft.com/download/en/details.aspx?id=19704

Run the .exe file and install/extract the database to your desktop.

Step 2: Launch Visual Web Developer/ Visual Studio. Step 3: Go to File – New Website. Step 4: Select ASP.NET website under Visual Studio Installed Templates.

Assign a path and name to your project (e.g. L:aspdotnetprojectsmsaccessdirect), then make sure you select “File system” under Location. Under “Language”, you can choose either Visual Basic or Visual C#, depending on which language you are using.

Step 5: Copy the Northwind.mdb database to the App_Data directory in your website project.

Step 6: Supposing you want to output the entire data of the columns Category ID, Category Name and Description in the Categories table of Northwind database:

Below is the complete Default.aspx source code with VB.NET scripts (inline, direct file method) to connect to MS Access, retrieve the data, and output it to the browser:

[code] <%@ Page language="VB" %> <%@ Import Namespace="System.Data.OleDb" %> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Connecting to MS Access directly from ASP.NET web page- Direct File Method</title> <script language="VB" runat="server"> Sub Page_Load() Dim msaccessconnect As OleDbConnection Dim Query As String Dim databasecommunication As OleDbCommand Dim readdatabase As OleDbDataReader msaccessconnect = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=L:aspdotnetprojectsmsaccessdirectApp_DataNwind.mdb") msaccessconnect.Open() Query = "SELECT CategoryID,CategoryName,Description FROM `Categories`" databasecommunication = New OleDbCommand(query, msaccessconnect) readdatabase = databasecommunication.ExecuteReader() Categories.DataSource = readdatabase Categories.DataBind() readdatabase.Close() msaccessconnect.Close() End Sub </script> </head> <body> <form id="Form1" runat="server"> <asp:Repeater id="Categories" runat="server"> <HeaderTemplate> <table> <tr> <th>Category ID</th> <th>Category Name</th> <th>Description</th> </tr> </HeaderTemplate> <ItemTemplate> <tr> <td><%#Container.DataItem("CategoryID")%></td> <td><%#Container.DataItem("CategoryName")%></td> <td><%#Container.DataItem("Description")%></td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> </form> </body> </html> [/code]

To implement the code in your own project, simply replace:

[code]data source=L:aspdotnetprojectsmsaccessdirectApp_DataNwind.mdb[/code]

With the actual path to your database. Or, if your database utilizes a username and password for authentication, you can entirely replace:

[code]Provider=Microsoft.Jet.OLEDB.4.0;data source=L:aspdotnetprojectsmsaccessdirectApp_DataNwind.mdb[/code]

With the different variations of Access database connection strings mentioned here: http://www.connectionstrings.com/access and here for Access 2007: http://www.connectionstrings.com/access-2007

To implement the above example in your own project, try deleting the entire Default.aspx source and paste the above code while tweaking the connection strings to reflect your database path and authentication requirements.

Meanwhile below is the equivalent C# code of the above VB.NET example:

[code] <%@ Page language="C#" %> <%@ Import Namespace="System.Data.OleDb" %> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Connecting to MS Access directly from ASP.NET web page- Direct File Method</title> <script language="C#" runat="server"> public void Page_Load() { OleDbConnection msaccessconnect = default(OleDbConnection); string Query = null; OleDbCommand databasecommunication = default(OleDbCommand); OleDbDataReader readdatabase = default(OleDbDataReader); msaccessconnect = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=L:\aspdotnetprojects\msaccessdirect\App_Data\Nwind.mdb"); msaccessconnect.Open(); Query = "SELECT CategoryID,CategoryName,Description FROM `Categories`"; databasecommunication = new OleDbCommand(Query, msaccessconnect); readdatabase = databasecommunication.ExecuteReader(); Categories.DataSource = readdatabase; Categories.DataBind(); readdatabase.Close(); msaccessconnect.Close(); } </script> </head> <body> <form id="Form1" runat="server"> <asp:Repeater id="Categories" runat="server"> <HeaderTemplate> <table> <tr> <th>Category ID</th> <th>Category Name</th> <th>Description</th> </tr> </HeaderTemplate> <ItemTemplate> <tr> <td><%# DataBinder.Eval(Container.DataItem, "CategoryID")%></td> <td><%# DataBinder.Eval(Container.DataItem, "CategoryName")%></td> <td><%# DataBinder.Eval(Container.DataItem, "Description")%></td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> </form> </body> </html> [/code]

Step 7: In Visual Web Developer, go to File – View in Browser. This is how it looks in the browser:

Connecting to MS Access Database using Code behind

In some instances, you will need to put all of your VB.NET or C# scripts into a separate file. This is the “code behind-the-scenes” method. One of the advantages of this method is that it is easier to manage and clean. In this method, you will put your code (which will connect to the MS Access database to fetch and show data to the browser) into Default.aspx.vb (for VB.NET) or Default.aspx.cs (for C#).

To get started, try creating another ASP.NET project, just for testing purposes (refer to Step 1 to Step 5 in the previous section). Name the project “msaccesscodebehind” to differentiate it from the previous example, which is uses the inline method.

Using VB.NET:

This is the complete Default.aspx source code:

[code] <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Connecting to MS Access from ASP.NET web page using Code Behind</title> </head> <body> <form id="Form1" runat="server"> <asp:Repeater id="Categories" runat="server"> <HeaderTemplate> <table> <tr> <th>Category ID</th> <th>Category Name</th> <th>Description</th> </tr> </HeaderTemplate> <ItemTemplate> <tr> <td><%#Container.DataItem("CategoryID")%></td> <td><%#Container.DataItem("CategoryName")%></td> <td><%#Container.DataItem("Description")%></td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> </form> </body> </html> [/code]

Note: There is no more VB.NET code in the Default.aspx, because the connection strings will be placed in the code behind file which is Default.aspx.vb.

Below is the complete Default.aspx.vb code:

[code] Imports System.Data.OleDb Partial Class _Default Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim msaccessconnect As OleDbConnection Dim Query As String Dim databasecommunication As OleDbCommand Dim readdatabase As OleDbDataReader msaccessconnect = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=L:aspdotnetprojectsmsaccessdirectApp_DataNwind.mdb") msaccessconnect.Open() Query = "SELECT CategoryID,CategoryName,Description FROM `Categories`" databasecommunication = New OleDbCommand(Query, msaccessconnect) readdatabase = databasecommunication.ExecuteReader() Categories.DataSource = readdatabase Categories.DataBind() readdatabase.Close() msaccessconnect.Close() End Sub End Class [/code]

Note: If you view this in the browser, the output will remain the same with the inline method.

Using C#:

This is the complete Default.aspx source code:

[code] <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Connecting to MS Access from ASP.NET web page using Code Behind</title> </head> <body> <form id="Form1" runat="server"> <asp:Repeater id="Categories" runat="server"> <HeaderTemplate> <table> <tr> <th>Category ID</th> <th>Category Name</th> <th>Description</th> </tr> </HeaderTemplate> <ItemTemplate> <tr> <td><%# DataBinder.Eval(Container.DataItem, "CategoryID")%></td> <td><%# DataBinder.Eval(Container.DataItem, "CategoryName")%></td> <td><%# DataBinder.Eval(Container.DataItem, "Description")%></td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> </form> </body> </html> [/code]

And this is the complete Default.aspx.cs code:

[code] using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.OleDb; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { OleDbConnection msaccessconnect = default(OleDbConnection); string Query = null; OleDbCommand databasecommunication = default(OleDbCommand); OleDbDataReader readdatabase = default(OleDbDataReader); msaccessconnect = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=L:\aspdotnetprojects\msaccessdirect\App_Data\Nwind.mdb"); msaccessconnect.Open(); Query = "SELECT CategoryID,CategoryName,Description FROM `Categories`"; databasecommunication = new OleDbCommand(Query, msaccessconnect); readdatabase = databasecommunication.ExecuteReader(); Categories.DataSource = readdatabase; Categories.DataBind(); readdatabase.Close(); msaccessconnect.Close(); } } [/code]

Connecting to MS Access Database using Gridview and AccessDataSource

Gridview is an important web control in ASP.NET that outputs data in table format. This saves a lot of time, since you do not need to worry about using the correct VB.NET or C# connection strings script.

Refer to the steps below for how you can use Gridview and AccessdataSource to fetch data from an MS Access database and display the same output (as used in the previous example) to the browser.

Step 1: Using either Visual Web Developer or Visual Studio, create a new website project. Refer to the steps in previous section. Step 2: In Default.aspx, click and drag the “AccessDataSource” web control (Tools – Data – AccessDataSource) between <div> tags. Step 3: Click and drag the Gridview web control (Tools – Data – Gridview) below the AccessDataSource web control.

Step 4: Go to Design view, right click on AccessDataSource, and click “Configure Data Source”. Step 5: It will ask for the Access database file. Make sure you first copy the Northwind Database project to your App_Data. When the database is already inside your App_data directory, browse to it. Then, click next. Step 6: You will then provide the SQL query that you need to execute in the database. Simply copy and paste the query from the previous example to the text box (where “SQL Statement” is checked):

SELECT CategoryID,CategoryName,Description FROM `Categories`

Screenshot:

Step 7: Finally, click next and Finish.

This is the complete Default.aspx source code after configuration:

[code] <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/Nwind.mdb" SelectCommand="SELECT CategoryID,CategoryName,Description FROM `Categories`"></asp:AccessDataSource> <asp:GridView ID="GridView1" runat="server" DataSourceID="AccessDataSource1"> </asp:GridView> </div> </form> </body> </html> [/code]

The output of this code in the browser will be similar to the previous examples.

You can download all the illustrated source code as discussed in this tutorial here: http://www.dotnetdevelopment.net/tutorials/connecttomsaccess.zip

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