OleDbDataAdapter: custom InsertCommand to Update() from a DataTable

See more:

ASP.NET

DataTable

DataAdapter

I'm trying to use a DataAdapter to load data from a database into a DataTable object, add a few rows, and then use the DataAdapter's Update() method to push the new rows back to the database.

Two key things:

1) I don't want to use the automated "CommandBuilder". I really need to use a customized InsertCommand attached to the DataAdapter that will fire when running the Update() method. It may not be obvious why from the snippet below, but trust me: it's important that I define by own InsertCommand.

2) I need to be able to make multiple changes before calling the Update() method on the data adapter. So that part of my example, below, is accurate: In my final code, I will need to be able to do multiple updates to the Datatable object before finally executing a batch operation by calling the Update() method.

So... that's what I'm trying to do! Here is my broken code:

Dim Connexion As OleDBConnection

Connexion = New OleDbConnection("...")
Connexion.Open()

Dim keyParm As OleDBParameter

Dim CustomersCommand as OleDbCommand
Dim CustomersAdapter As OleDbDataAdapter
Dim CustomersSet As DataSet
Dim CustomersTable as DataTable
Dim CustomersRows() as DataRow
Dim CustRow as DataRow

CustomersCommand = New OleDbCommand("SELECT * FROM orgIOCustomers WHERE iocIsActive='Y' and iocIsHidden='N'", Connexion)
CustomersAdapter = New OleDbDataAdapter(CustomersCommand)

CustomersAdapter.InsertCommand = New OleDBCommand("INSERT INTO orgIOCustomers (IOCID, IOCISACTIVE, IOCISHIDDEN) VALUES (@IOCID, @IOCISACTIVE, @IOCISHIDDEN)", Connexion)
CustomersAdapter.InsertCommand.Parameters.Add("@IOCID", OleDBType.VarChar, 8, "IOCID")
CustomersAdapter.InsertCommand.Parameters.Add("@IOCISACTIVE", OleDbType.VarChar, 1, "IOCISACTIVE")
CustomersAdapter.InsertCommand.Parameters.Add("@IOCISHIDDEN", OleDbType.VarChar, 1, "IOCISHIDDEN")

CustomersSet = New DataSet("Customers")
CustomersAdapter.Fill( CustomersSet, "Customers" )
CustomersTable = CustomersSet.Tables("Customers")


             ' Customer rows
             CustRow = CustomersTable.NewRow()
             CustRow("IOCID") = "10000000"
             CustRow("IOCISACTIVE") = "Y"
             CustRow("IOCISHIDDEN") = "N"
             CustomersTable.Rows.Add( CustRow )

             CustRow = CustomersTable.NewRow()
             CustRow("IOCID") = "10000001"
             CustRow("IOCISACTIVE") = "Y"
             CustRow("IOCISHIDDEN") = "N"
             CustomersTable.Rows.Add( CustRow )

             CustRow = CustomersTable.NewRow()
             CustRow("IOCID") = "10000002"
             CustRow("IOCISACTIVE") = "Y"
             CustRow("IOCISHIDDEN") = "N"
             CustomersTable.Rows.Add( CustRow )


CustomersAdapter.Update( CustomersSet, "Customers" )

The result I am getting is ORA-00936: missing expression at the last line (calling the Upate method).

I'm sure I'm doing something very basic wrong, I just don't know what it is.


Any and all help is much appreciated.