I'm getting an exception while trying to open a OleDBConnection.

I'm developing a win app under Vista 64bit environment. The file's suppose to open an excel file, and bind it to a datagridview.  The code below is throwing the following exception:

ServerVersion 'oleDbXLS.ServerVersion' threw an exception of type System.InvalidOperationException'      string {System.InvalidOperationException}

When it's trying to execute:

oleDbXLS.Open();

I've been researching the whole night and I can't find any answers. Help!
private DataTable get_data_from_excel(string _strFullFilename)
{
     OleDbConnection oleDbXLS = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
          "Data Source=" + _strFullFilename + ";" +
          "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';");
     OleDbCommand OleDbcomSelect = new OleDbCommand(@"SELECT * FROM [Employee Information]", oleDbXLS);
     OleDbDataAdapter OleDbdaMain = new OleDbDataAdapter();
     DataTable oReturn = new DataTable();
 
     try
     {
         oleDbXLS.Open();
         OleDbdaMain.SelectCommand = OleDbcomSelect;
         OleDbdaMain.Fill(oReturn);
     }
     catch (Exception e)
     {
     }
    finally
    {
         oleDbXLS.Close();
         OleDbdaMain = null;
     }
 
     return oReturn;
}

Open in new window

LVL 2
youteaAsked:
Who is Participating?
 
youteaAuthor Commented:
Yea, it was weird that the debugger just skip that line when it's suppose to run right after open().
0
 
käµfm³d 👽Commented:
What version of excel is the file?
0
 
youteaAuthor Commented:
I believe it's 2003
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
käµfm³d 👽Commented:
What version of Excel do you have loaded on the macine? I ran the code as posted and I connected fine.
0
 
youteaAuthor Commented:
I'm running it on 2007, I've tried Excel 12.0 in my connection string before, and it doesn't work.
0
 
käµfm³d 👽Commented:
Do you get the same error message?
0
 
youteaAuthor Commented:
Yup, same exception.
0
 
youteaAuthor Commented:
I've just tested the same code on a Win XP x86 box running Excel 2003, and it works fine. Does this has to do with any connection/driver settings?
0
 
käµfm³d 👽Commented:
My belief is that it has something to do with the 2007 version you have installed. You are using a connection string for a 2003 version Excel install. However, you say that the 2007 string does not work for you. When you tried the 2007 version, was it similar to:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

??
0
 
youteaAuthor Commented:
Yea, I googled that string last night, and it's exact same string. After looking at the debug messages from the working version, it throws the same exception when that line of code is being executed. With that in mind, I don't think it's the open() function is generating the error. Thanks for looking those up through.
0
 
youteaAuthor Commented:
I found out what's wrong! I need $ from my select statement
SELECT * FROM [Employee Information$]

Open in new window

0
 
käµfm³d 👽Commented:
Glad you figured it out--it is peculiar though that it would throw the error on near the open() statement rather than the fill() statement.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.