Connection to MS Access database using ODBC: My own ASP.NET 2.0 coding style
By Aurelie
A. Peralta
If you
dont have a good .NET IDE (Integrated Development Environment) but at least you
have the following: an operating system with IIS with ASP.Net support, dotnet framework
2.0, Microsoft Access database and a text editor like Notepad, you may find these
program codes helpful in creating a webpage that can retrieve records from your
database. (actually my self-discovered coding style)
Open your
text editor and let us start working on this.
First you
should have the following on top of your program codes:
<%@
Page Language=”VB” %>
<%@ import Namespace=”System.Data” %>
<%@ import Namespace=”System.Data.Odbc” %>
<%@ import Namespace=”System.XML” %>
<%@ import Namespace=”System.Web” %>
<%@ import Namespace=”System.Web.UI.WebControls” %>
The first
line indicates that our ASP.Net page will be using visual basic as its language.
The succeeding lines indicate the different namespaces or objects from the .net
framework that will be used for our ODBC database connection and for our webform.
next create
a script tag:
<%@
Page Language=”VB” %>
<%@ import Namespace=”System.Data” %>
<%@ import Namespace=”System.Data.Odbc” %>
<%@ import Namespace=”System.XML” %>
<%@ import Namespace=”System.Web” %>
<%@ import Namespace=”System.Web.UI.WebControls” %>
<script
runat=”server”></script>
We will
place our vb.net codes inside this script tag. It is the program section of our
webpage.
Inside
the script tag, create a page load sub procedure. This event procedure will handle
the process of connecting to our database and retrieving the records to be displayed
in a datagrid.
<%@
Page Language=”VB” %>
<%@ import Namespace=”System.Data” %>
<%@ import Namespace=”System.Data.Odbc” %>
<%@ import Namespace=”System.XML” %>
<%@ import Namespace=”System.Web” %>
<%@ import Namespace=”System.Web.UI.WebControls” %>
<script
runat=”server”>
Sub
Page_Load(Src As Object, e As EventArgs)
End
Sub
</script>
Inside
the page load procedure, let us declare the following variables:
<%@
Page Language=”VB” %>
<%@ import Namespace=”System.Data” %>
<%@ import Namespace=”System.Data.Odbc” %>
<%@ import Namespace=”System.XML” %>
<%@ import Namespace=”System.Web” %>
<%@ import Namespace=”System.Web.UI.WebControls” %>
<script
runat=”server”>
Sub Page_Load(Src As Object, e As EventArgs)
Dim dbconn as new OdbcConnection(”Driver={Microsoft
Access Driver (*.mdb)};DBQ=” & Server.Mappath(”~\Mydatabase.mdb”))
Dim dbadapter as new OdbcDataAdapter(”Select * from
MyTable”, dbconn)
Dim dbdataset as new Dataset
End Sub
</script>
The above
codes show ADO.Net in action, the connection object, dataadapter, and the dataset.
Let us
now retrieve the records and display it in a datagrid.
<%@
Page Language=”VB” %>
<%@ import Namespace=”System.Data” %>
<%@ import Namespace=”System.Data.Odbc” %>
<%@ import Namespace=”System.XML” %>
<%@ import Namespace=”System.Web” %>
<%@ import Namespace=”System.Web.UI.WebControls” %>
<script
runat=”server”>
Sub Page_Load(Src As Object, e As EventArgs)
Dim dbconn as new OdbcConnection(”Driver={Microsoft
Access Driver (*.mdb)};DBQ=” & Server.Mappath(”~\Mydatabase.mdb”))
Dim dbadapter as new OdbcDataAdapter(”Select * from
MyTable”, dbconn)
Dim dbdataset as new Dataset
dbdataset.clear()
dbadapter.fill(dbdataset, “MyTable”)
datagrid1.datasource = dbdataset.tables(”MyTable”).defaultview
datagrid1.databind()
End Sub
</script>
Let us
now code the design section of our web page (html part)
<html>
<head>
<title>Sample db connection using ODBC</title>
</head>
<body>
<form runat=”server”>
<ASP:DataGrid id=”datagrid1″ runat=”server”
>
</ASP:DataGrid>
</form>
</body>
</html>
Finally
we have this very simple web application in ASP.NET:
<%@
Page Language=”VB” %>
<%@ import Namespace=”System.Data” %>
<%@ import Namespace=”System.Data.Odbc” %>
<%@ import Namespace=”System.XML” %>
<%@ import Namespace=”System.Web” %>
<%@ import Namespace=”System.Web.UI.WebControls” %>
<script
runat=”server”>
Sub Page_Load(Src As Object, e As EventArgs)
Dim dbconn as new OdbcConnection(”Driver={Microsoft
Access Driver (*.mdb)};DBQ=” & Server.Mappath(”~\Mydatabase.mdb”))
Dim dbadapter as new OdbcDataAdapter(”Select * from
MyTable”, dbconn)
Dim dbdataset as new Dataset
dbdataset.clear()
dbadapter.fill(dbdataset, “MyTable”)
datagrid1.datasource = dbdataset.tables(”MyTable”).defaultview
datagrid1.databind()
End Sub
</script>
<html>
<head>
<title>Sample db connection using ODBC</title>
</head>
<body>
<form runat=”server”>
<ASP:DataGrid id=”datagrid1″ runat=”server”
>
</ASP:DataGrid>
</form>
</body>
</html>
That’s all for now, expect more to come.