none
DataTable.GetChanges does not perform as I expected it RRS feed

  • Question

  • I am trying to update the primary keys of an Access data table.  When I do, using GetChanges, the database is updated properly, and the GetChanges datatable is also updated properly (I use an event routine to fetch the new primary keys).
    The original table, is not updated so that when I update the reste of the table I get duplicate error problems.  Here is a simple routine which shows the error.
     Private Sub DebugToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DebugToolStripMenuItem.Click
      For i = 0 To 10
       Dim row = cDB.File(i)       'File is the table in the dataset
       Dim newRow = cDB.File.NewFileRow
       UTILS.cDbInt.CopyRow(row, newRow) 'Preforms a copy corresponding into the new row (does not affect the primary key)
       newRow.fName &= "2" 'Appends 2 to the column to prevent duplicates which are not permitted
       cDB.File.AddFileRow(newRow)
      Next
     
      Dim temp = CType(cDB.File.GetChanges, ArchivesDataSet.FileDataTable)
      cDB.TAFile.Update(temp)
      For i = 0 To 10
       Debug.Print(String.Format("{0}) {1} ({2})", i, temp(i).RowState, temp(i).ID))
      Next 
    
    'The temp data table is properly updated see below in the first print out
    
      Dim temp1 = CType(cDB.File.GetChanges, ArchivesDataSet.FileDataTable)
      For i = 0 To 10
       Debug.Print(String.Format("{0}) {1} ({2})", i, temp1(i).RowState, temp1(i).ID))
      Next
     End Sub
    Notice in the 2nd printout that the primary key remains negative.
    
    Here are the results of the values in temp
     
    0) Unchanged (25156)
    1) Unchanged (25157)
    2) Unchanged (25158)
    3) Unchanged (25159)
    4) Unchanged (25160)
    5) Unchanged (25161)
    6) Unchanged (25162)
    7) Unchanged (25163)
    8) Unchanged (25164)
    9) Unchanged (25165)
    10) Unchanged (25166)
    Here are the resulsts of fetching changed rows from File into temp1 after I thought I have updated the rows (the database has the rows I updated in temp!)
    0) Added (-1)
    1) Added (-2)
    2) Added (-3)
    3) Added (-4)
    4) Added (-5)
    5) Added (-6)
    6) Added (-7)
    7) Added (-8)
    8) Added (-9)
    9) Added (-10)
    10) Added (-11)
    
    Why aren't the records updated in the original detached table?
    
    Any help would be appreciated.
    
    Leon
    

    Leon
    • Moved by VMazurModerator Wednesday, May 4, 2011 10:51 AM (From:ADO.NET Managed Providers)
    Tuesday, May 3, 2011 6:51 PM

Answers

  • Do you have a specific reason to pass the result of GetChanges() to your TAFileUpdate()? I know what you've posted is simply "repro code" so that we can easily see what your issue is, but unless you specifically need that functionality, I don't think you want to go about doing your update this way.

    I don't typically use TableAdapters, but the usual way of updating would be to pass the actual table, not the result of GetChange()"

    ' Instead of this (what you currently are doing):
    Dim temp = CType(cDB.File.GetChanges, ArchivesDataSet.FileDataTable)
     cDB.TAFile.Update(temp)
    
    ' Try doing it this way instead
     cDB.TAFile.Update(cDB.File)
    
    

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, May 7, 2011 4:30 PM
  • Get

    Changes yields a copy of rows in a new datatable. Hence when you update those the changes won't reflect on the original.

    So, you can use two approaches:

    a) as Bonnie suggested you could pass entire original table

    b) you use DataAdapter.Update(table.Select(null, null, [ROWSTATE])) method which will filter for desired original rows


    Miha Markic [MVP C#] http://blog.rthand.com
    Monday, May 16, 2011 6:07 AM

All replies

  • Do you have a specific reason to pass the result of GetChanges() to your TAFileUpdate()? I know what you've posted is simply "repro code" so that we can easily see what your issue is, but unless you specifically need that functionality, I don't think you want to go about doing your update this way.

    I don't typically use TableAdapters, but the usual way of updating would be to pass the actual table, not the result of GetChange()"

    ' Instead of this (what you currently are doing):
    Dim temp = CType(cDB.File.GetChanges, ArchivesDataSet.FileDataTable)
     cDB.TAFile.Update(temp)
    
    ' Try doing it this way instead
     cDB.TAFile.Update(cDB.File)
    
    

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, May 7, 2011 4:30 PM
  • Get

    Changes yields a copy of rows in a new datatable. Hence when you update those the changes won't reflect on the original.

    So, you can use two approaches:

    a) as Bonnie suggested you could pass entire original table

    b) you use DataAdapter.Update(table.Select(null, null, [ROWSTATE])) method which will filter for desired original rows


    Miha Markic [MVP C#] http://blog.rthand.com
    Monday, May 16, 2011 6:07 AM