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.