CODEWITHSUNDEEP
c#visual-studiooledbconnection
mybirthname
mybirthname

Reputation: 18127

OleDbConnection.Open() throws exception only in one project, same code works in other projects

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

I know the common fix for this which is to install:

Microsoft Access Database Engine 2010 Redistributable

Or

2007 Office System Driver: Data Connectivity Components

Both are installed on my local PC. This is the code which I have

        OleDbConnection conn = new OleDbConnection();
        string fileName = "test.xlsx";


        try
        {
            string connectString = String.Format(
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR={1};'",
                fileName, "YES");

            conn.ConnectionString = connectString;
            conn.Open(); //exception is thrown here !!!

            OleDbCommand comm = new OleDbCommand();
            comm.CommandText =
                string.Format("CREATE TABLE [{0}] ", "Test");

            comm.Connection = conn;
            comm.ExecuteNonQuery();

            OleDbDataAdapter ad = new OleDbDataAdapter(
                string.Format("SELECT * FROM [{0}]", "Test"), conn);
            OleDbCommandBuilder builder = new OleDbCommandBuilder(ad);
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            conn.Close();
        }

I try this code in other projects on my local machine and everything is working. I have project which creates excel exports and I don't have this problem.

The problem is in the project I don't understand how to fix it. Also on the current project I create one new .aspx page and in Page_Load put only this code, same exception.

Additional information: this project was written on vs 2008, convert to 2010 and after that used in vs 2012. Everything was working in the project till now.

Also same thing for JET connection string !

EDIT

After the answer of Joe I see that this project is run on 64bitProcess:

bool test = Environment.Is64BitProcess; //return true.

My driver is for 32bit. What I can do now, can I change the environment process. I can't install the driver for 64bit process because I have installed Office 2010 x32.

Upvotes: 2

Views: 5036

Answers (2)

mybirthname
mybirthname

Reputation: 18127

After some hours I found a solution:

What were the steps, first I try to run the project on x86 - Properties/Build/Platform Target. Exception was thrown that I can't rebuild, because a registry was false. I create the registry. How to do it:

In notepad file paste this code and save it like reg file. Make the file name something to remember why you have it in the future(Fusion.reg).

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Fusion]
"EnableLog"=dword:00000001

After that I had a problem because not all of my assemblies was readable by the application pool. Solution about that was going to IIS/Application Pools/Application Pool 4.0/General/Enable 32- Bit Applications. After that restart the IIS, close the project and open it again and everything was working with 32bit version.

Upvotes: 0

to StackOverflow
to StackOverflow

Reputation: 124726

The OLEDB drivers for 32-bit and 64-bit applications are different.

If you only have the 32-bit driver installed, then 64-bit applications that attempt to use it will get this error. Similarly, if you have only the 64-bit version installed, then 32-bit applications that attempt to use it will get this error.

You say:

I try this code in other projects on my local machine and everything is working

Ergo, at least one of the two must be correctly installed.

To understand what's happening you could examine Environment.Is64BitProcess in both the application that works, and the one that doesn't. That will tell you which version is missing.

Then download and install the 32-bit or 64-bit version that's missing from:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

You need AccessDatabaseEngine.exe (32-bit) or AccessDatabaseEngine_64.exe (64-bit)

Note that you may need to specify the provider as 'Microsoft.ACE.OLEDB.14.0' for the Office 2010 version (12.0 was for Office 2007).

Upvotes: 4

Related Questions