Confusion regarding loaddatarow and update

[Guest]

Hi all,

Im using a SQLDataAdapter with custom select, update and insertqueries which
i assume work (as they are simple SQL statements).

I use this adapter to fill a table. I also add a few more columns, and set
up primary keys. I make updates to this table. I do this by queuing the data
to be added up, and then calling beginloaddata, loaddatarow per queue item,
and finally endloaddata. I then call update on the datatable to send these
updates to the database. Unfortunately Im not getting the behavior I expect.

My understanding of loaddatarow was that it uses the primary key values to
determine if a row exists to amend - if not then it creates the row instead.
However, this only seems to happen if you set the acceptchanges argument on
loaddatarow to true. If I do this, however, then the table seems to forget
which were the new/changed rows (while debugging the status of the row reads
"unchanged") and so no updates get sent.

If I set this flag to false then endloaddata throws an exception since I may
have added two rows with the same primary keys (when what I really want to
do is update a row that may have already been newly added in this batch of
loaddatarow calls, if you see what I mean).

Is my understanding of this incorrect?

Spammy

 

[William Ryan eMVP]

Hi all,

Im using a SQLDataAdapter with custom select, update and insertqueries which
i assume work (as they are simple SQL statements).

I use this adapter to fill a table. I also add a few more columns, and set
up primary keys. I make updates to this table. I do this by queuing the data
to be added up, and then calling beginloaddata, loaddatarow per queue item,
and finally endloaddata. I then call update on the datatable to send these
updates to the database. Unfortunately Im not getting the behavior I

expect.
This is essentially the way it's supposed to work. It will take the value
or values provided and find it in the PK if it's there. If not it will
create the row.

My understanding of loaddatarow was that it uses the primary key values to
determine if a row exists to amend - if not then it creates the row instead.
However, this only seems to happen if you set the acceptchanges argument on
loaddatarow to true.

I'm not following you here. AcceptChanges will ensure that all of the
changes are committed locally
http://www.knowdotnet.com/articles/efficient_pt4.html which effectively
ensure that Update called afterward won't do anything (unless of course you
change the rows again between these two)

If I do this, however, then the table seems to forget

which were the new/changed rows (while debugging the status of the row reads
"unchanged") and so no updates get sent.

AcceptChanges isn't a flag, it's a method. I don't mean to parse words but
that's a big difference here. Notice that every implementation of
Acceptchanges, on the DataRow, DataSet etc is of type void (or a Sub in
VB.NET) so there is not true/false about it. You may be getting this
confused with HasChanges but this is a readonly property.

If I set this flag to false then endloaddata throws an exception since I may
have added two rows with the same primary keys (when what I really want to
do is update a row that may have already been newly added in this batch of
loaddatarow calls, if you see what I mean).

You lost me here. It should throw an exception if you try to add a
duplicate record

Is my understanding of this incorrect?

At least on AcceptChanges it isn't. You're right on though with the
LoadDataRow. I'm not sure where you're going wrong b/c of the second
statement you made regarding acceptchanges. You can use DataTable dt =
dataSetName.GetChanges(); which will return the changes for you. Then you
can call update on dt using the same adapter that you would for the dataset
since their structure will be the same. If it successfully completes, you
can then call AcceptChanges on the original dataset.

Let me know if this helps and if not, I'll do what I can.

Spammy

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/