• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3373
  • Last Modified:

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

0
youtea
Asked:
youtea
  • 7
  • 5
1 Solution
 
käµfm³d 👽Commented:
What version of excel is the file?
0
 
youteaAuthor Commented:
I believe it's 2003
0
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
youteaAuthor Commented:
Yea, it was weird that the debugger just skip that line when it's suppose to run right after open().
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now