Typed DataSet Problem

S

Sarah

Hi,

In my application I am manually adding a datarow (with user data not from
db) in a typed dataset. At that time the datarow row state is "Added" which
is correct. The problem I am running into is when I modify that row later
in the application the row state is still "Added" and not "Modified". Why
is this the case?

Thanks
 
M

Mohamoss

Hi
this is because you didn't execute the accept changes of the dataset
object. once you do that , added will be commeted and when you do changes ,
the state will be modified ... hope that helps
Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC
 
W

William Ryan eMVP

It won't change to modified until AcceptChanges is called on the dataset or
you call dataadatper.UPdate successfully which does the same. At that
point, it's still Added. The DataAdapter will need to use its Insert
Command to submit the update. Your Insert Command is different from your
Update Command... so if the row didn't exist yet in the db and it used and
Update statement, it would either not work and do nothing or it would assume
it was deleted since it first saw it and throw a concurrency exception - it
would depend on how you were using the dataadapter..

Either way, remember that Rowstate dictates what Command the Adapter is
going to use against the DB and it's going to use fields of that row as
Command Parameters.

However, once you call acceptchanges, then all rows marked Deleted (rowstate
is deleted) will by physically removed from the datatable (until then, they
are still there, it's just that their rowstate is set to Deleted), and all
of the Original values are reset to the current values. This way the state
of the datatable matches the database (assuming you fired an Update
Command). This keeps the dataadapter from reissuing commands it already sent
for a given row.

Hopefully this explains it?

If notlet me know.

Bill
 
S

Sarah

Thanks,

I do call Ds.AcceptChanges() and it changes the row state to "unchanged".
Is the rowstate functionality behaving differently because I the data is not
coming from the db but rather manually? There is a scenerio that the user
data entered into the dataset could be in the db already and it will need to
update that record. Does the dataadapter rely only on the rowstate to
decide when to update or insert?

Thanks
 
W

William Ryan eMVP

Sarah said:
Thanks,

I do call Ds.AcceptChanges() and it changes the row state to "unchanged".
Is the rowstate functionality behaving differently because I the data is not
coming from the db but rather manually?
No! This is a very common misconception. When you use a DataAdapter and
get the data from a Database, many things happen, the rows are created, the
columns are created, the columns are mapped based on the schema etc.
However, if you manually created the columns and manually added the values,
there's no way you could tell the difference between the two just by looking
at the tables. All else being equal, you aren't going to get different
behavoir from a Datatable because it got filled from a database.

There is a scenerio that the user
data entered into the dataset could be in the db already and it will need to
update that record. Does the dataadapter rely only on the rowstate to
decide when to update or insert?

Yes, The Adapter first checks if the HasChanges flag is set to true. If
false, it does nothing b/c nothing has changed. If so, it goes row by row on
the changed rows and depending on the rowstate, decides which command it's
going to fire, uses the values in the rows to set the parameters and fires
the update. You can modify this behavior somewhat by filtering on Rowstate
so you could for instance, only call Update on rows that have been added for
instance, or you could do added first, modified second and deleted third.

If you call update or you call AcceptChanges, then the row should be no
longer considered added. However, if you then modify it, and it's not in
the db b/c you called acceptchanges and the Insert Commmand never executed
against it, then you'll most likely get a concurrency exception b/c the
dataadapter will think the row used to exist (b/c its in the datatable and
it's rowstate isn't added, it's modified) and has been deleted when in fact
it didn't exist at all. This is dependent on your UPdate logic to a large
degree and the object you use, (You have very little control with a
CommandBuilder) but from the sounds of your scenario, you may need todo
something different.
Why are you calling AcceptChanges before you call update? This will ensure
that your update never fires as expected (at all in most cases).
Or am I misunderstanding it?
 
S

Sarah

Thanks,

I agree with you I think in my case I cannot use the data adapter but rather
create my own logic. I have tested using the data adapter and it never
fires the update command when I manually enter in the data even if the the
data exists in the db. I can never get the rowstate to be equal to
"modified" when I change the data on my manually added data row after it has
been updated to the db.

Thanks again
 
W

William Ryan eMVP

Sarah:

If you send me the code I"d be more than happy to look at it and see if I
can find anything. The rowstate mechanism is one of the most robust
features and while I'm not implying there can't be any bugs or issues, I
know of none and I'm fairly active in this regard. If you are submitting
the row in an update statement and it's being inserted, then the rowstate
should no longer indicate added. If you change anything to it, it should be
modified. If it's still showing added, I suspect that you may have an
exception being raised that you are just eating thereby giving the
appearance that it's updated when it's not or some other type problem. Just
to be safe, I'd call update, for testing purposes I'd fire another query
just to verify unquestionably that it worked, then I'd try an edit. So
assuming we are talking about Row 0 in datatable, I'd do the following:

Debug.Assert(dt.Rows[0].RowState == RowState.Added); //Call this before
update. This assertion should pass
next, call the update
int i = dataAdapter.Update(dt);
Now, make sure you are catching any exceptions here and not eating them..
Verify something happened:

Debug.Assert(i > 0);
//If this fails, the update didn't work as expected. Assuming it did the
rowstate should be Unchanged
Debug.Assert(dt.Rows[0].RowState == RowState.Unchanged);

This too should pass. If it fails, but the other assertions didn't,
something weird is happening.

Ok, but assume it does, just for the sake of addressing this one issue, call
AcceptChanges just to be sure, then check the rowstate again There is the
possilibity that the row is being modified in another thread or if you
created an event, then it's firing before we check again.

dt.AcceptChanges();

Now,check the rowstate again... All of the assertions should pass but if
they don't, somethign else like what I mention above may be at play. If so,
then send me the code it you'd like and I'll take a look at it.

However,based on our earlier dialog, I think the insert may be failing b/c
it already exists so you may need to check beforehand or use some other
mechanism.

Let me know if you need any help.

Bill
 
G

Guest

Thanks again for your help.

In my application the majority of the data comes from another application
which is run before it. These two application are tightly integrated via
XML. So, the application can get data these ways:
1) Via XML from the other app including some user input (majority of the
time)
2) Via the app database (updating a current record)
3) User input

