2

There is a discussion on this Diagnosing an OLEDB exception when Quering Excel 2010

But my question is, if we are not allowed to install ACE components in IIS servers where website is hosted; how we can avoid this error? Is there any other way to Import excel to SQL database?

I'm using below connectionstring and works perfectly fine in local but not in IIS server after deployment. 

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

Error :System.InvalidOperationException: The 'Microsoft.Jet.OLEDB.12.0' provider is not registered on the local machine.

Community
  • 1
  • 1
Interstellar
  • 662
  • 2
  • 10
  • 29

2 Answers2

1

it is because you are compiling your application as X64 bit , please force it to run as 32 bit (x86) using following settings:

->Right click on Project
->Select Properties
->Goto Build Options
->Change  "Platform Target" from "ANY CPU" to "X86"
Sudhakar Tillapudi
  • 25,935
  • 5
  • 37
  • 67
  • You can set the properties as Sudhakar mentioned above. You can also refer similar answer here http://stackoverflow.com/questions/11179905/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine – Raaghav Nov 15 '13 at 15:46
  • I ran into another error, when I changed from 'ANY CPU' to X86. 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 in 32 bit. The deployment IIS server is on 64 bit. When I change to 64 bit i get the same error "System.InvalidOperationException: The 'Microsoft.Jet.OLEDB.12.0' provider is not registered on the local machine." – Interstellar Nov 18 '13 at 10:08
0

I would suggest you take a look at http://linqtocsv.codeplex.com/.

Using this method you can read in your excel files into C# objects, validate them and then submit them to the SQL Database.

The problem your getting above is a tricky one, I've came across this myself in the past, it normally means you require an instance of Microsoft Office of a particular version installing.

We had some users operating on Office 2003 and they couldn't use our system as they required a later version of the Microsoft Jet Engine on there machine.

Derek
  • 8,300
  • 12
  • 56
  • 88