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