I have no problem with 2 because I fill my datasets and when I change the
data my rowstate equals "modified". And when I call update my dataadapter
it calles my update command. The problem I have is with 1 and 3 because the
record could already be in the database (App does not know - I can test it
before insert). My datasets rowstate equals "added" and of coarse the
dataadapter calls the insert command. I can create a stored procedure that
can test for the record and if exists then update it. This works fine
however I do not like tricking to the application. I could design the
application not to use the data adpater and just manually pass the
parameters. The real problem I run into is one of my tables is designed to
have multiple child tables. There are times when these tables do not have
any data in them which means the keys in the main table are null. So, on
the update I would need at times to delete data (rows) which I could really
use the dataadpater and the three comands (insert, update and delete). This
could simplify my updating logic if I could somehow make the rowstate work.
I hoped I explained my situation.

Thanks again.
William Ryan eMVP said:
Sarah:

If you send me the code I"d be more than happy to look at it and see if I
can find anything. The rowstate mechanism is one of the most robust
features and while I'm not implying there can't be any bugs or issues, I
know of none and I'm fairly active in this regard. If you are submitting
the row in an update statement and it's being inserted, then the rowstate
should no longer indicate added. If you change anything to it, it should be
modified. If it's still showing added, I suspect that you may have an
exception being raised that you are just eating thereby giving the
appearance that it's updated when it's not or some other type problem. Just
to be safe, I'd call update, for testing purposes I'd fire another query
just to verify unquestionably that it worked, then I'd try an edit. So
assuming we are talking about Row 0 in datatable, I'd do the following:

Debug.Assert(dt.Rows[0].RowState == RowState.Added); file://Call this before
update. This assertion should pass
next, call the update
int i = dataAdapter.Update(dt);
Now, make sure you are catching any exceptions here and not eating them..
Verify something happened:

Debug.Assert(i > 0);
file://If this fails, the update didn't work as expected. Assuming it did the
rowstate should be Unchanged
Debug.Assert(dt.Rows[0].RowState == RowState.Unchanged);

This too should pass. If it fails, but the other assertions didn't,
something weird is happening.

