Deleted Records and DataAdapter.Fill

Dear All,

Question
Why DataAdapter.Fill does not refresh the deleted records in the dataset?
If this is by design then how can I refresh the data and get rid of removed
records without actually refilling the dataset with clear and fill
operations?

Detailed
I have a DataAdapter refering to a table in database. The data is being
shown in a DataGrid. As the data is updated, in the table (from other
connection), it is reflected in the grid by a call to the Fill method of the
DataAdapter. All is well , but if some row is deleted then the call to Fill
method does not delete the record from its underlying DataSet. I have come
to know that calling clear method is an option but it seems so dirty -
clearing all the data and then getting it again.

Can someone plz guide Why it is done this way and what is the resolution.

Thank You

Cor
Mon May 31 08:08:16 CDT 2004

Hi Rawcoder

Is this a Web or a Windowform datagrid?

For a windowform datagrid this sounds strange, as soon as you delete a row
from the datatable, it should be reflect in a windowform datagrid.

However when you refill that, they have to be correctly updated in the
database of course.

I hope this helps?

Cor

>
> Question
> Why DataAdapter.Fill does not refresh the deleted records in the dataset?
> If this is by design then how can I refresh the data and get rid of
removed
> records without actually refilling the dataset with clear and fill
> operations?
>
> Detailed
> I have a DataAdapter refering to a table in database. The data is being
> shown in a DataGrid. As the data is updated, in the table (from other
> connection), it is reflected in the grid by a call to the Fill method of
the
> DataAdapter. All is well , but if some row is deleted then the call to
Fill
> method does not delete the record from its underlying DataSet. I have come
> to know that calling clear method is an option but it seems so dirty -
> clearing all the data and then getting it again.
>
> Can someone plz guide Why it is done this way and what is the resolution.
>
> Thank You
>
>

William
Mon May 31 13:26:43 CDT 2004

If all of the deletion is being done client side, why pull over two separate
instances of the same datatable. Why not just share it and let the deleting
function work on the same table as the one in the grid. You can easily
share a table by making it a static property for instance (shared in
VB.NET). As soon as the local deletion is done, it will be reflected in
both places. When you call update from one, it will immediately affect the
other.

This has a few benefits
1) Solves this problem
2) Saves the overhead associated with calling to Select statement and
filling both of them
3) Keeps everything in sync, much easier to code and maintain.

HTH,

Bill

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
"rawCoder" <rawCoder@hotmail.com> wrote in message
news:uBU%23euvREHA.2408@tk2msftngp13.phx.gbl...
> Dear All,
>
> Question
> Why DataAdapter.Fill does not refresh the deleted records in the dataset?
> If this is by design then how can I refresh the data and get rid of
removed
> records without actually refilling the dataset with clear and fill
> operations?
>
> Detailed
> I have a DataAdapter refering to a table in database. The data is being
> shown in a DataGrid. As the data is updated, in the table (from other
> connection), it is reflected in the grid by a call to the Fill method of
the
> DataAdapter. All is well , but if some row is deleted then the call to
Fill
> method does not delete the record from its underlying DataSet. I have come
> to know that calling clear method is an option but it seems so dirty -
> clearing all the data and then getting it again.
>
> Can someone plz guide Why it is done this way and what is the resolution.
>
> Thank You
>
>

rawCoder
Tue Jun 01 01:04:46 CDT 2004

Ok please let me clear out the scenario here.

I have making kinda reporting tool (WinForm) which uses Extended Stored
Procedure and Triggers to get notified when there is a change in the
Database in certain table. When the notification (via Socket) arrives i call
the dataadapter.Fill method ... which works fine for addition and edition of
rows. But when there is a row/record deleted, the Fill method just deos
nothing and keeps showing the deleted row unlesss I .Clear the dataset (
which i think is not such a pretty way ).

So I repeate the question - Is there any way to avoid the .Clear & .Fill
strategy? and out of curiosity why does DataAdapter's Fill method act this
way?

