Sync problem -- database getting updated, but _not_ DataSet??

[Frnak McKenney]

I'm using an in-core DataSet as an image of my application's
'database' (a multi-table Access97 mdb file). Updates are made to
the DataTables within the DataSet via forms with bound TextBoxes,
then written to the database... or at least that's what's supposed
to be happening.

Unfortunately, I've discovered that while it appears that when I
create a new record/row I'm successfully updating the Access
database, once the Update is complete the new record is not (is no
longer) present in the DataSet's DataTable. Ack!

It's not what we don't know that gives us trouble, it's what
we know that ain't so. -- Will Rogers

While it's _possible_ that I've invented the dotNET equivalent of
transporter technology and my DataSet is merely acting as a tachyon
sequencing buffer during the database updates, it's slightly more
likely that I'm missing some subtle (or not-so-subtle) part of the
dotNet DataSet-to-database sequence.

Here's what I'm doing under the impression that it's supposed to be
updating both the DataTable/DataSet AND the database.

Given a DataSet 'ds' and a table within that DataSet whose name is
contained in the variable 'table_name', the logic looks like:
---------
if ( ds.HasChanges() ) {
// (Odd if it doesn't)
DataTable dt_changes = ds[table_name].GetChanges();

// Make changes, but only if any exist
if (dt_changes != null) {
DataRowCollection drc = dt_changes.Rows; //**DEBUG**
if (dt_changes.Rows.Count > 0) {
try {
// Write changes back to database (.mdb file)
DbAdapters[table_name].Update(dt_changes);
} catch (DBConcurrencyException DBCe) {
ShowDbConcurrencyException(DBCe); // Report error
}
// Make changes permanent part of DataSet
ds.Tables[table_name].AcceptChanges();
}
}
}
---------

The data does get to the database file... it's just not visible to
either a DataView of the table or a VSDebug Watch of the DataTable
itself (as far as I can tell -- 'Watch'ing a DataTable with 150
items gets a little tricky <grin>)


Assuming that neither the OleDbDataAdapter.Update() nor the
DataTable.AcceptChanges() triggers an exception, _should_ the above
do what I think it should, that is, update both the in-core
DataTable AND the Access97 database? Or is there something subtle
I'm overlooking?

Or do I truly need to re-.Fill each table from the database every
time I issue an Update() to it? (Ack! Phlblbt!)


Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

 

[Guest]

Frank,

I think your problem lies with these statements:

DataTable dt_changes = ds[table_name].GetChanges();

- and -

DbAdapters[table_name].Update(dt_changes);

This would be ok to do if you were basically "rolling your own" update
stuff, but in using the .Update() method of the DataAdapter, I believe you
need to use the original DataSet or DataTable and not use the .GetChanges()
method.

~~Bonnie

 

[Frnak McKenney]

Bonnie,

Thank you for taking the time to reply.

I think your problem lies with these statements:

DataTable dt_changes = ds[table_name].GetChanges();

- and -

DbAdapters[table_name].Update(dt_changes);

This would be ok to do if you were basically "rolling your own" update
stuff, but in using the .Update() method of the DataAdapter, I believe you
need to use the original DataSet or DataTable and not use the .GetChanges()
method.

Oh. So if someone is using DataAdapter.Update() they'd have to re-write
the entire table? That's doable-but-not-efficient in my case, but for a
really large table I'd think it could approach "horrendous". Ig.

Anyway, it's certainly something I hadn't considered. I guess I'd better
re-read the OleDbDataAdapter.Update() documentation again.

Thanks for pointing that out to me. I'll let you know what happens.


Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

 

[Guest]

No no no ... it doesn't re-write the entire table. But the .Update() method
needs to have a before-and-after snapshot of the data (hence the whole
DataSet) in order to both update the database correctly (IOW, to know if
you're doing an Update, an Insert or a Delete) and to do the proper thing to
the DataSet when it's done.

~~Bonnie

Bonnie,

Thank you for taking the time to reply.

I think your problem lies with these statements:

DataTable dt_changes = ds[table_name].GetChanges();

- and -

DbAdapters[table_name].Update(dt_changes);

This would be ok to do if you were basically "rolling your own" update
stuff, but in using the .Update() method of the DataAdapter, I believe you
need to use the original DataSet or DataTable and not use the .GetChanges()
method.

Oh. So if someone is using DataAdapter.Update() they'd have to re-write
the entire table? That's doable-but-not-efficient in my case, but for a
really large table I'd think it could approach "horrendous". Ig.

Anyway, it's certainly something I hadn't considered. I guess I'd better
re-read the OleDbDataAdapter.Update() documentation again.

Thanks for pointing that out to me. I'll let you know what happens.


Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

 

[W.G. Ryan MVP]

Bonnie - he's actually doing this part right. He's using the GetChanges to
update the data but he's calling AcceptChanges on the original dataset.
That will cause the state of the datatable to be the same as if he had used
Update on it directly. But I still can't figure out his problem ;-(..

No no no ... it doesn't re-write the entire table. But the .Update()
method
needs to have a before-and-after snapshot of the data (hence the whole
DataSet) in order to both update the database correctly (IOW, to know if
you're doing an Update, an Insert or a Delete) and to do the proper thing
to
the DataSet when it's done.

~~Bonnie

Bonnie,

Thank you for taking the time to reply.

I think your problem lies with these statements:

DataTable dt_changes = ds[table_name].GetChanges();

- and -

DbAdapters[table_name].Update(dt_changes);

This would be ok to do if you were basically "rolling your own" update
stuff, but in using the .Update() method of the DataAdapter, I believe
you
need to use the original DataSet or DataTable and not use the
.GetChanges()
method.

Oh. So if someone is using DataAdapter.Update() they'd have to re-write
the entire table? That's doable-but-not-efficient in my case, but for a
really large table I'd think it could approach "horrendous". Ig.

Anyway, it's certainly something I hadn't considered. I guess I'd better
re-read the OleDbDataAdapter.Update() documentation again.

Thanks for pointing that out to me. I'll let you know what happens.


Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)