Ok, but assume it does, just for the sake of addressing this one issue, call
AcceptChanges just to be sure, then check the rowstate again There is the
possilibity that the row is being modified in another thread or if you
created an event, then it's firing before we check again.

dt.AcceptChanges();

Now,check the rowstate again... All of the assertions should pass but if
they don't, somethign else like what I mention above may be at play. If so,
then send me the code it you'd like and I'll take a look at it.

However,based on our earlier dialog, I think the insert may be failing b/c
it already exists so you may need to check beforehand or use some other
mechanism.

Let me know if you need any help.

Bill
Sarah said:
Thanks,

I agree with you I think in my case I cannot use the data adapter but rather
create my own logic. I have tested using the data adapter and it never
fires the update command when I manually enter in the data even if the the
data exists in the db. I can never get the rowstate to be equal to
"modified" when I change the data on my manually added data row after it has
been updated to the db.

Thanks again
data
is created,
the row added
for Adapter
is row
as
 
W

William Ryan eMVP

Sarah:

I think I understand. Ok, whenver you add a value to the Row, it may be in
the db or it may not. Assuming it's not in there, a simple insert command
will work. But if it is, that causes some drama. So here's esssentially
what I propose. If the data exists in the db, you still want to use the
dataadapter and you want it to use the Update command. However, since it
sees the rowstate as added, it will use the Insert command. If it 'knew' to
use Insert when it didn't exist in the database and rowstate was added and
to use "Update" when it was in the DB and rowstate would be added, life
would be good, correct?

Ok, you'll have to add the value to the row either way b/c if it's not in
the datatable, neither command is going to much matter. So, after adding
the row to the datatable, run a function that returns whether or not the row
was in the db. If it is already in there, Immediately call .AcceptChanges
only on THAT ROW. Otherwise no acceptchanges is necessary
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatarowclassacceptchangestopic.asp.

So, you add a row and it happens to exist in the db. Right after you add it
to the db, you call your function (something like DoesValueExist) which
returns True (indicating it does). Currenlty the rowstate is added. So,
the very next line you call NewlyAddedDataRow.AcceptChanges (only on this
row). Now the Rowstate is unchanged. Anything you do to this row will cause
its rowstate to be Modified... After calling AcceptChanges, Rowstate won't
ever be added unless you delete or remove it and add it again. So, you
change the values, call dataAdapter.UPdate(dataset) and then it will see a
rowstate or modified, call the Update command instead of Insert, and life
should be good.

Now, you add another new row which isn't in the db. Immediately afterward
you call DoesValueExist and it returns false. In this instance, the
Rowstate is currently Added and you want it to remain that way. You may
still need to change some values, but the final values are what you want and
you need it to be inserted into the db. So, you call update, the adapter
sees the rowstate as added, and calls the InsertCommand as planned.

The whole thing is governed by checking if it exists immediately after you
add the row. If it does, call AcceptChanges which makes it as though it
came from the db originally. If it doesn't, then leave the rowstate aalone.

In doing so, the rowstate of your datatable will match the db which is what
you want. The whole prolem is that they are out of sync at times but
calling acceptchanges on any row that already exists will set it to
UnChanged which is what you want (it's what happens when you call
dataadapter.Fill with the default setting of AcceptChangesDuringFill set to
false.)

Does this make sense? Basically, you are just using AcceptChanges to make
sure the datatable matches the db as closely as possible, and in doing so,
you can use the Update/Insert/Delete command as you would normally.

Let me know if you have any problems. Sorry about the delay. I'll keep my
eyes open for your response.

Bill
Thanks again for your help.

In my application the majority of the data comes from another application
which is run before it. These two application are tightly integrated via
XML. So, the application can get data these ways:
1) Via XML from the other app including some user input (majority of the
time)
2) Via the app database (updating a current record)
3) User input

