Click here to Skip to main content
13,703,000 members
Rate this:
 
Please Sign up or sign in to vote.
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
Updated 14-Jul-11 22:07pm
v4
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
v2
Comments
unknowndentified10111 15-Jul-11 4:38am
   
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?
Joezer BH 10-Sep-13 3:34am
   
5ed!
OriginalGriff 15-Jul-11 4:54am
   
Answer updated
unknowndentified10111 15-Jul-11 4:59am
   
Yeah! It works like magic. Thank you very much!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Here's a tip: If you keep your .xls file open in Excel while the code is executing, this error might occur.
  Permalink  
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?

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180920.1 | Last Updated 7 Sep 2013
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100