Typed DataSet Problem

Discussion in 'Microsoft ADO .NET' started by Sarah, Apr 23, 2004.

  1. Sarah

    Sarah Guest

    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
     
    Sarah, Apr 23, 2004
    #1
    1. Advertisements

  2. Sarah

    Mohamoss Guest

    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
     
    Mohamoss, Apr 23, 2004
    #2
    1. Advertisements

  3. 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
    "Sarah" <> wrote in message
    news:...
    > 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
    >
    >
     
    William Ryan eMVP, Apr 23, 2004
    #3
  4. Sarah

    Sarah Guest

    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

    "William Ryan eMVP" <> wrote in message
    news:...
    > 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
    > "Sarah" <> wrote in message
    > news:...
    > > 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
    > >
    > >

    >
    >
     
    Sarah, Apr 23, 2004
    #4
  5. "Sarah" <> wrote in message
    news:On$...
    > 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?
    >
    > Thanks
    >
    > "William Ryan eMVP" <> wrote in message
    > news:...
    > > 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
    > > "Sarah" <> wrote in message
    > > news:...
    > > > 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
    > > >
    > > >

    > >
    > >

    >
    >
     
    William Ryan eMVP, Apr 23, 2004
    #5
  6. Sarah

    Sarah Guest

    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

    "William Ryan eMVP" <> wrote in message
    news:OXrGF$...
    >
    > "Sarah" <> wrote in message
    > news:On$...
    > > 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?
    > >
    > > Thanks
    > >
    > > "William Ryan eMVP" <> wrote in message
    > > news:...
    > > > 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
    > > > "Sarah" <> wrote in message
    > > > news:...
    > > > > 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
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    Sarah, Apr 23, 2004
    #6
  7. 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
    "Sarah" <> wrote in message
    news:...
    > 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
    >
    > "William Ryan eMVP" <> wrote in message
    > news:OXrGF$...
    > >
    > > "Sarah" <> wrote in message
    > > news:On$...
    > > > 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?
    > > >
    > > > Thanks
    > > >
    > > > "William Ryan eMVP" <> wrote in message
    > > > news:...
    > > > > 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
    > > > > "Sarah" <> wrote in message
    > > > > news:...
    > > > > > 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
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    William Ryan eMVP, Apr 24, 2004
    #7
  8. Sarah

    Guest 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 <> wrote in message
    news:#...
    > 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" <> wrote in message
    > news:...
    > > 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
    > >
    > > "William Ryan eMVP" <> wrote in message
    > > news:OXrGF$...
    > > >
    > > > "Sarah" <> wrote in message
    > > > news:On$...
    > > > > 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?
    > > > >
    > > > > Thanks
    > > > >
    > > > > "William Ryan eMVP" <> wrote in message
    > > > > news:...
    > > > > > 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
    > > > > > "Sarah" <> wrote in message
    > > > > > news:...
    > > > > > > 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
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    Guest, Apr 26, 2004
    #8
  9. 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
    <Sarah> wrote in message news:...
    > 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 <> wrote in message
    > news:#...
    > > 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" <> wrote in message
    > > news:...
    > > > 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
    > > >
    > > > "William Ryan eMVP" <> wrote in message
    > > > news:OXrGF$...
    > > > >
    > > > > "Sarah" <> wrote in message
    > > > > news:On$...
    > > > > > 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?
    > > > > >
    > > > > > Thanks
    > > > > >
    > > > > > "William Ryan eMVP" <> wrote in

    message
    > > > > > news:...
    > > > > > > 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
    > > > > > > "Sarah" <> wrote in message
    > > > > > > news:...
    > > > > > > > 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
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    William Ryan eMVP, Apr 26, 2004
    #9
  10. Sarah

    Sarah Guest

    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" <> wrote in message
    news:%...
    > 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
    > <Sarah> wrote in message news:...
    > > 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 <> wrote in message
    > > news:#...
    > > > 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" <> wrote in message
    > > > news:...
    > > > > 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
    > > > >
    > > > > "William Ryan eMVP" <> wrote in message
    > > > > news:OXrGF$...
    > > > > >
    > > > > > "Sarah" <> wrote in message
    > > > > > news:On$...
    > > > > > > 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?
    > > > > > >
    > > > > > > Thanks
    > > > > > >
    > > > > > > "William Ryan eMVP" <> wrote in

    > message
    > > > > > > news:...
    > > > > > > > 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
    > > > > > > > "Sarah" <> wrote in message
    > > > > > > > news:...
    > > > > > > > > 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
    > > > > > > > >
    > > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    Sarah, Apr 26, 2004
    #10
  11. 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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:%...
    > > 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
    > > <Sarah> wrote in message news:...
    > > > 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 <> wrote in message
    > > > news:#...
    > > > > 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" <> wrote in message
    > > > > news:...
    > > > > > 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
    > > > > >
    > > > > > "William Ryan eMVP" <> wrote in

    message
    > > > > > news:OXrGF$...
    > > > > > >
    > > > > > > "Sarah" <> wrote in message
    > > > > > > news:On$...
    > > > > > > > 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?
    > > > > > > >
    > > > > > > > Thanks
    > > > > > > >
    > > > > > > > "William Ryan eMVP" <> wrote in

    > > message
    > > > > > > > news:...
    > > > > > > > > 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
    > > > > > > > > "Sarah" <> wrote in message
    > > > > > > > > news:...
    > > > > > > > > > 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
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    William Ryan eMVP, Apr 26, 2004
    #11
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paddy

    GetChanges in a Typed Dataset returns a DataSet??

    Paddy, Jul 3, 2003, in forum: Microsoft ADO .NET
    Replies:
    1
    Views:
    2,695
    Gavin Joyce
    Jul 5, 2003
  2. Lewis Edward Moten III

    Re: Merging untyped dataset into a typed dataset (GUID problems)

    Lewis Edward Moten III, Jul 14, 2003, in forum: Microsoft ADO .NET
    Replies:
    0
    Views:
    5,384
    Lewis Edward Moten III
    Jul 14, 2003
  3. Eric Kennedy
    Replies:
    0
    Views:
    413
    Eric Kennedy
    Aug 29, 2003
  4. Brad Allison

    typed vs strong typed dataset

    Brad Allison, Jul 21, 2004, in forum: Microsoft ADO .NET
    Replies:
    3
    Views:
    657
    Brad Allison
    Jul 21, 2004
  5. A.M-SG
    Replies:
    6
    Views:
    1,404
    Steven Cheng[MSFT]
    Feb 1, 2006
Loading...

Share This Page