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")
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.