none
Typed Dataset: Problems with delete rows and update database RRS feed

  • Question

  • I used the DataSet designer to create a typed Dataset.
    It has just one DataTable: 'Region' that belongs to NorthWind database.

    I'm trying to delete a row from that table and after that, update the database, but I haven't had success.

    I wrote the following code, but it doesn't work, I mean, the table isn't updated in the data base:

    Sub Delete1()

            Dim ta As New NorthWindDataSetTableAdapters.RegionTableAdapter
            Dim ds As New NorthWindDataSet
            '
            ta.Fill(ds._Region)

            ' count rows before delete
            Debug.Print(ds._Region.Rows.Count.ToString)  ' it shows 4

            ' find a row to delete:
            Dim rowToDelete As NorthWindDataSet.RegionRow = ds._Region.FindByRegionID(RegionID:=4)
            ' delete row
            ds._Region.RemoveRegionRow(row:=rowToDelete)

            ' count rows after delete
            Debug.Print(ds._Region.Rows.Count.ToString)  ' it shows 3
            '
            ' update database
            ta.Update(ds)

     Debug.Print(ds._Region.Rows.Count.ToString) ' it show 3

    End Sub

    How can I update the database based on changes in a typed Dataset?
    (I did perform these tasks: add a new row and update existents rows, but delete, don't!).

    Many thanks for any kind of help.

    Sincerely,

    Ciro.


    Ciro
    Thursday, July 30, 2009 12:49 PM

Answers

  • Hey Ciro,

    I have reproed your issue, now I understand what you are asking. When you call Region.RemoveRegionRow(row) this calls Region.Rows.Remove(row). This puts the row into a "detached" rowstate meaning its not part of the table, but this is not in a "deleted" state. So when the Update on the adapter is called it will not send a delete to the adapter because the row isn't marked in the deleted state.

    Below is a msdn article that talks about Remove
    http://msdn.microsoft.com/en-us/library/system.data.datarowcollection.remove.aspx
    "Calling Remove is the same as calling Delete and then calling AcceptChanges."

    If you call delete on a row it will be put in the deleted state, then upon acceptchanges it will transition to the detached state.

    So if you want the updates to be sent to the database just call row.delete.

    Thanks
    Chris Robinson
    Program Manager - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, August 3, 2009 5:12 PM

All replies

  • Hey Ciro,

    This looks correct but there is one issue. If you delete a Region and that region is used somewhere else then when the update is run typically what will happen is a constraint violation when the update is attempted to be pushed against the store.

    Perhaps as a sample, create a new region and delete only that one so you can understand how this works and also know that it doesn't have any dependencies.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, July 31, 2009 12:37 AM
  • Hi, Chris!

    First of all, thanks for your repply.

    I tried your sugestion, but unfortunately, it didn't work.

    After many...., I found a solution changing the code to delete the row, like this:

    FROM:
    .....
    ' find a row to delete:
    Dim rowToDelete As NorthWindDataSet.RegionRow = ds._Region.FindByRegionID(RegionID:=4)
     ' delete row
    ds._Region.RemoveRegionRow(row:=rowToDelete)

    TO:
    .....
    ' find a row to delete:
    Dim rowToDelete As NorthWindDataSet.RegionRow = ds._Region.FindByRegionID(RegionID:=4)
     ' delete row
    rowToDelete.Delete

    Chris, my question now is: Why the code generated by the Dataset design (ds._Region.RemoveRegionRow(row:=rowToDelete)) fails?

    Is it a bug or I'm using the generated code incorrectly?

    Once again, many thanks for your help.

    Ciro.


    Ciro
    Friday, July 31, 2009 7:45 PM
  • Hey Ciro,

    Exactly how does the delete fail. What is the exception that is thrown or the symptoms that you see that indicates the Region has failed to delete?

    Also what type of member are you accessing when calling "_Region", is this a property, or field? What is its accessiblity, public, private, or internal.
    Typically I would expect people to write code like ds.Region not ds._Region.

    If you expect to see the delete in the database you will need to call Update on the proper table adapter.


    for example

    regionTableAdapter.Update(ds.Region);

    Are you submitting the changes to the database by calling the adapters?

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, July 31, 2009 9:13 PM
  • Hi, Chris!

    Lets go:

    "...Exactly how does the delete fail. What is the exception that is thrown or the symptoms that you see that indicates the Region has failed to delete?"

    No message/exception is displayed on the problem.

    The main problem is: using the code generated by dataset design (ds._Region.RemoveRegionRow(row:=rowToDelete)), I can delete the row of the table, but can not update the database, even after using  "regionTableAdapter.Update(ds.Region)".

    Again:
    DON'T WORK: (database isn't updated)
    .....
    ' find a row to delete:
    Dim rowToDelete As NorthWindDataSet.RegionRow = ds._Region.FindByRegionID(RegionID:=4)
     ' delete row
    ds._Region.RemoveRegionRow(row:=rowToDelete)

    WORK: (database is updated)
    .....
    ' find a row to delete:
    Dim rowToDelete As NorthWindDataSet.RegionRow = ds._Region.FindByRegionID(RegionID:=4)
     ' delete row
    rowToDelete.Delete

    note: to reproduce my dataset building procedure, just use the Data Source Configuration Wizard (menu Add New Data Source - database: Northwind - table: Region).

    Once again, many thanks for any kind of help.

    Ciro

    Ciro
    Saturday, August 1, 2009 3:49 PM
  • Hey Ciro,

    I have reproed your issue, now I understand what you are asking. When you call Region.RemoveRegionRow(row) this calls Region.Rows.Remove(row). This puts the row into a "detached" rowstate meaning its not part of the table, but this is not in a "deleted" state. So when the Update on the adapter is called it will not send a delete to the adapter because the row isn't marked in the deleted state.

    Below is a msdn article that talks about Remove
    http://msdn.microsoft.com/en-us/library/system.data.datarowcollection.remove.aspx
    "Calling Remove is the same as calling Delete and then calling AcceptChanges."

    If you call delete on a row it will be put in the deleted state, then upon acceptchanges it will transition to the detached state.

    So if you want the updates to be sent to the database just call row.delete.

    Thanks
    Chris Robinson
    Program Manager - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, August 3, 2009 5:12 PM
  • Hi, Chris!

    Many thanks for you attention and help.

    Sincerly,

    Ciro.
    Ciro
    Monday, August 3, 2009 8:11 PM