asked on

ASP.NET System.Data.OleDb.OleDbException: Unspecified error reading Excel file

I have the following code in a web service that attempts to open an excel file for reading:
System.Data.OleDb.OleDbConnection xcn = new System.Data.OleDb.OleDbConnection();
xcn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;IMEX=1\"";
xcn.Open();

In our production environment this starts to fail once or twice a day and the only way I can fix is with IISRESET /RESTART. Here is the error I get:

System.Data.OleDb.OleDbException: Unspecified error
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection...

I have checked permissions on various temp folders that JET supposedly uses, and permissions are fine. The most frustrating thing is that it will work for several times in a row, and then start failing every time (until IISREST).

Any thoughts?

MogalManic

Are you closing the connection when you finished?

I'm not sure how ODBC and Excel work with connection pooling, but if you open the Excel file multiple times, eventually you will run out of file handles and the connection will fail. The .Net garbage collector will close the connections, but it will happen randomly and you will have no control on when that will happen.

JCS2009

ASKER

Yes, I am calling xcn.Close() at the end of the code that reads from the Excel file.

MogalManic

Try turning off connection pooling by adding ' pooling = false' to your connection string. So it will now be:

System.Data.OleDb.OleDbConnection xcn = new System.Data.OleDb.OleDbConnection();
xcn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;IMEX=1\";pooling=false;";
xcn.Open();

JCS2009

ASKER

I will try this in production as soon as I can and report back. I can't find documentation on that extended property. Can you point me to some? Thanks for the suggestion.

ASKER CERTIFIED SOLUTION

MogalManic

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

antonybrahin

I am going through this problem..

does setting "pooling=false" fixed the issue?