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

i error while trying update field(s) in excel sheet using oledb.

this work sometimes, error. (operation must use 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) {         {         thread.sleep(50);     } while (myconnection.state != connectionstate.open); } 

here code performs update:

linesx = "231,214,412"; //this variable string sqlx = string.format("update [resolved results$] set [audit result] = 'audited' [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: works , not, same value of linesx

i monitor status of connections make sure connection closed before being opened, , connection open before command executed. have tried multiple ways solved including : having separate connection read (con1) , update commands(myconnection ), (ofcourse, make sure con1 closed before open myconnection)

i have researched plenty issue , answer there no write permissions file. however, program have write permissions (as update command work , not)

sometimes update command work after has failed once, (i.e try update twice or thrice) , doesnt.

why happen?? how fix this??

update 1: tried using microsoft.ace.oledb.12.0 insted of jet - error doesnt appear doesnt update table either!!!

update 2: using multiple connections same file, might have caused problem. connection global variable. changed whole application architecture initialize connection when required, damith said , worked charm (it works 99% of times( worth time took implement change in entire application.

thank damith

i hope helps other 2^n people facing same problem :d

update 3: think know fix other 1% - there functions create multiple threads, , each thread opens own connection - maybe that's causing problem. solution make sure there 1 , 1 connection excel db. (not connectionstate.close, con variable should out of scope , null)

do below, create connection when need it. using block handle 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' [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;     } }