Connecting ASP.NET with Access

CHen · January 20th, 2005, 01:25 PM

I am trying to connect from ASP.Net to Access and the following is causing an error in my WebConfig file.

<configuration>
    <appSettings>

    <add key = "ConnectionString" value = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +Server.MapPath("ComputerCentre.mdb"); />
    </appSettings>

Just wondering if anyone can see where this is wrong.
Thanks.
Clare.

Clare Hennessy

bmains · January 20th, 2005, 01:42 PM

You can't have server code in the web.config. You have to do Server.MapPath("ComputerCentre.mdb") in the ASP.NET page, when you get the connection string. You can have in the config:

<add key = "ConnectionString" value = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}" />

And in the ASP.NET Page:

Dim strConnString As String = ConfigurationSettings.AppSettings.Get("ConnectionS tring")
strConnString = String.Format(strConnString, Server.MapPath("ComputerCentre.mdb"))

Although, you may want to look at something a little more "concrete" than putting that code in each and every ASP.NET page.

Brian

CHen · January 28th, 2005, 05:07 PM

Thanks Brian.
Anything you would suggest instead of doing it that way?
I'm doing the proj in C#

Thanks,
Clare.

Clare Hennessy

Bob Bedell · January 30th, 2005, 01:20 AM

Hi Chen,

Here's something I use in C# that reduces the connectioin string code in an ASP.NET page to the following line:

OleDbConnection cn = new OleDbConnection(GlobalStatics.ConnectionString);

Here's how it works. I'm using a secured .mdb with a work group file named system.mdw just to cover all the bases. Both db's are on the server.

1. File: Global.asax.cs

Add the following to the Application_Start sub in global.asax -

Code:

protected void Application_Start(Object sender, EventArgs e)
        {
            string connectString = ConfigurationSettings.AppSettings["ConnectionString"];
            connectString = String.Format(connectString, Server.MapPath("ArabianNights.mdb"), Server.MapPath("system.mdw"));
            Globals.ConnectionString = connectString;
        }

2. File: GlobalStatics (User-defined class)

Since C# doesn't support VB style global variables, create a new class and call it GlobalStatics. Create a static read/write property to store the connection string. Here's the code:

Code:

using System;

namespace AccessGlobalConnCS
{
    // A sealed class cannot be inherited. 
    public sealed class GlobalStatics
    {
        private static string connectionString; 

        public static string ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }

        private GlobalStatics()
        {
            //*********************************************************
            //
            // Since this class provides only static connection 
            // string get/set methods, make the default constructor 
            // private to prevent instances from being created with 
            // "new GlobalStatics()".
            //
            //*********************************************************
        }
    }
}

3. File: Web.config

Here's the <appsettings> attribute:

Code:

configuration>
    <appSettings>
        <add key="ConnectionString"
        value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:System Database={1};User ID=Ali Baba;Password=Open sesame "/>
    </appSettings>

4. File: WebForm1.aspx.cs

Open connections on the WebForm code-behind file like:

Code:

private void Page_Load(object sender, System.EventArgs e)
        {

            //Pass connection string to OleDbConnection object.
            OleDbConnection cn = new OleDbConnection(GlobalStatics.ConnectionString);

            //Open connection.
            cn.Open();

            //Pass SQL statement to OleDbCOmmand object.
            string selectString = "SELECT MagicalFormulaID, MagicalFormulaName FROM MagicalFormulas";
            OleDbCommand cmd = new OleDbCommand(selectString,cn);

            //Execute an OleDbDataReader
            OleDbDataReader reader = cmd.ExecuteReader();

            //Bind DataReader to DataGrid 
            DataGrid1.DataSource = reader;
            DataGrid1.DataBind();

            //Close reader and connection.
            reader.Close();
            cn.Close();
        }

Probably should use encrypted passwords in WebConfig files, but you get the general idea. Just using a C# static property to emulate a vb global variable.

- Bob

Bob Bedell · January 30th, 2005, 01:46 AM

Gotta' say the main reason I like this approach is 'cause I put together (most of) an Access version of Microsoft's Data Access Application Block. So any time I need, say, a DataReader, all I gotta' do is:

Code:

OleDbDataReader rdr = AccessHelper.ExecuteReader(GlobalStatics.ConnectionString, 
                                                             CommandType.StoredProcedure,
                                                             "procGetCatalog",
                                                             new OleDbParameter("@C_ID", 1));

One liner. Love it. (procGetCatalog is really just an Access parameterized query ;))

- Bob

Bob Bedell · January 30th, 2005, 01:52 AM

Oops...

Globals.ConnectionString = connectString;

in the Application_Start sub should be...

GlobalStatics.ConnectionString = connectString;

Globals is really the name of the class I use. I used GlobalStatics in the post so you wouldn't get the user-defined class and the Global.asax class confused.