C # oledbexception

Hi guys I would like to thank all of you for the great information you guys provide. I always use stackoverflow but never asked questions. Anyway I am working on a C # project to read data from excel files into an Access database. I am getting a type exception OleDbException

. Now the problem is not why I am getting this error, but how to handle it. I am getting the error because I am letting the user decide which file they want to upload, and some files may have the wrong headers or format. Here is the code I'm using: the line with ** is what throws the exception. I've tried using:

  • catch (OleDbException)

  • catch {}

  • catch (Exception)

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

thank

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.");
    }
 }

      

+3


source to share


1 answer


It could either be a problem with your connection string, or you don't have the ACE.OLEDB library installed, so OleDB cannot find the provider you want. Have a look at this page for alternative connection strings or you can download the provider from.

You can try this:



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);
}

      

is equivalent to try / finally and will ensure proper cleanup of the connection, command, and IDataReader objects. The catch block should catch (almost) any exception thrown by this code.

+1


source







All Articles