OleDbDataAdapter Update Problem RRS feed

  • Question

  • I have an encrypted table in an Access database which I want to fill a datatable with, unencrypt, allow the user to edit, encrypt, and write back out to the database. I'm using an OleDbDataAdapter for this. I have everything working, except that when I call the update function, instead of updating the rows in which the data has been changed, it inserts every row, changed or not, as a new row, so every time I update the table it doubles in size. Here's an abbreviated version of my code:

    (Trusted connection)

    DATACONNECTION = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=" & txtDbLocation.Text & ";")

    DATAADAPTER = New System.Data.OleDb.OleDbDataAdapter(New OleDb.OleDbCommand("Select * From " & *Table name* & ";"DATACONNECTION))

    Dim Builder As New OleDb.OleDbCommandBuilder(DATAADAPTER)

    TABLE = New DataTable(*Table name from database*)



    DATAADAPTER.FillSchema(TABLE, SchemaType.Source)

    * Unencrypt data from tempTable and insert into TABLE*

    * Allow user to edit data in Unencrypted form *

    Dim tempTable2 As New DataTable(TABLE.TableName)

    DATAADAPTER.FillSchema(tempTable2, SchemaType.Source)

    * Encrypt TABLE data and insert into tempTable2 *



    • Edited by DanErickson Monday, August 25, 2008 8:19 PM woops
    Monday, August 25, 2008 7:15 PM


  • Problem solved! Turns out that since I was copying the data to a new temporary table, the rowstates were all set to 'created'. This meant that when I updated the dataadapter, every row appeared  to be new, so it just added all of them as new rows. I changed my fill and update logic to edit the main DataTable directly (without the use of a temporary table), and call the AcceptChanges() function of the DataTable after every call to DataAdapter.fill and it works fine now.
    • Marked as answer by DanErickson Wednesday, August 27, 2008 5:02 PM
    Wednesday, August 27, 2008 5:01 PM