Click here to Skip to main content
11,720,731 members (54,083 online)
Rate this: bad
good
Please Sign up or sign in to vote.
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.
Posted 7-Jul-12 16:54pm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 2,179
1 OriginalGriff 924
2 F-ES Sitecore 640
3 Maciej Los 609
4 Richard MacCutchan 540


Advertise | Privacy | Mobile
Web01 | 2.8.150901.1 | Last Updated 7 Jul 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100