C# oledbexception

Go To StackoverFlow.com

3

Hi Guys I would like to first thank all of you for the great information you guys provide. I always use stackoverflow but never asked a question. Anyway I am working on a C# project to read data from excel files into a Access database. I keep getting a exception of type OleDbException. Now the issue is not why I get that error but how to handle it. I get the error because I let the user decide which file they want to upload and some files might not have the correct headers or format. Here is the code I am using: the line with ** is what throws the exception. I have tried using:

  1. catch (OleDbException)
  2. catch {}
  3. catch (Exception)

but it seems that the exception is never thrown to my catch clause.

Thank You

public UploadGrades(string filename, OleDbConnection con)
{
    this.filename = filename;
    this.con = con;
    //connect to the file and retrive all data.
    excelconn = new OleDbConnection(
     @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"");;

    try
    {
        excelconn.Open();
        OleDbCommand command = new OleDbCommand("SELECT temp, name, score, submitdate, test from [sheet1$]", excelconn);
        **reader = command.ExecuteReader();**
    }
    catch 
    {
        MessageBox.Show("The File " + filename + " cann't be read.  It is either in use by a different user \n or it doen't contain the " +
            "correct columns.  Please ensure that column A1 is temp B1 is Name C1 is Score D1 is Submitdate and E1 is Test.");
    }
 }
2012-04-05 20:58
by Mohamed
Why are you passing a connection to the method and then creating a new one? Also, are you sure you are reaching the reader.ExecuteReader() line? Your connection may be timing out, so you never actually reach the ExecuteReader line, and, if the connection timeout is long enough, you may close the app before an exception is ever thrown. Or it might be thown when you create the connection, outside of the try catch - dash 2012-04-05 21:01
move the try up a few lines then do catch(Exception ex) { messagebox.show(ex.message) - bluepoisondartfrog 2012-04-05 21:07
In fact, try changing your connection instantiation to: new OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"", filename)); too. You might also want to test the file exists, and is an excel file for completeness - dash 2012-04-05 21:09
@iterationx is likely to be right. Otherwise, windows is doing something stupid, wouldn't be the first time for that mind - Tony Hopkinson 2012-04-05 21:11
In the VS menu: Debug + Exceptions, untick the boxes. Now your catch handler will work : - Hans Passant 2012-04-05 21:25
Wow thanks for the fast response I wasn't expecting it in like 5 minutes. Anyway I figured out the issue. I had the setting to break whenever any exceptions occurs to on. Does that make sense if not when you go to debug-->exceptions-->you will see what I am talking about. Now it works perfect. You see before when the exception occurs the app will break and display the exception. Also The connection I am passing is the one for the access database and the one I am creating is the one for the excel spreadshee - Mohamed 2012-04-05 21:26


1

It may either be a problem with your connection string, or you don't have the ACE.OLEDB library installed, so OleDB cannot find the right provider. Look at this this page for alternative connection strings or you should be able to download the provider from here.

You may want to try the following:

try
{

       using(OleDbConnection excelConnection = new OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"", filename)))
       {
        excelConnection .Open();     
          using(OleDbCommand command = new OleDbCommand("SELECT columbia_uni, name, score, submitdate, test from [sheet1$]", excelconn))
          {     
                 command.CommandType = CommandType.Text;
                 using(IDataReader reader = command.ExecuteReader())
                 {
                    while(reader.Read())
                    {
                      //Do something
                    }
                 }    
          }
       }


}
catch(Exception e)
{
    MessageBox.Show("The File " + filename + " cann't be read.  It is either in use by a different user \n or it doen't contain the correct columns.  Please ensure that column A1 is Columbia_UNI B1 is Name C1 is Score D1 is Submitdate and E1 is Test.\r\n The actual exception message is: " + e.Message);
}

using is equivalent to try/finally and will ensure the connection, command, and IDataReader objects are appropriately cleaned up. The catch block should catch (almost) any exception generated by this code.

2012-04-05 21:15
by dash
Wow thanks for the fast response I wasn't expecting it in like 5 minutes. Anyway I figured out the issue. I had the setting to break whenever any exceptions occurs to on. Does that make sense if not when you go to debug-->exceptions-->you will see what I am talking about. Now it works perfect. You see before when the exception occurs the app will break and display the exception - Mohamed 2012-04-05 21:24
Hah - yes, that one. You are actually stopping on the underlying exception! I would still advise you to use a using statement, however, to properly manage and dispose the objects. I would also advise against holding a connection object in a field, but that will depend on your design - dash 2012-04-05 21:25
Ads