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

Posted on 2003-03-26
Medium Priority
Last Modified: 2012-05-04
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.
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

        '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

        If MyDataSet.HasChanges() Then
                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)
            End Try
        End If

Anyone knows how to fix it?

Question by:tbbrhun
LVL 28

Accepted Solution

iboutchkine earned 200 total points
ID: 8217448
In your code you did not generate Update command. Here is an example how to update db

   Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim sql As String = "SELECT * FROM Publishers"
        Dim cn As New OleDbConnection(BiblioConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
        Dim ds as New DataSet()

        da.Fill(ds, "Publishers")

        Dim cmdBuilder As New OleDbCommandBuilder(da)

        ' generate the three default commands
        da.DeleteCommand = cmdBuilder.GetDeleteCommand
        da.InsertCommand = cmdBuilder.GetInsertCommand
        da.UpdateCommand = cmdBuilder.GetUpdateCommand

        With ds.Tables("Publishers")
            ' Modify the first record (just append 3 asterisks to Name field)
            .Rows(0)("Name") = .Rows(0)("Name").ToString & " ***"

            ' Add a new record
            Dim dr As DataRow = .NewRow
            dr("pub_id") = "9988"
            dr("Name") = "Iouri"
            dr("city") = "New York"
            dr("country") = "USA"
        End With

        ' Send changes them to the database.
        da.Update(ds, "Publishers")
    End Sub

'ensure that changes were successful
  If Not ds.HasChanges Then Exit Sub

'else there was no success give a message

Author Comment

ID: 8218860
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.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question