I have no problem with 2 because I fill my datasets and when I change the
data my rowstate equals "modified". And when I call update my dataadapter
it calles my update command. The problem I have is with 1 and 3 because the
record could already be in the database (App does not know - I can test it
before insert). My datasets rowstate equals "added" and of coarse the
dataadapter calls the insert command. I can create a stored procedure that
can test for the record and if exists then update it. This works fine
however I do not like tricking to the application. I could design the
application not to use the data adpater and just manually pass the
parameters. The real problem I run into is one of my tables is designed to
have multiple child tables. There are times when these tables do not have
any data in them which means the keys in the main table are null. So, on
the update I would need at times to delete data (rows) which I could really
use the dataadpater and the three comands (insert, update and delete). This
could simplify my updating logic if I could somehow make the rowstate work.
I hoped I explained my situation.

Thanks again.
William Ryan eMVP said:
Sarah:

If you send me the code I"d be more than happy to look at it and see if I
can find anything. The rowstate mechanism is one of the most robust
features and while I'm not implying there can't be any bugs or issues, I
know of none and I'm fairly active in this regard. If you are submitting
the row in an update statement and it's being inserted, then the rowstate
should no longer indicate added. If you change anything to it, it
should
be
modified. If it's still showing added, I suspect that you may have an
exception being raised that you are just eating thereby giving the
appearance that it's updated when it's not or some other type problem. Just
to be safe, I'd call update, for testing purposes I'd fire another query
just to verify unquestionably that it worked, then I'd try an edit. So
assuming we are talking about Row 0 in datatable, I'd do the following:

Debug.Assert(dt.Rows[0].RowState == RowState.Added); file://Call this before
update. This assertion should pass
next, call the update
int i = dataAdapter.Update(dt);
Now, make sure you are catching any exceptions here and not eating them..
Verify something happened:

Debug.Assert(i > 0);
file://If this fails, the update didn't work as expected. Assuming it
did
the
rowstate should be Unchanged
Debug.Assert(dt.Rows[0].RowState == RowState.Unchanged);

This too should pass. If it fails, but the other assertions didn't,
something weird is happening.

Ok, but assume it does, just for the sake of addressing this one issue, call
AcceptChanges just to be sure, then check the rowstate again There is the
possilibity that the row is being modified in another thread or if you
created an event, then it's firing before we check again.

dt.AcceptChanges();

Now,check the rowstate again... All of the assertions should pass but if
they don't, somethign else like what I mention above may be at play. If so,
then send me the code it you'd like and I'll take a look at it.

However,based on our earlier dialog, I think the insert may be failing b/c
it already exists so you may need to check beforehand or use some other
mechanism.

Let me know if you need any help.

Bill
Sarah said:
Thanks,

I agree with you I think in my case I cannot use the data adapter but rather
create my own logic. I have tested using the data adapter and it never
fires the update command when I manually enter in the data even if the the
data exists in the db. I can never get the rowstate to be equal to
"modified" when I change the data on my manually added data row after
it
has
been updated to the db.

Thanks again


Thanks,

I do call Ds.AcceptChanges() and it changes the row state to
"unchanged".
Is the rowstate functionality behaving differently because I the
data
is
not
coming from the db but rather manually?
No! This is a very common misconception. When you use a
DataAdapter
and
get the data from a Database, many things happen, the rows are created,
the
columns are created, the columns are mapped based on the schema etc.
However, if you manually created the columns and manually added the
values,
there's no way you could tell the difference between the two just by
looking
at the tables. All else being equal, you aren't going to get different
behavoir from a Datatable because it got filled from a database.

There is a scenerio that the user
data entered into the dataset could be in the db already and it will
need
to
update that record. Does the dataadapter rely only on the
rowstate
to by
row command
it's not
in datatable
and At
that it
used Deleted),
and way
the data
not that
row
 
S

Sarah

William,

Thanks for all your help and I hope I did not offend you by calling your
attention with another post. After reading your reply, I think your
suggestion will work however there are alot of tables the app works with.
This means the app will be communicating alot with the db which I would
rather not have. However, I see no other way around it. Like I said in an
earlier post I have one table which has multiple tables related to it where
depending on user input could have data in each table or have no data which
is related back to the main table. In the case above, I could get data from
the other app which already has a record where the data has changed I need
to remove data from different tables and also add data to other tables
(sorry for the confusion). I thought about this approach, when the record
already exists. When the data is coming from the other app, I verify if the
data exists and if so I fill the datasets and then I modify the data from
the other app. What do you think?

Thanks

