Link to home
Start Free TrialLog in
Avatar of tbbrhun
tbbrhun

asked on

Cannot update data back to Access database from ADO.NET Dataset object

I'm trying to modify some data I pulled from an Access database into ADO.NET MyDataSet object, and post changes back to the database.

Below is the code from which I removed unnecessary details. The Error I get reads:
5: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
or
5: Update unable to find TableMapping['Table'] or DataTable 'Table'.
depending which version of overloaded DataAdapter.Update method I use

The line cuasing error is
DataAdapter.Update(MyDataSet, "Actions")

This is the entire code:

        Dim ConnectionString As String = "MyCorrectConnectionString"
        'Open Connection based on connection string provided
        Dim CONN As New OleDb.OleDbConnection(ConnectionString)
        'Declare and create new DataSet called "dataSetName"
        Dim MyDataSet As New DataSet("dataSetName")
        'Declare DataAdapter
        Dim DataAdapter As OleDb.OleDbDataAdapter
        'Declare Command
        Dim CMD As New OleDb.OleDbCommand()
        Dim rowEntry As DataRow

        'Open connection to the database
        CONN.Open()

        'Set Command's properties
        With CMD
            .CommandText = "Select * from Actions"
            .CommandType = CommandType.Text
            .Connection = CONN
        End With

        'Create DataAdapter based on the Command
        DataAdapter = New OleDb.OleDbDataAdapter(CMD)

        'Fill DataAdapter with "Actions" table
        DataAdapter.Fill(MyDataSet, "Actions")

        For Each rowEntry In MyDataSet.Tables("Actions").Rows
            With rowEntry
                .BeginEdit()    'put in edit mode to avoide raising events
                .Item("Comments") = "My Comment"
                .EndEdit()      'exit the edit mode
            End With
        Next

        If MyDataSet.HasChanges() Then
            Try
                DataAdapter.Update(MyDataSet, "Actions")   'this is the error line
                'DataAdapter.Update(MyDataSet)             'another version thereof
            Catch Exc As Exception
                'this is where the code goes
                MessageBox.Show(Err.Number.ToString & ": " & Err.Description)
            Finally
            End Try
        End If
       
        CONN.Close
        CONN.Dispose()

Anyone knows how to fix it?
thanx!




ASKER CERTIFIED SOLUTION
Avatar of iboutchkine
iboutchkine

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tbbrhun
tbbrhun

ASKER

Yes, it works. Thanks! Funny thing, I read so many articles on ADO.NET, and how to work with datasets, and for some reason I never saw this part. Way more complicated than traditional ADO. Thanks again.