Programming Articles

Connecting to MS Access in ASP.NET without a DSN

This script demonstrates connecting to an Access database in ASP.NET without a DSN. It is written in VB. The script will make the connection and then output all the rows in the specified table to an HTML table.

Public Sub Page_Load(Source As Object, E As EventArgs)
This function is called when the page loads.

Dim ConnectionString As String
ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & dbpath & ";"
Declares a variable and creates the connection string.

Dim dbpath As String
dbpath = "C:path odatabasedb est.mdb"
Declares a variable and sets it to the database path.

Dim QuerySQL As String
QuerySQL = "SELECT * FROM table"
Declares a variable and sets it to the query string.

Dim objConn As New OleDbConnection(ConnectionString)
Creates the connection object from the connection string.

Dim objCmd As OleDbCommand

objCmd = New OleDbCommand
objCmd.CommandText = QuerySQL
objCmd.Connection = objConn
Sets the query object.

Try
objConn.Open()
Opens the connection to the database.

dg1.DataSource = objCmd.ExecuteReader()
dg1.DataBind()
Executes the query and gives the results to the data grid "dg1".

Catch Err As Exception

ltlError.Text = Err.ToString()
Catch any errors in the connection

objConn.Close()
Close the database connection.

End Try

<html>
<head>
<title>Connecting to Microsoft Access Database</title>
</head>
<body>

<form runat="server">
<h3>Connecting to Microsoft Access Database</h3>
<h3>
<asp:DataGrid id="dg1" runat="server"></asp:DataGrid>
</h3>
<p>
<asp:Literal id="ltlError" runat="server"></asp:Literal>
</p>
</form>

</body>
</html>
Prints out the results of the database query from the data grid in HTML.

--Here is the full script, the filename should be: access-dsnless-vb.aspx--

<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Page Language="VB" %>
<script runat="server">

Public Sub Page_Load(Source As Object, E As EventArgs)

     Dim ConnectionString As String
     Dim dbpath As String

     dbpath = "C:path odatabasedb est.mdb"
     ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & dbpath & ";"

     Dim QuerySQL As String
     QuerySQL = "SELECT * FROM table"

     Dim objConn As New OleDbConnection(ConnectionString)
     Dim objCmd As OleDbCommand

     objCmd = New OleDbCommand
     objCmd.CommandText = QuerySQL
     objCmd.Connection = objConn

     Try
          objConn.Open()

          dg1.DataSource = objCmd.ExecuteReader()
          dg1.DataBind()

     Catch Err As Exception
          ltlError.Text = Err.ToString()

     Finally
          objConn.Close()

     End Try

End Sub

</script>
<html>
<head>
<title>Connecting to Microsoft Access Database</title>
</head>
<body>

<form runat="server">
<h3>Connecting to Microsoft Access Database</h3>
<h3>
<asp:DataGrid id="dg1" runat="server"></asp:DataGrid>
</h3>
<p>
<asp:Literal id="ltlError" runat="server"></asp:Literal>
</p>
</form>

</body>
</html>

--End Script--
1/5/2008 2:14:01 AM Category Database Connection Strings Comments 0

Back