Operation must use an updateable query - Excel 2007 file as OleDB Data source

Question

I often get this error while trying to update a field(s) in an excel sheet using OLEDB.

This does work sometimes, but I often get this error. (Operation must use an updateable query)

Connection Code:

string filepath = "c:\\SampleFile.xls";
OleDbConnection MyConnection;
MyConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filepath + "';Extended Properties=Excel 8.0;");
con3 = MyConnection;
MyConnection.Open();
if (MyConnection.State != ConnectionState.Open)
{
    do
    {
        Thread.Sleep(50);
    } while (MyConnection.State != ConnectionState.Open);
}

Here is the code that performs the update:

linesx = "231,214,412"; //this is variable
string sqlx = string.Format("Update [Resolved Results$] set [Audit Result] = 'AUDITED' where [LineNo] IN ({0})", linesx);
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = MyConnection;
myCommand.CommandText = sqlx;
try
{
    Console.WriteLine(sqlx);
    myCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
    updRes = "Failed! :("+Environment.NewLine+"ERROR: " + ex.Message + Environment.NewLine;                        
} 

NOTE: This sometimes works and sometimes not, for the SAME value of linesx

I also monitor the status of connections to make sure that the connection is closed before it is being opened, and that the connection is open before the command is executed. I have tried multiple ways to solved it including : having a separate connection for read (con1) and update commands(MyConnection ), (ofcourse, I make sure con1 is closed before I open MyConnection)

I have researched plenty about this issue and the answer that i could get the most is that there is no write permissions for the file. HOwever, the program does have write permissions (As the update command does work sometimes and sometimes not)

Sometimes the update command does work after it has failed once, (I.e i try to update twice or thrice) and sometimes it doesnt.

Why does this happen?? How do I fix this??

UPDATE 1: I tried using Microsoft.ACE.OLEDB.12.0 insted of the JET - The error doesnt appear but it doesnt update the table either!!!

UPDATE 2: i was using multiple connections to the same file, which might have caused the problem. the connection was a global variable. I changed the whole application architecture to initialize the connection only when required, like Damith said and it worked like a charm (It works 99% of the times( It was well worth the time i took to implement this change in my entire application.

Thank you very much Damith

I hope this helps the other 2^n people who are facing the same problem :D

UPDATE 3: I think i know to fix the other 1% - there are functions that create multiple threads, and each thread opens its own connection - maybe that's causing the problem. The solution is to make sure there is one and only one connection to the excel DB. (not necessarily ConnectionState.Close, but the con variable should be out of scope and null)

Answer 1

do as below, create connection when need it. using block handle the disposing of connection object.

var connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filepath + "';Extended Properties=Excel 8.0;";
var sqlx = string.Format("Update [Resolved Results$] set [Audit Result] = 'AUDITED' where [LineNo] IN ({0})", linesx);
using (var connection = new OleDbConnection(connectionString))
using (var command = new OleDbCommand(sqlx , connection))
{
    try
    {
        connection.Open();
        command.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        updRes = "Failed! :("+Environment.NewLine+"ERROR: " + ex.Message + Environment.NewLine;
    }
}