Introduction
This tutorial will show you how to connect to an access database with asp.net and then iterate through the records inside the database and display them on a page. For this tutorial I will be using "Microsoft Visual Web Developer 2008 Express", which is a free program and can be downloaded by anyone right here.
Creating the Database
First thing that you will need to do is to create the database we are going to use. By default we will create the Database in the "App_Data" folder of our website, so if we have created a website called "Tutorial1", the Database would usually be created in "Tutorial1\App_Data\Database-name.mdb". So let's browse to this folder and create the database, so in the "App_Data" folder, right click and select "New > Microsoft Office Access Application" and give it your desired name. Let's add a table to this database and call it "Customers" and inside that table we'll add the following columns: "CustomerID(AutoNumber)", "Name(Text)", "LastName(text)", "TelephoneNumber(number)". Then just populate this table with some fake information.
Setting Up The Page
Let's create the following page for this example: "customers.aspx", page language: "visual basic", and the code is going to be in the same page, not in the code-behind page.
Once you have got the page open where you'd like to display all the data from the database, we first have to add two namespaces that are going to be used in this example so right at the top you will have the following
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
So this then imports the "System.Data" namespace and the "System.Data.OleDb" namsepace, these naspaces are not a must to import at the top of our page, but makes life a little easier in the long run.
Connecting to the database
You will now add the code somewhere in your page with the code tags (<% %>) around it:
Dim ConString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & Server.MapPath("App_Data\Customers.mdb")
Dim oCon As OleDbConnection = New OleDbConnection(ConString)
oCon.Open()
Dim sQuery As String = "SELECT * FROM Customers"
Dim oCommand As OleDbCommand = New OleDbCommand(sQuery, oCon)
Dim oDataReader As OleDbDataReader = oCommand.ExecuteReader()
oCon.Close
So this code works as follows:
ConString is the connection string to the selected Database, and the "Server.MapPath" maps the path to the "App_Data\Customers.mdb" for when this is uploaded to the server/host we don't have to add a fixed path, but it get's worked out for us.
oCon is the connection to the database which creates a connection to the database using the connection string provided ("ConString").
oCon.Open() opens the connection to the database.
sQuery is the SQL query we specify to be executed on this connection, in this case we select all the fields from the Customers table.
oCommand is a Database command, and in this case it sets "sQuery" to "oCon" ready for execution
oDataReader is a Data Reader that in this case executes "oCommand" and reads out all the data from that database with the selected query.
oCon.Close() closes the database connection.
Reading Data from the Database
now that we have defined the "oDataReader" as an OleDbDataReader we can use it to iterate throught the records. We do that by adding the following code which is placed between "Dim oDataReader As OleDbDataReader = oCommand.ExecuteReader()" and "oCon.Close()"
While oDataReader.Read
Response.Write("<p>Name: " & oDataReader("Name") & "</p>")
Response.Write("<p>Name: " & oDataReader("LastName") & "</p>")
Response.Write("<p>Name: " & oDataReader("TelephoneNumber") & "</p>")
End While
This piece of code goes through the database row by row and reads the "Name","LastName" and "TelephoneNumber" fields from the database for each row.
The full page's code will look somethig like this
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server"></script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>First Db Connection</title>
<link rel='stylesheet' type='text/css' href='stylesheet.css' />
</head>
<body>
<h1>First Db Connection</h1>
<p>This is a tutorial I found on Ozzu</p>
<%
Dim ConString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & Server.MapPath("App_Data\Customers.mdb")
Dim oCon As OleDbConnection = New OleDbConnection(ConString)
oCon.Open()
Dim sQuery As String = "SELECT * FROM Customers"
Dim oCommand As OleDbCommand = New OleDbCommand(sQuery, oCon)
Dim oDataReader As OleDbDataReader = oCommand.ExecuteReader()
While oDataReader.Read
Response.Write("<p>Name: " & oDataReader("Name") & "</p>")
Response.Write("<p>Name: " & oDataReader("LastName") & "</p>")
Response.Write("<p>Name: " & oDataReader("TelephoneNumber") & "</p>")
End While
oCon.Close
%>
</body>
</html>
Conclusion
We saw here how to connect to an Access database and read Data from it and display it on our page. So the steps we followed were: we connect to the Database, we open the connection, we use a OleDbDataReader to read through the records, as the reader reads we write the results on our page, and we close the connection.
This page was published on It was last revised on
