Updating records requires a valid InsertCommand when passed DataRow collection with new rows

Question

I'm trying to add data from a form back into an Access table but I keep getting this error message "Update requires a valid InsertCommand when passed DataRow collection with new rows." And for the life of me I can't work out what I need to do.

Here's the code for the button click that's supposed to update the records.

Public Class Orders

Dim ClientOrderConnection As New OleDb.OleDbConnection

Dim Provider As String
Dim dbSource As String
Dim sqlQuery As String

Dim dsClientOrder As New DataSet
Dim daClientOrder As New OleDb.OleDbDataAdapter
Dim dtOrders As New Data.DataTable
Dim Booking As New ArrayList



Dim RowNumber As Integer
Dim Counter As Integer = 0
Dim NumberOfRows As Integer

Private Sub Orders_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Provider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
    dbSource = "Data Source = A2ComputingDatabase.accdb"

    ClientOrderConnection.ConnectionString = Provider & dbSource

    ClientOrderConnection.Open()

    sqlQuery = "SELECT * FROM TblClientOrder"

    daClientOrder = New OleDb.OleDbDataAdapter(sqlQuery, ClientOrderConnection)

    daClientOrder.Fill(dsClientOrder, "ClientOrder")

    ClientOrderConnection.Close()

    NumberOfRows = dsClientOrder.Tables("ClientOrder").Rows.Count

Private Sub btnSubmit_Click(sender As System.Object, e As System.EventArgs) Handles btnSubmit.Click

    If RowNumber <> -1 Then

        Dim cbClientOrder As New OleDb.OleDbCommandBuilder
        Dim dsClientNewRow As DataRow




        dsClientNewRow = dsClientOrder.Tables("ClientOrder").NewRow()

        dsClientNewRow.Item("ClientOrderNumber") = txtOrderNo.Text
        dsClientNewRow.Item("ClientTelNo") = txtClientTelNo.Text

        dsClientOrder.Tables("ClientOrder").Rows.Add(dsClientNewRow)

        daClientOrder.Update(dsClientOrder, "ClientOrder")


        MsgBox("New Reocrd added to the Database")
    End If



End Sub

Any help is much appreciated, thanks in advance!

Eric Walker Eric Walker · Accepted Answer · 2015-10-15T02:10:57

You actually have to instantiate the OledbCommandBuilder from your DataAdapter:

cbClientOrder = New OleDb.OleDbCommandBuilder(daClientOrder)

Dim cbClientOrder with your DataAdapter then put that somewhere after the daClientOrder.Fill() call and you should be good to go. And of course get rid of the other declaration of cbClientOrder in your btnSubmit_Click event.

Updating records requires a valid InsertCommand when passed DataRow collection with new rows

1 Answers