Exception in Importing excel to SQL database using C#

Question

I've asked this question in below link. Reposting this with the new error message that I'm facing:

Exception in Importing excel using C# using Oledbconnection

I'm trying to build "Import to DB from excel" functionality.

I use the below connection string which works fine locally

excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path +
                                                 ";Extended Properties=\"HDR=YES;\";Jet OLEDB:Engine Type=37";

After deployment, i get Error :System.InvalidOperationException: The 'Microsoft.Jet.OLEDB.12.0' provider is not registered on the local machine.

Steps Taken as suggested in first post:

1. Changed the Platform Target from 'Any CPU' to x86 in both Configuration Manager --> Platform & Project --> Properties --> Build. After doing this I get Error: System.BadImageFormatException: Could not load file or assembly 'Webtool' or one of its dependencies. An attempt was made to load a program with an incorrect format

My local machine where i'm developing is 32 bit. The deployment IIS server is 64 bit.

2. Again changed from x86 to 64 bit in both the places mentioned in above point & deployed. Got the original error "System.InvalidOperationException: The 'Microsoft.Jet.OLEDB.12.0' provider is not registered on the local machine."

I googled for BadImageFormatException but couldn't find much. I cannot install ACE or any other excel components in IIS.

Please let me know if there is any solution to this.

Answer 1

First off you need to figure out if your computer should be set to x64 or x86 which corresponds to 64 and 32 bit operating system respectively.

You can determine this under

Control Panel System ---> Security System

Then there will be a list of properties. It is the value under

System --> System Type

---

You will also need installed on the computer the Microsoft Access Database Engine available at Microsofts website http://www.microsoft.com/en-us/download/details.aspx?id=13255 Once again there are two choices and you need to pick appropriately based on your 32 or 64 bit system.

---

Next step will be to change the build type in your visual studios to once again the appropriate type. Then be sure to be using that version of your application on your computer or server of choice.

Debug --> (YourApplicationName) Properties --> A Screen Opens *select* Build --> Platform target Change the platform target to your choice

It will then move a solid platform 32 bits up or 64 bits up so you can make Mario jump to the next level. . . . (seriously this feels like a videogame walkthrough guide)

---

Then you need to set your connection string appropriately:

I have found this works for the x64

    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Migration\Sharepoint Access SQL Batch Job\Database2.accdb;Jet OLEDB:Database Password=password";

I believe it will work for the x32 system using Provider=Microsoft.Jet.OLEDB.4.0; but I do not have a 32 bit system only 8 bit (NES) and 64 bit (N64)

All jokes aside I hope this helps, and if someone should verify the correct string for the JET that would be a good thing. Also your connection string should be in your app.config or web.config file and you should be using something like

using System.Configuration; string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["yourDB"].ToString();

So it is easy to deploy to multiple environments.

Best of Luck!

Answer 2

The alternative best solution is to use OpenXML SDK for Import/Export of Excel... it has great functionalities/features which ease the server issues.