Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 20th, 2005, 01:25 PM
Registered User
 
Join Date: Jan 2005
Location: , , Ireland.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to CHen
Default Connecting ASP.NET with Access

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
 
Old January 20th, 2005, 01:42 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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
 
Old January 28th, 2005, 05:07 PM
Registered User
 
Join Date: Jan 2005
Location: , , Ireland.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to CHen
Default

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

Thanks,
Clare.

Clare Hennessy
 
Old January 30th, 2005, 01:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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




 
Old January 30th, 2005, 01:46 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old January 30th, 2005, 01:52 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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.







Similar Threads
Thread Thread Starter Forum Replies Last Post
Connecting to Access 20007 DB with vb.net JohnBoy VB.NET 1 May 21st, 2008 01:26 PM
problem connecting .net with access in 64bit darkelf ADO.NET 1 September 26th, 2006 05:26 PM
Connecting database using ms access and vb.net adil_farooq Access 0 October 27th, 2005 11:54 PM
Connecting ASP.Net to Access CHen Access 5 February 3rd, 2005 02:03 AM
Connecting to Access DB using Visual Basic .NET Lieth Access 6 September 26th, 2003 06:17 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.