Thank You

Cor
Tue Jun 01 04:10:03 CDT 2004

Hi RawCoder

The scenario with a dataset is very raw

Fill dataset
Update dataset
Fill existing dataset to refresh

Here is a page which shows that very nice.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconupdatingdatabasewithdataadapterdataset.asp

However I get the idea that you are doing the update using an other method?

Cor

Ken
Tue Jun 01 08:22:37 CDT 2004

No, I see his point, and a quick experiment (and a check of the
documentation) indicates there may be an issue here.

1. Client A requests information from the database and retrieves a DataSet.
2. Client B requests the same information and gets the same DataSet.
3. Client A makes some changes, and most notably deletes record '123'.
4. Client A generates a new DataAdpater with the same SELECT, uses Fill() to
update the DataSet, and issues an Update(), which deletes record '123' from
the database (along with any other changes).
5. Client B makes some changes to the DataSet, but does not affect record
'123'.
6. Client B generates a new DataAdapter with the same SELECT and uses Fill()
in preparation for the Update() call -- but the record '123' remains in the
DataSet!

-ken

"William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
news:Oun$qwzREHA.3716@TK2MSFTNGP09.phx.gbl...
> If all of the deletion is being done client side, why pull over two
separate
> instances of the same datatable. Why not just share it and let the
deleting
> function work on the same table as the one in the grid. You can easily
> share a table by making it a static property for instance (shared in
> VB.NET). As soon as the local deletion is done, it will be reflected in
> both places. When you call update from one, it will immediately affect
the
> other.
>
> This has a few benefits
> 1) Solves this problem
> 2) Saves the overhead associated with calling to Select statement and
> filling both of them
> 3) Keeps everything in sync, much easier to code and maintain.
>
> HTH,
>
> Bill
>
> --
>
> W.G. Ryan, eMVP
>
> http://forums.devbuzz.com/
> http://www.knowdotnet.com/williamryan.html
> http://www.msmvps.com/WilliamRyan/
> http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
> "rawCoder" <rawCoder@hotmail.com> wrote in message
> news:uBU%23euvREHA.2408@tk2msftngp13.phx.gbl...
> > Dear All,
> >
> > Question
> > Why DataAdapter.Fill does not refresh the deleted records in the
dataset?
> > If this is by design then how can I refresh the data and get rid of
> removed
> > records without actually refilling the dataset with clear and fill
> > operations?
> >
> > Detailed
> > I have a DataAdapter refering to a table in database. The data is
being
> > shown in a DataGrid. As the data is updated, in the table (from other
> > connection), it is reflected in the grid by a call to the Fill method of
> the
> > DataAdapter. All is well , but if some row is deleted then the call to
> Fill
> > method does not delete the record from its underlying DataSet. I have
come
> > to know that calling clear method is an option but it seems so dirty -
> > clearing all the data and then getting it again.
> >
> > Can someone plz guide Why it is done this way and what is the
resolution.
> >
> > Thank You
> >
> >
>
>

Cor
Tue Jun 01 08:38:10 CDT 2004

Hi Ken,

Did you see the message I was answering to RawCoder today?

In your message I do not understand this.
>
> 1. Client A requests information from the database and retrieves a
DataSet.
> 2. Client B requests the same information and gets the same DataSet.
> 3. Client A makes some changes, and most notably deletes record '123'.
> 4. Client A generates a new DataAdpater with the same SELECT, uses Fill()
to

Why has he to do a Fill before the update the dataset?

> update the DataSet, and issues an Update(), which deletes record '123'
from
> the database (along with any other changes).
> 5. Client B makes some changes to the DataSet, but does not affect record
> '123'.
> 6. Client B generates a new DataAdapter with the same SELECT and uses
Fill()

The same as above?

> in preparation for the Update() call -- but the record '123' remains in
the
> DataSet!
>

Ken
Wed Jun 02 05:54:55 CDT 2004