William Ryan eMVP said:
Sarah:

I think I understand. Ok, whenver you add a value to the Row, it may be in
the db or it may not. Assuming it's not in there, a simple insert command
will work. But if it is, that causes some drama. So here's esssentially
what I propose. If the data exists in the db, you still want to use the
dataadapter and you want it to use the Update command. However, since it
sees the rowstate as added, it will use the Insert command. If it 'knew' to
use Insert when it didn't exist in the database and rowstate was added and
to use "Update" when it was in the DB and rowstate would be added, life
would be good, correct?

Ok, you'll have to add the value to the row either way b/c if it's not in
the datatable, neither command is going to much matter. So, after adding
the row to the datatable, run a function that returns whether or not the row
was in the db. If it is already in there, Immediately call .AcceptChanges
only on THAT ROW. Otherwise no acceptchanges is necessary
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatarowclassacceptchangestopic.asp.

So, you add a row and it happens to exist in the db. Right after you add it
to the db, you call your function (something like DoesValueExist) which
returns True (indicating it does). Currenlty the rowstate is added. So,
the very next line you call NewlyAddedDataRow.AcceptChanges (only on this
row). Now the Rowstate is unchanged. Anything you do to this row will cause
its rowstate to be Modified... After calling AcceptChanges, Rowstate won't
ever be added unless you delete or remove it and add it again. So, you
change the values, call dataAdapter.UPdate(dataset) and then it will see a
rowstate or modified, call the Update command instead of Insert, and life
should be good.

Now, you add another new row which isn't in the db. Immediately afterward
you call DoesValueExist and it returns false. In this instance, the
Rowstate is currently Added and you want it to remain that way. You may
still need to change some values, but the final values are what you want and
you need it to be inserted into the db. So, you call update, the adapter
sees the rowstate as added, and calls the InsertCommand as planned.

The whole thing is governed by checking if it exists immediately after you
add the row. If it does, call AcceptChanges which makes it as though it
came from the db originally. If it doesn't, then leave the rowstate aalone.

In doing so, the rowstate of your datatable will match the db which is what
you want. The whole prolem is that they are out of sync at times but
calling acceptchanges on any row that already exists will set it to
UnChanged which is what you want (it's what happens when you call
dataadapter.Fill with the default setting of AcceptChangesDuringFill set to
false.)

Does this make sense? Basically, you are just using AcceptChanges to make
sure the datatable matches the db as closely as possible, and in doing so,
you can use the Update/Insert/Delete command as you would normally.

Let me know if you have any problems. Sorry about the delay. I'll keep my
eyes open for your response.

Bill
Thanks again for your help.

In my application the majority of the data comes from another application
which is run before it. These two application are tightly integrated via
XML. So, the application can get data these ways:
1) Via XML from the other app including some user input (majority of the
time)
2) Via the app database (updating a current record)
3) User input

I have no problem with 2 because I fill my datasets and when I change the
data my rowstate equals "modified". And when I call update my dataadapter
it calles my update command. The problem I have is with 1 and 3 because the
record could already be in the database (App does not know - I can test it
before insert). My datasets rowstate equals "added" and of coarse the
dataadapter calls the insert command. I can create a stored procedure that
can test for the record and if exists then update it. This works fine
however I do not like tricking to the application. I could design the
application not to use the data adpater and just manually pass the
parameters. The real problem I run into is one of my tables is designed to
have multiple child tables. There are times when these tables do not have
any data in them which means the keys in the main table are null. So, on
the update I would need at times to delete data (rows) which I could really
use the dataadpater and the three comands (insert, update and delete). This
could simplify my updating logic if I could somehow make the rowstate work.
I hoped I explained my situation.

Thanks again.
if
I
can find anything. The rowstate mechanism is one of the most robust
features and while I'm not implying there can't be any bugs or issues, I
know of none and I'm fairly active in this regard. If you are submitting
the row in an update statement and it's being inserted, then the rowstate
should no longer indicate added. If you change anything to it, it
should
be
modified. If it's still showing added, I suspect that you may have an
exception being raised that you are just eating thereby giving the
appearance that it's updated when it's not or some other type problem. Just
to be safe, I'd call update, for testing purposes I'd fire another query
just to verify unquestionably that it worked, then I'd try an edit. So
assuming we are talking about Row 0 in datatable, I'd do the following:

Debug.Assert(dt.Rows[0].RowState == RowState.Added); file://Call this before
update. This assertion should pass
next, call the update
int i = dataAdapter.Update(dt);
Now, make sure you are catching any exceptions here and not eating them..
Verify something happened:

Debug.Assert(i > 0);
file://If this fails, the update didn't work as expected. Assuming it
did
the
rowstate should be Unchanged
Debug.Assert(dt.Rows[0].RowState == RowState.Unchanged);

This too should pass. If it fails, but the other assertions didn't,
something weird is happening.

Ok, but assume it does, just for the sake of addressing this one
issue,
call
AcceptChanges just to be sure, then check the rowstate again There is the
possilibity that the row is being modified in another thread or if you
created an event, then it's firing before we check again.

dt.AcceptChanges();

Now,check the rowstate again... All of the assertions should pass but if
they don't, somethign else like what I mention above may be at play.
If
so,
then send me the code it you'd like and I'll take a look at it.

However,based on our earlier dialog, I think the insert may be failing b/c
it already exists so you may need to check beforehand or use some other
mechanism.

Let me know if you need any help.

Bill
Thanks,

I agree with you I think in my case I cannot use the data adapter but
rather
create my own logic. I have tested using the data adapter and it never
fires the update command when I manually enter in the data even if
the
the
data exists in the db. I can never get the rowstate to be equal to
"modified" when I change the data on my manually added data row
after
it rowstate true.
If be
no b/c
the
when
in different
from that
row
 
W

William Ryan eMVP

No problem , and actually I'm glad you posted it. Many times people don't
ever respond so I usually only look back a day or two.

As far as your questions, I don't really see any way around the frequent
calls to the db considering the situation. If you want to use Update and the
dataadapter, then there's not much you can do, it's going to fire the Insert
command against whatever is "added". You can write some really complex
update logic, but like you found out, it's not simple. So the crux of the
problem is when to call insert and when to call update. And if you want to
depend on rowstate, you'll have to make sure you've verified the rowstate
and AcceptChanges will get you there. As far as DB stress goes, 100 queries
is 100 queries, so your app or the other one, same difference. No one app
may respond better if it doesn't have to fire a bunch of queries but it's
all the same to the db.

Anyway, your solution below sounds good based on the project constraints.

Let me know how it goes
Sarah said:
William,

Thanks for all your help and I hope I did not offend you by calling your
attention with another post. After reading your reply, I think your
suggestion will work however there are alot of tables the app works with.
This means the app will be communicating alot with the db which I would
rather not have. However, I see no other way around it. Like I said in an
earlier post I have one table which has multiple tables related to it where
depending on user input could have data in each table or have no data which
is related back to the main table. In the case above, I could get data from
the other app which already has a record where the data has changed I need
to remove data from different tables and also add data to other tables
(sorry for the confusion). I thought about this approach, when the record
already exists. When the data is coming from the other app, I verify if the
data exists and if so I fill the datasets and then I modify the data from
the other app. What do you think?

Thanks

William Ryan eMVP said:
Sarah:

I think I understand. Ok, whenver you add a value to the Row, it may be in
the db or it may not. Assuming it's not in there, a simple insert command
will work. But if it is, that causes some drama. So here's esssentially
what I propose. If the data exists in the db, you still want to use the
dataadapter and you want it to use the Update command. However, since it
sees the rowstate as added, it will use the Insert command. If it 'knew' to
use Insert when it didn't exist in the database and rowstate was added and
to use "Update" when it was in the DB and rowstate would be added, life
would be good, correct?

Ok, you'll have to add the value to the row either way b/c if it's not in
the datatable, neither command is going to much matter. So, after adding
the row to the datatable, run a function that returns whether or not the row
was in the db. If it is already in there, Immediately call ..AcceptChanges
only on THAT ROW. Otherwise no acceptchanges is necessary
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatarowclassacceptchangestopic.asp.

So, you add a row and it happens to exist in the db. Right after you add it
to the db, you call your function (something like DoesValueExist) which
returns True (indicating it does). Currenlty the rowstate is added. So,
the very next line you call NewlyAddedDataRow.AcceptChanges (only on this
row). Now the Rowstate is unchanged. Anything you do to this row will cause
its rowstate to be Modified... After calling AcceptChanges, Rowstate won't
ever be added unless you delete or remove it and add it again. So, you
change the values, call dataAdapter.UPdate(dataset) and then it will see a
rowstate or modified, call the Update command instead of Insert, and life
should be good.

Now, you add another new row which isn't in the db. Immediately afterward
you call DoesValueExist and it returns false. In this instance, the
Rowstate is currently Added and you want it to remain that way. You may
still need to change some values, but the final values are what you want and
you need it to be inserted into the db. So, you call update, the adapter
sees the rowstate as added, and calls the InsertCommand as planned.

The whole thing is governed by checking if it exists immediately after you
add the row. If it does, call AcceptChanges which makes it as though it
came from the db originally. If it doesn't, then leave the rowstate aalone.

In doing so, the rowstate of your datatable will match the db which is what
you want. The whole prolem is that they are out of sync at times but
calling acceptchanges on any row that already exists will set it to
UnChanged which is what you want (it's what happens when you call
dataadapter.Fill with the default setting of AcceptChangesDuringFill set to
false.)

Does this make sense? Basically, you are just using AcceptChanges to make
sure the datatable matches the db as closely as possible, and in doing so,
you can use the Update/Insert/Delete command as you would normally.

Let me know if you have any problems. Sorry about the delay. I'll keep my
eyes open for your response.

Bill
because
the
test
designed
issues,
I
know of none and I'm fairly active in this regard. If you are submitting
the row in an update statement and it's being inserted, then the rowstate
should no longer indicate added. If you change anything to it, it should
be
modified. If it's still showing added, I suspect that you may have an
exception being raised that you are just eating thereby giving the
appearance that it's updated when it's not or some other type problem.
Just
to be safe, I'd call update, for testing purposes I'd fire another query
just to verify unquestionably that it worked, then I'd try an edit. So
assuming we are talking about Row 0 in datatable, I'd do the following:

Debug.Assert(dt.Rows[0].RowState == RowState.Added); file://Call this
before
update. This assertion should pass
next, call the update
int i = dataAdapter.Update(dt);
Now, make sure you are catching any exceptions here and not eating them..
Verify something happened:

Debug.Assert(i > 0);
file://If this fails, the update didn't work as expected. Assuming
it
did
the
rowstate should be Unchanged
Debug.Assert(dt.Rows[0].RowState == RowState.Unchanged);

This too should pass. If it fails, but the other assertions didn't,
something weird is happening.

Ok, but assume it does, just for the sake of addressing this one issue,
call
AcceptChanges just to be sure, then check the rowstate again There
is
the
possilibity that the row is being modified in another thread or if you
created an event, then it's firing before we check again.

dt.AcceptChanges();

Now,check the rowstate again... All of the assertions should pass
but
if
they don't, somethign else like what I mention above may be at play. If
so,
then send me the code it you'd like and I'll take a look at it.

However,based on our earlier dialog, I think the insert may be
failing
b/c
it already exists so you may need to check beforehand or use some other
mechanism.

Let me know if you need any help.

Bill
Thanks,

I agree with you I think in my case I cannot use the data adapter but
rather
create my own logic. I have tested using the data adapter and it never
fires the update command when I manually enter in the data even if the
the
data exists in the db. I can never get the rowstate to be equal to
"modified" when I change the data on my manually added data row
after
it
has
been updated to the db.

Thanks again


Thanks,

I do call Ds.AcceptChanges() and it changes the row state to
"unchanged".
Is the rowstate functionality behaving differently because I the
data
is
not
coming from the db but rather manually?
No! This is a very common misconception. When you use a DataAdapter
and
get the data from a Database, many things happen, the rows are
created,
the
columns are created, the columns are mapped based on the schema etc.
However, if you manually created the columns and manually added the
values,
there's no way you could tell the difference between the two
just
by it
will row
by
should
be it's
not when
to
with
a same.
At and
it This
way user
data
state
is modify
that
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top