i use oledbdataadapter , oledbcommandbuilder fill dataset object database contents, , update database according changes made in dataset. problem exception: "concurrency violation: updatecommand affected 0 of expected 1 records". i've found explanation of error:
because record have been modified after returned select statement, before update or delete statement issued, automatically generated update or delete statement contains clause, specifying row updated if contains original values , has not been deleted data source. automatically generated update attempts update row has been deleted or not contain original values found in dataset, command not affect records, , dbconcurrencyexception thrown.
that means auto generated update command affected 0 rows in database. work paradox(db-file) database , no 1 changes except me. guess program changes same row 2 times somewhere. wanted debug program executing generated queries manually , finding 1 doesn't affect row(because i'm pretty sure changes made once , bug somewhere else))). possible run auto generated commands manually?
my code big , complicated post here works this(i made working project , took there)
using system; using system.data; using system.windows.forms; using system.data.oledb; namespace oledbcommandbuilder { public partial class form1 : form { public form1() { initializecomponent(); } private void button1_click(object sender, eventargs e) { string cs = @"provider=microsoft.jet.oledb.4.0;"; cs += @"data source=c:\folder\1\spr_kmz\;"; cs += @"extended properties=paradox 5.x;"; oledbconnection connection = new oledbconnection(); connection.connectionstring = cs; try { connection.open(); } catch (exception ex) { messagebox.show("error openning database! " + ex.message, "error", messageboxbuttons.ok, messageboxicon.error); environment.exit(0); } string sqlquery = "select * spr_kmz rez<>0"; dataset spr_kmz = new dataset(); oledbdataadapter dataadapter = new oledbdataadapter(); dataadapter.selectcommand = new oledbcommand(sqlquery, connection); oledbcommandbuilder builder = new oledbcommandbuilder(dataadapter); try { dataadapter.fill(spr_kmz); } catch (exception ex) { system.windows.forms.messagebox.show(string.format("error \n{0}\n{1}", ex.message, sqlquery)); environment.exit(0); } datarow[] spr_kmz_rows = spr_kmz.tables[0].select("fkmz=10000912 , rez=1"); foreach (datarow spr_kmz_row in spr_kmz_rows) { spr_kmz_row["dn"] = convert.todatetime("30.12.1899");//26.12.2008 spr_kmz_row["price"] = convert.todouble(0);//168,92 } dataadapter.update(spr_kmz); system.windows.forms.messagebox.show("success!"); environment.exit(0); } } } p.s. updated database without concurrency exception, after lot of changes(i commented out line "dataadapter.update(spr_kmz);" long time debugging reason, don't know when error started throw)
p.s.s. there no inserts or deletes in code, updates...
<<update>>
i've found problem: if "dn" field has null value after changing it, auto-generated update statement don't affect anything, because "dn" contained in primary key , command builder didn't expect primary key field have null values(who ever would))), no surprise engine called "paradox")))
that's why in
commandbuilder.getupdatecommand().commandtext in clause "dn" field there kind of pattern:
... ((rez = ?) , (dn = ?) , ... while nullable fields described this:
... , ((? = 1 , price null) or (price = ?)) , ((? = 1 , nmed null) or (nmed = ?)) , ... p.s.s.s. hey, can try set updatecommand manually fix this!)))
here how i've managed set updatecommand manually , sql code every update command being executed!(more or less)). helpful while debugging - can see sql query failed execute during dataadapter.update(dbdataset) command.
public void update(dataset dbdataset) { dataadapter.rowupdating += before_update; dataadapter.update(dbdataset); } public void before_update(object sender, eventargs e) { //convert eventargs oledbrowupdatingeventargs able use oledbcommand property system.data.oledb.oledbrowupdatingeventargs oledb_e = (system.data.oledb.oledbrowupdatingeventargs) e; //get query template string cmd_txt = oledb_e.command.commandtext; //modify query template here fix //cmd_txt = cmd_txt.replace("table_name", "\"table_name\""); //fill tamplate values string cmd_txt_filled = cmd_txt; foreach(system.data.oledb.oledbparameter par in oledb_e.command.parameters) { string par_type = par.dbtype.tostring(); string string_to_replace_with = ""; if (par.value.gettype().name == "dbnull") { string_to_replace_with = "null"; } else { if (par_type == "int32") { par.size = 4; string_to_replace_with=convert.toint32(par.value).tostring(); } else if (par_type == "double") { par.size = 8; string_to_replace_with=convert.todouble(par.value).tostring().replace(",","."); } else if (par_type == "datetime") { par.size = 8; /* in paradox sql queries can't specify date string, * result in incompatible types, have count days * between 30.12.1899 , required date , specify number */ string_to_replace_with = datetoparadoxdays(convert.todatetime(par.value).tostring("dd.mm.yyyy")); } else if (par_type == "string") { string_to_replace_with = '"' + convert.tostring(par.value) + '"'; } else { //break execution if field has type not handled here system.diagnostics.debugger.break(); } } cmd_txt_filled = replacefirst(cmd_txt_filled, "?", string_to_replace_with); } cmd_txt_filled = cmd_txt_filled.replace("= null", "is null"); //get query text here test in database manager //system.diagnostics.debug.writeline(cmd_txt_filled); //uncomment apply modified query template //oledb_e.command.commandtext = cmd_txt; //uncomment run prepared update command //oledb_e.command.commandtext = cmd_txt_filled; } public string replacefirst(string text, string search, string replace) { int pos = text.indexof(search); if (pos < 0) { return text; } return text.substring(0, pos) + replace + text.substring(pos + search.length); } private static string datetoparadoxdays(string date) { return (convert.todatetime(date) - convert.todatetime("30.12.1899")).totaldays.tostring(); } 