Mirror changed DataTable to old DataTable (ADO.NET)

[David Hovey]

I am having trouble making changes to a DataSet. Here is my situation... What i have is a DataSet with several tables and all their respective columns loaded from database. At some point during the program execution a new dataadapter and table are created based a SQL query (to access the same database spoken of above). I then make specific changes to the table/rows. While using the Update method and a OleDbCommandBuilder the changes are saved to the database. The problem is I want to now update the original dataset with Data now changed. I tried running the Fill command of the new DataAdapter and reference the old DataSet but this does not work when rows are being modified. Oddly it does work when a row has been added? I have tried various methods such as BeginEdit of the row and AcceptChanges, but perhaps I am not using them correctly? Can you help, please? Thanks You!!

[Wendelius]

David Hovey wrote:

I tried running the Fill command of the new DataAdapter and reference the old DataSet but this does not work when rows are being modified

Are you getting an exception? If so, please include info about the exception.

David Hovey wrote:

I have tried various methods such as BeginEdit of the row and AcceptChanges, but perhaps I am not using them correctly

It would be a whole lot easier if you could post the code part that's not working, including the refill portion.

The need to optimize rises from a bad design.My articles[^]

[David Hovey]

I was reluctant to post code since it might not make a lot of sense. Here it is though. Toward the end of EditCongregation is where I want to update m_dsData with the modified DataRow... Thanks again.

    Private m\_dsData As DataSet
    Public Sub LoadData()

        'MsgBox("Legacy function LoadData() called.")
        Dim dbDataAdapter As OleDbDataAdapter
        dbDataAdapter = New OleDbDataAdapter()

        Try
            m\_dsData = Nothing
            m\_dsData = New DataSet

            dbDataAdapter.SelectCommand = New OleDbCommand("SELECT \* FROM Congregations", m\_dbConnection)
            dbDataAdapter.Fill(m\_dsData, "Congregations")
            dbDataAdapter.SelectCommand = New OleDbCommand("SELECT \* FROM HomeTalks", m\_dbConnection)
            dbDataAdapter.Fill(m\_dsData, "HomeTalks")
            dbDataAdapter.SelectCommand = New OleDbCommand("SELECT \* FROM CongregationSpeakers", m\_dbConnection)
            dbDataAdapter.Fill(m\_dsData, "CongregationSpeakers")
            dbDataAdapter.SelectCommand = New OleDbCommand("SELECT \* FROM CongregationSpeakerOutlines", m\_dbConnection)
            dbDataAdapter.Fill(m\_dsData, "CongregationSpeakerOutlines")
            dbDataAdapter.SelectCommand = New OleDbCommand("SELECT \* FROM Outlines", m\_dbConnection)
            dbDataAdapter.Fill(m\_dsData, "Outlines")
            dbDataAdapter.SelectCommand = New OleDbCommand("SELECT \* FROM OutlineCategories", m\_dbConnection)
            dbDataAdapter.Fill(m\_dsData, "OutlineCategories")
            dbDataAdapter.SelectCommand = New OleDbCommand("SELECT \* FROM Readers", m\_dbConnection)
            dbDataAdapter.Fill(m\_dsData, "Readers")
            dbDataAdapter.SelectCommand = New OleDbCommand("SELECT \* FROM Chairmen", m\_dbConnection)
            dbDataAdapter.Fill(m\_dsData, "Chairmen")
            dbDataAdapter.SelectCommand = New OleDbCommand("SELECT \* FROM Hospitality", m\_dbConnection)
            dbDataAdapter.Fill(m\_dsData, "Hospitality")
            dbDataAdapter.SelectCommand = New OleDbCommand("SELECT \* FROM AwayTalks", m\_dbConnection)
            dbDataAdapter.Fill(m\_dsData, "AwayTalks")
            dbDataAdapter.SelectCommand = New OleDbCommand("SELECT \* FROM Notes", m\_dbConnection)
            dbDataAdapter.Fill(m\_dsData, "Notes")
            dbDataAdapter.SelectCommand = New OleDbCommand("SELECT \* FROM Conductors", m\_dbConnection)

[Wendelius]

David Hovey wrote:

da.Fill(m_dsData, "Congregations")

I take it, that's the point where you expect the datatable (Congregations) to refresh. Are you getting an exception or what are the symptoms?

The need to optimize rises from a bad design.My articles[^]

[David Hovey]

Sorry forgot to clarify... No exception occurs. It is very odd. I feel like I'm doing something wrong that is dumb and simple. No exception occurs and after running the Fill method, I've tested the table to see if a value is updated and it is...But then. After routine is finished the value is back to previous. Do I need to set a property for run a method on the table or dataset to allow the rows to update? The way I can fix it is by delete the table altogether and then using the Fill method. That obviously works. But in other circumstances the new table may not contain that columns that existed before.

[Wendelius]

Okay, some guesses: Have you noticed this on documentation: When using subsequent Fill calls to refresh the contents of the DataSet, two conditions must be met: 1. The SQL statement should match the one initially used to populate the DataSet. 2. The Key column information must be present. If primary key information is present, any duplicate rows are reconciled and only appear once in the DataTable that corresponds to the DataSet. Primary key information may be set either through FillSchema, by specifying the PrimaryKey property of the DataTable, or by setting the MissingSchemaAction property to AddWithKey. Also I'm not sure what's the status of each row after refresh so after refilling the dataset you could have a look with the debugger and if the states are modified, accept changes after fill (on the whole dataset). Somehow the behaviour sounds like the changes might have been rejected somewhere in the other parts of the code.

The need to optimize rises from a bad design.My articles[^]

[David Hovey]

Thanks for your help Mika. You were right all along. There were a couple things I was doing wrong during my debugging to figure this out. But in the end Number 2 of your last post was the problem. I didn't think the PrimaryKey was the problem because when I open the database in Access 2007 (using 2007 version database) it shows my column ID as the primary key. This is the column that Access automatically created when I first created the tables. Shouldn't the OleDbDataAdapter read this information about the primary key?? Thanks again!

[Wendelius]

David Hovey wrote:

Thanks for your help

No problem.

David Hovey wrote:

Shouldn't the OleDbDataAdapter read this information about the primary key

When using just Fill-method it won't read anything else than basic properties (column names, data types for columns etc). If you want to have a data table which corresponds to the schema in Access, I think when creating the table for the first time, you should do something like this (before the first fill):

da.FillSchema(m_dsData, SchemaType.Mapped);

The need to optimize rises from a bad design.My articles[^]