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:
catch (OleDbException)
catch {}
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.");
}
}
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.