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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 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