SetModified not persisting to db.

[marc.l.caron]

It appears to me that using the SetModified() doesn't force the data
changes into the database. I'm thinking it is doing comparisons
between the DataRowVersion's. I say this because of my following
problem.

I have a scenario where the best way to handle the data is to take
datatable table1. While processing it's contents if a row exhibits
problems it has a status field and description updated and the changes
accepted. (to the datatable only) then the row is Deleted (again in
the data table only)

All "deleted" rows are then pulled out of table1
via .GetChanges(DataRowState.Deleted) and it results in a deletedItems
table.

DataTable deletedItems = table1..GetChanges(DataRowState.Deleted);

Now what I actually need to do is apply the changes made to these
deleted rows to the database. So first I reject all the deletes.
Then mark every row as modified. I then call update on the data
adapter. The call to update returns the correct # of modified rows
but nothing is persisted to the database. So here's some simple code
to show the concept.

<code>
foreach (DataRow row in table1)
{
if (every 10 rows)
{
row["Status"] = "X";
row["Description"] = "This is a 10th row";
row.AcceptChanges(); // If we don't do this here the the above 2
values are not accessable after the delete
row.Delete();
}
}

DataTable deleteditems = table1.GetChanges(DataRowState.Deleted);
table1.AcceptChanges();
deleteditems.RejectChanges();

foreach (DataRow row in deleteditems)
{
row.SetModified();
}

int modified = table1DataAdapter.Update(deleteditems);
// modified comes out right. But no changes get to the database.
</code>

Obviously my reason for marking the rows deleted is way more complex
then that but that gives the idea.

Anyone have any ideas? I've been working on this all day and can't
find a reason why nothing updates.

thanks

 

[Cor Ligthert[MVP]]

Marc,

With getChanges, you get an array of references to the rows that are
changed. It stays however the same rows and anything you do on it will
applied to it.

I hope that I explain it understandable with this.

Cor

 

[marc.l.caron]

Also if I do the Update with the original table with the rows marked
Deleted still they are properly deleted from the database. Not what I
want obviously but it shows that the DataAdapter is functioning.

So here's new stuff I've tried with pulling the deleted rows out to a
new table still.

Marking as changed -> adapter.Update -> Operation succeeds but no
changes in db.

Setting data fields that I care about = to themselves ->
adapter.Update -> Operation succeeds but no changes in db.

Copy the 2 values out to temps -> Set the columns to bogus values ->
Accept changes on row -> Set column values back to originals (repeat
for all rows) -> adapter.Update (all rows @ once) -> Operation fails
with concurrency error that 0 of expected 1 rows were updated.



For the moment I'm going to Hack it by generating and executing UPDATE
statements directly. This seems it should be an easy operation
however, so it's frustrating me.

 

[marc.l.caron]

So I found a solution.

I need to manually provide the UPDATE command object/SQL to accomplish
what I want. The SQL builder won't manage it for me. So I move the
important fields off to a second table that has just the Key and
important fields. Custom dataadapter that got schema based off a
select of those fields and an UPDATE command with bound parameters.

And that worked!