0

So the following C# code contain the code that I use to check my database connection. Not sure whether it's the correct way or not but I seems to always get the:

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

Any idea how to fix this? btw I'm currently using VS2017 and Excel 2016

try{
   string connectString = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source =C:\\Users\\user\\Desktop\\Book1.xlsx;Extended Properties=\"Excel 16.0 XML;HDR=Yes;\"";

   OleDbConnection xlConnection = new OleDbConnection(connectString);
   xlConnection.Open();
   MessageBox.Show("Success");

   }
catch(OleDbExceptionEx ex)
   {
     MessageBox.Show(ex.Message);
   }
halfer
  • 19,824
  • 17
  • 99
  • 186
Joseph Lim
  • 23
  • 9
  • 1
    What is the exception you are catching? – Tamás Szabó May 09 '17 at 07:05
  • You also seem to be missing a quotation mark from the end of the connection string. – Tamás Szabó May 09 '17 at 07:08
  • Sorry I'm still quite new to this, what do you mean by that? Does the code check my connection? – Joseph Lim May 09 '17 at 07:10
  • It's still wrong though, my original code has the quotation mark in it, I just missed it here – Joseph Lim May 09 '17 at 07:12
  • When you call `xlConnection.Open()` it will throw an exception if it can't connect successfully. You are then catching this exception using the try-catch block. That exception contains details about why opening the connection failed. Change `catch` to `catch (OleDbException ex)` and add `MessageBox.Show(ex.Message)` to the block underneath it. – Tamás Szabó May 09 '17 at 07:12
  • Ahh I see, now it state" System.InvalidOperationException: 'The Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine' Hmm is there a higher version than this then? – Joseph Lim May 09 '17 at 07:16
  • When you uncover new info to your problem it's best to edit your question, so others can see it asap, so make sure you add the exception message to your question :). As for the solution, check out [the top answer here](http://stackoverflow.com/questions/11179905/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine). Here is the [official Microsoft thread](https://social.msdn.microsoft.com/Forums/en-US/1d5c04c7-157f-4955-a14b-41d912d50a64/how-to-fix-error-the-microsoftaceoledb120-provider-is-not-registered-on-the-local-machine?forum=vstsdb) – Tamás Szabó May 09 '17 at 07:19
  • Try this at the end: `Extended Properties=\"Excel 12.0;HDR=YES\"`. And see in Control Panel - ODBC Data Sources. – i486 May 09 '17 at 07:31
  • @i486 Unfortunately, It doesn't work – Joseph Lim May 09 '17 at 07:52
  • See in Data Sources in Control Panel for Excel data source. Maybe you have to install ODBC source, or MS Office, or LibreOffice (which will install ODBC driver). – i486 May 09 '17 at 09:05
  • @i486 I fixed it by downloading this https://www.microsoft.com/en-us/download/confirmation.aspx?id=23734 Thanks anyway :) – Joseph Lim May 09 '17 at 09:20

1 Answers1

-1

As per a comment conversion with Tamás:

The solution is in the top answer here. Here is the official Microsoft thread.

So from the link that Tamás has provided, I manage to fix the my problem by downloading this.

Community
  • 1
  • 1
Joseph Lim
  • 23
  • 9