3

I'm using the following code to update a cell in an Excel file.

public bool WriteChange(string Filename, string SheetName, string Cell, string Value)
{
    if(!System.IO.File.Exists(Filename))
    {
        throw new System.IO.FileNotFoundException("File \"" + Filename + "\" could not be found");
    }
    bool result = false;
    string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Filename + ";Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=NO;\"";
    string SQL = "UPDATE [" + SheetName + Cell + ":" + Cell + "] SET F1='" + Value + "'";

    using(OleDbConnection Connection = new OleDbConnection(ConnectionString))
    {
        Connection.Open();
        using(OleDbCommand cmd = new OleDbCommand(SQL,Connection))
        {
            int value = cmd.ExecuteNonQuery();
            if(value > 0)
            {
                result = true;
            }
        }
    }
    return result;
}

Which works fine, unless I try to update the same cell multiple times. Once a cell has been updated using this function, it can never be updated using this function again. If I try to update the cell again; even after restarting the application, I get an OleDbException: System Resource Exceeded.

I know you typically receive this exception if you are creating a bunch of connections to the spreadsheet (in a loop for example), but I am only connecting once per application run. The typical work flow is.

  • Start application.
  • Call WriteChange.
  • Exit application.

Why am I getting this error, when the connection should be long dead by the time I try to connect again?

Tester101
  • 8,042
  • 13
  • 55
  • 78
  • maybe this can help you http://stackoverflow.com/questions/155959/oledbexception-system-resources-exceeded – Brijesh Mishra Apr 25 '12 at 15:12
  • @BrijeshMishra As I stated in the question, I would expect this exception if I was creating a bunch of connections. In my case, however, I am only creating one connection. – Tester101 Apr 25 '12 at 15:27
  • is it a web application? if not tried running it on some other machine to rule out problem with driver? – Brijesh Mishra Apr 25 '12 at 16:10
  • Most peculiar. I duplicated your code exactly and had no problems writing to the same cell multiple times. I am using VS2010, Windows 7, Excel 2010 with workbook saved to 2003 (.xls) format. – Stewbob Apr 25 '12 at 16:55
  • @Stewbob Odd. I used a different spreadsheet, and I can't duplicate the problem either. Must have broken the spreadsheet some how. – Tester101 Apr 25 '12 at 17:05

2 Answers2

0

Use following connection string because Microsoft.Jet.OLEDB.4.0 has this error.

string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @Filename + ";Extended Properties=\"Excel 12.0;HDR=No;\"";
Romil Kumar Jain
  • 20,239
  • 9
  • 63
  • 92
0

I think the error may have occured because you are not closing the OleDbconnection connection. Close your Dbconnection after completing your update query -- `int value = cmd.ExecuteNonQuery();

Finisher001
  • 455
  • 1
  • 5
  • 18