Click here to Skip to main content
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 525
1 CPallini 480
2 George Jonsson 353
3 OriginalGriff 260
4 Pheonyx 218
0 OriginalGriff 4,813
1 CPallini 3,890
2 Sergey Alexandrovich Kryukov 3,274
3 George Jonsson 2,552
4 Gihan Liyanage 2,186


Advertise | Privacy | Mobile
Web03 | 2.8.140905.1 | Last Updated 7 Jul 2012
Copyright © CodeProject, 1999-2014
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