Why does Connection String Change?

There are many questions about not being able to connect. I have done some debugging, and this is what I've found.

In my Web.Config file, I provide the following connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dbProject2.mdb

In my code, some days I can connect to the Access database, and some days I can not.

private static void GetAccessDbInfo(string sql)
{
    var exe = System.Reflection.Assembly.GetExecutingAssembly().Location;
    var exePath = Path.GetDirectoryName(exe);
    var path = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();
    var conStr = $"{ConfigurationManager.ConnectionStrings["dbProject2"]}";
    using (var cmd = new OleDbCommand(sql, new OleDbConnection(conStr)))
    {
        try
        {
            cmd.Connection.Open();
            using (var reader = cmd.ExecuteReader())
            {
              // unrelated code to pull the data
            }
        }
        catch (OleDbException e)
        {
            Debug.WriteLine(e.Message);
            Debug.WriteLine($"Exe Path: {exePath}");
            Debug.WriteLine($"DataDirectory: {path}");
        }
        finally
        {
            cmd.Connection.Close(); // MS Access will not close automatically
        }
    }
}

Here is the OleDbException Message:

Could not find file 'C:\Program Files\IIS Express\dbProject2.mdb'.

Why is it looking in the IIS Express folder???

Data from these snippets:

var exe = System.Reflection.Assembly.GetExecutingAssembly().Location;
var exePath = Path.GetDirectoryName(exe);
Debug.WriteLine($"Exe Path: {exePath}");

I get this:

Exe Path: C:\Users\jp2code\AppData\Local\Temp\Temporary ASP.NET Files\vs\e1b79110\d4b45aaa\assembly\dl3\50c7c262\9e04b6ea_d01bd901

Obviously, I can't tell it to base its search on the executing assembly location.

Now look at the DataDirectory info:

var path = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();
Debug.WriteLine($"DataDirectory: {path}");

And it gives the correct location:

DataDirectory: C:\Users\jp2code\Source\Workspaces\Websites\Project2\Project2\App_Data

It shows the right location where my database is, but for whatever reason, Visual Studio has gone crazy and decided to go to the IIS Express folder.

I tried replacing |DataDirectory| with the path:

var path = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();
var webConfigStr = $"{ConfigurationManager.ConnectionStrings["dbProject2"]}";
var conStr = webConfigStr.Replace("|DataDirectory|", path);

But I still get the same OleDbException Message:

Could not find file 'C:\Program Files\IIS Express\dbProject2.mdb'.

Frustration.

How do I fix this?

0 answers