Click here to Skip to main content
14,929,655 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I have developed an application which reads data from excel sheet using OLEDB.
It's working fine on my PC where I am using connection string for Excel 2007. But when I copy output files to some another machine then its not working and trowing an error.

Eventually I thought that it was because that machine has Excel 2003 installed
then I tried where Excel 2003 is installed I used connection string as follow -
C#
string sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" + filePath + ";Extended Properties=Excel 8.0;HDR=YES;";
OleDbConnection dbCon = new OleDbConnection(sConnection);
dbCon.Open();

but there also error is occurring. When I tried to debug my code on that machine then it threw an error like "ServerVersion = 'dbCon.ServerVersion' threw an exception of type 'System.InvalidOperationException'" at the time of opening OLEDB Connection.

I am not getting the reason behind the error. Please help. Tell me if you need more information.
Posted
Updated 29-Feb-12 1:55am
v2
Comments
Herman<T>.Instance 29-Feb-12 7:55am
   
HDR=1
Ankit Kumar Gupta 29-Feb-12 7:57am
   
Give detailed exception,
content of filePath.

1 solution

You could try something like this (note that I like to use a set of fall back connection strings, you might want to add strings that are relevant to you there);

C#
public IEnumerable<sometypeofyours> Load(string filename, string sheetName)
{

    foreach (string connectionStringBase in new[]
        {
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;",
            "Provider=Microsoft.Jet.OLEDB.4.0;Data source={0};Extended Properties=Excel 8.0;"
        })
    {
        try
        {
            string connectionString = String.Format(connectionStringBase, filename);
            OleDbDataAdapter adapter = new OleDbDataAdapter(String.Format("select * from [{0}$]", sheetName), connectionString);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset, "dummy");

            DataTable table = dataset.Tables["dummy"];
            IList<sometypeofyours> result = new List<sometypeofyours>();
            foreach (System.Data.DataRow row in table.Rows)
            {
                SomeTypeOfYours item = new SomeTypeOfYours { SomeProperty = row.Get(0) };
                result.Add(item);
            }
            return result;
        }
        catch (Exception)
        {
            // Try next
        }
    }

    throw new ArgumentOutOfRangeException("filename", "File does not contain import data in a known format.");
}


Hope this helps,
Fredrik
   
v2
Comments
udayRBA 1-Mar-12 7:50am
   
Thanks a lot Fredik. This has solved my problem. But I didn't understand why the error was there even if I was using same connection strings as you. The only change I did in my code is I changed the connection strings with yours and its working now.
Fredrik Bornander 1-Mar-12 8:21am
   
Glad I could help.
Thakkar Vipul 22-Feb-13 1:55am
   
Thanks it works for me

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900