DataSet update error requires InsertCommand

Question

Trying to add a row to an Access 2010 database with VB 2013. Everything seems to work until the UPDATE statement when I get this error:

Update requires a valid InsertCommand when passed DataRow collection with new rows.

My code is:

    Dim sqlConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CLI_CRVM.accdb")
    Dim cmd As New System.Data.OleDb.OleDbCommand()
    Dim ds As New DataSet1()
    cmd.CommandType = System.Data.CommandType.Text
    cmd.CommandText = "SELECT * FROM [extract] ;"
    cmd.Connection = sqlConnection

    sqlConnection.Open()

    Dim da = New OleDb.OleDbDataAdapter(cmd.CommandText, sqlConnection)
    da.Fill(ds, "extract")
    ds.Clear()

    Dim newExtractRow As DataRow = ds.Tables("extract").NewRow()
    newExtractRow("Field1") = "ABC123"

    ds.Tables("Extract").Rows.Add(newExtractRow)

    da.Update(ds, "Extract")

    sqlConnection.Close()

Everything I've found so far seems to reference an SQL database, not OleDb connection.

Answer 1

You can probably use an OleDbCommandBuilder, like so

' your existing OleDbDataAdapter
Dim da = New OleDb.OleDbDataAdapter(cmd.CommandText, sqlConnection)
' add the following lines:
Dim cb = New OleDbCommandBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"

For details, see

OleDbCommandBuilder Class

Working Example

For an Access table named extract with fields

ID - AutoNumber, Primary Key
Field1 - Text(255)

and data

ID  Field1 
--  -------
 1  TEST999

the following VB.NET code will insert a second row into the table in the Access database

Using con As New OleDbConnection
    con.ConnectionString =
            "Provider=Microsoft.ACE.OLEDB.12.0;" &
            "Data Source=C:\Users\Public\Database1.accdb;"
    con.Open()
    Using da As New OleDbDataAdapter("SELECT * FROM [extract]", con)
        Dim cb = New OleDbCommandBuilder(da)
        cb.QuotePrefix = "["
        cb.QuoteSuffix = "]"
        Dim dt = New DataTable
        da.Fill(dt)
        Dim dr As DataRow = dt.NewRow
        dr("Field1") = "ABC123"
        dt.Rows.Add(dr)
        da.Update(dt)
    End Using
    con.Close()
End Using

with the result

ID  Field1 
--  -------
 1  TEST999
 2  ABC123

The OleDbCommandBuilder object automatically (and invisibly) creates the INSERT, UPDATE, and DELETE commands based on the SELECT command that was supplied when the OleDbDataAdapter object was created.