Actually, this came from another thread that I was reading and for which I
had not completely worked out the logic. The basic premise is that when
multiple clients can update the same database table, each update from a
single client needs to retrieve the changes made by the other clients as
well.

For the purpose of this discussion it does not matter whether the Fill() is
called before or after the Update() call -- although it will likely have an
impact on the actual changes committed to the database -- the point is that
the Fill() call to an existing DataSet is supposed to merge the existing
contents into the existing DataSet, but it does not seem to deal with
records deleted by another client! I suspect that one really needs to do is
commit their own changes via Update() and then replace the contents of the
entire DataSet (or at least the affected DataTable).

There are some larger issues here that I will address in a separate thread.

-ken

"Cor Ligthert" <notfirstname@planet.nl> wrote in message
news:%23eun629REHA.2552@TK2MSFTNGP11.phx.gbl...
> Hi Ken,
>
> Did you see the message I was answering to RawCoder today?
>
> In your message I do not understand this.
> >
> > 1. Client A requests information from the database and retrieves a
> DataSet.
> > 2. Client B requests the same information and gets the same DataSet.
> > 3. Client A makes some changes, and most notably deletes record '123'.
> > 4. Client A generates a new DataAdpater with the same SELECT, uses
Fill()
> to
>
> Why has he to do a Fill before the update the dataset?
>
> > update the DataSet, and issues an Update(), which deletes record '123'
> from
> > the database (along with any other changes).
> > 5. Client B makes some changes to the DataSet, but does not affect
record
> > '123'.
> > 6. Client B generates a new DataAdapter with the same SELECT and uses
> Fill()
>
> The same as above?
>
> > in preparation for the Update() call -- but the record '123' remains in
> the
> > DataSet!
> >
>
>

Cor
Wed Jun 02 07:10:24 CDT 2004

Hi Ken,

In my opinion is the normal sequence

Fill 'clean dataset
update 'which does at the end acceptchanges
Fill 'refresh dataset with information changed by other users (and yourself
by autokey)
update
Fill 'refresh dataset

Cor

rawCoder
Thu Jun 03 00:02:33 CDT 2004

Thanx Allen,Lighthert and Ryan

Well all this discussion guides me to the usage of Data Adapter in case of
single user and in case of multi user the DataAdapter's Fill method actually
MERGES the data so the rows deleted by one user are not updated in other
users DataSet. And that to refresh the deleted rows one needs to clear the
datatable in this scenario.

right ???

Well then its .Clear to the rescue I guess. Although i will hate to use it.
I still wonder why this design was chosen by MSFT ... i hope it isnt
influenced by some J Branded technology.

Any thing you might wanna add is most welcome.

Thank You All Again





"rawCoder" <rawCoder@hotmail.com> wrote in message
news:uBU%23euvREHA.2408@tk2msftngp13.phx.gbl...
> Dear All,
>
> Question
> Why DataAdapter.Fill does not refresh the deleted records in the dataset?
> If this is by design then how can I refresh the data and get rid of
removed
> records without actually refilling the dataset with clear and fill
> operations?
>
> Detailed
> I have a DataAdapter refering to a table in database. The data is being
> shown in a DataGrid. As the data is updated, in the table (from other
> connection), it is reflected in the grid by a call to the Fill method of
the
> DataAdapter. All is well , but if some row is deleted then the call to
Fill
> method does not delete the record from its underlying DataSet. I have come
> to know that calling clear method is an option but it seems so dirty -
> clearing all the data and then getting it again.
>
> Can someone plz guide Why it is done this way and what is the resolution.
>
> Thank You
>
>

Cor
Thu Jun 03 00:50:15 CDT 2004

Hi Raw Coder

> Well all this discussion guides me to the usage of Data Adapter in case of
> single user and in case of multi user the DataAdapter's Fill method
actually
> MERGES the data so the rows deleted by one user are not updated in other
> users DataSet. And that to refresh the deleted rows one needs to clear the
> datatable in this scenario.
>
> right ???
>

In my opinion yes.

Cor