Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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
Reply With Quote
  #4 (permalink)  
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




Reply With Quote
  #5 (permalink)  
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

Reply With Quote
  #6 (permalink)  
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.



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:06 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.