Reading Excel File(.xls): Unspecified Error

Question

1.00/5 (1 vote)

See more:

Hello All,

I have an application that reads an excel file(.xls) but occasionally an error is thrown: Unspecified Error.

I use an OleDbConnection to open a connection.
OleDbCommand to execute a query. And DbDataReader to read the rows.

My program logic is like this:
1. Open the excel file
2. Read each row
3. Validate each column - some validation may require opening the connection again
4. Save it to the database when no errors occur

When I upload a small file(less than 70 rows) the error is never thrown.
But when I upload a large file(71 rows and above)the error shows.

This is where the error occur:

 string myconnection1 = @"provider=Microsoft.Jet.OLEDB.4.0; data source=" + _excelFile + ";Extended Properties='Excel 8.0;IMEX=1';";
OleDbConnection Connection1 = new OleDbConnection(myconnection1);
OleDbCommand mycommand1 = new OleDbCommand("Select * FROM [MASTERLIST FORMAT$] where F6 = '" + _insured.StrDepLname + "' and F7 = '" + _insured.StrDepFname + "' and F8 = '" + _insured.StrDepMname + "' ", Connection1);
Connection1.Open();
DbDataReader dr1 = mycommand1.ExecuteReader();
while (dr1.Read())
{
   string civilStatus = dr1[9].ToString();
   if (civilStatus == "Single")
   {
       error = "" + _insured.StrDepLname + " " + _insured.StrDepFname + " is set to SINGLE Civil Status but has Spouse";
   }
 }

It also occur in some of my codes that opens the connection and read the excel file.
My validation is inside a loop statement and codes like this may occur a, I think 8 times each row.

Please help me.

Thanks in Advance.

Posted 14-Jul-11 21:13pm

unknowndentified10111

Updated 14-Jul-11 22:07pm

v4

Add a Solution

Comments

OriginalGriff 15-Jul-11 3:17am

Please show use the relevant code fragment: without it we would just be guessing...
Use the "Improve question" widget to edit your question and provide better information.

unknowndentified10111 15-Jul-11 3:41am

I don't really think there has an error on my code(I had checked it a million times).

OriginalGriff 15-Jul-11 3:52am

Hence the word "Fragment" - post only the bit that generates the error.
Either, your code is perfect and the error is an act of god (in which case learn to live with it) or it isn't : either way, some part of your code is raising an exception. Post that bit, and (say) 15 or so lines either side to give us context.

unknowndentified10111 15-Jul-11 4:08am

I did not mean to offend you. :P I'm just like this. Sorry.

OriginalGriff 15-Jul-11 4:22am

You didn't offend...but you shouldn't do it like that anyway: look up SQL Injection Attack and then use Parametrized queries instead. Won't solve this problem, but it should prevent much, much more serious ones in the future.

2 solutions

Solution 2

Here's a tip: If you keep your .xls file open in Excel while the code is executing, this error might occur.

Posted 14-Jul-11 22:33pm

Shameel

Comments

unknowndentified10111 15-Jul-11 4:45am

I also thought that but, there's no other way to do it(except to insert all excel values in a DataTable which cost a lot of work). And why 'might'? not always?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2011-07-14T22:25:00

OK, the code you have posted doesn't look too bad (apart from the SQL Injection Attack caveat I mentioned).
So, first things first.
Have you isolated that code, and tried it without the rest of your program, just to eliminate any other factors?
How many rows do you expect it to return? How many columns? These shouldn't be relevant, but at this stage it is difficult to tell.
Is there an inner exception?
Exactly which line is it excepting on?

"The inputs(_insured.StrDepFname ...) is from the excel file too. Yes, it is subject to sql injections, but even if the values is correct the error still occur. I haven't really checked what line the error occur because it loops too many time before an exception is thrown. Even if the inputs are all the same, still the error occur not in the first execution of the loop but on the n? times the statements inside the loop had already been executed. Is it because of reconnecting in OleDb too many times?"

Ah! You do the above code in a loop?

Try this: put each of your OleDbConnection and OleDbCommand objects into a using block:

using (OleDbConnection Connection1 = new OleDbConnection(myconnection1))
   {
   using (OleDbCommand mycommand1 = new OleDbCommand("Select * FROM [MASTERLIST FORMAT$] where F6 = '" + _insured.StrDepLname + "' and F7 = '" + _insured.StrDepFname + "' and F8 = '" + _insured.StrDepMname + "' ", Connection1))
      {
      Connection1.Open();
      ...
      }
   }

And make sure that all your other such are as well. I think your problem will magically disappear...

If it does, it is because the connections are scarce resources, which are running out well before the Garbage collector can Dispose them. The using block forces an immediate Dispose when you are finished with them so they cfan be re-used immediately.