Link to home
Start Free TrialLog in
Avatar of youtea
youtea

asked on

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

Avatar of kaufmed
kaufmed
Flag of United States of America image

What version of excel is the file?
Avatar of youtea
youtea

ASKER

I believe it's 2003
What version of Excel do you have loaded on the macine? I ran the code as posted and I connected fine.
Avatar of youtea

ASKER

I'm running it on 2007, I've tried Excel 12.0 in my connection string before, and it doesn't work.
Do you get the same error message?
Avatar of youtea

ASKER

Yup, same exception.
Avatar of youtea

ASKER

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?
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";

??
Avatar of youtea

ASKER

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.
Avatar of youtea

ASKER

I found out what's wrong! I need $ from my select statement
SELECT * FROM [Employee Information$]

Open in new window

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.

ASKER CERTIFIED SOLUTION
Avatar of youtea
youtea

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial