OleDbDataAdapter.Update for DB2

[igitur]

I have a populated DataSet that I want to upload to a DB2 table. There
isn't a stored procedure, so I'll have to do it with a direct INSERT
statement.

The OleDbCommandBuilder doesn't work to generate an INSERT statement
automatically, so I have to build my own. This is a documented
feature.

I tried doing something like this:

OleDbCommand insertCommand = new OleDbCommand("INSERT INTO
MYSCHEMA.MYTABLE (COL1, COL2, COL3) VALUES (@P1, @P1, @P3)",
connection);
insertCommand.CommandType = CommandType.Text;

insertCommand.Parameters.Add("@P1", OleDbType.Integer, 0, "column1");
insertCommand.Parameters.Add("@P2", OleDbType.VarChar, 50, "column2");
insertCommand.Parameters.Add("@P3", OleDbType.VarChar, 10, "column3");

dataAdapter.InsertCommand = insertCommand;
dataAdapter.Update(myTable); // and this crashes horribly. It
doesn't like the @P1 type parameters that I tried.

Obviously column1, column2 and column3 are columns in my dataset.

I really don't want to loop through all the records 1 by 1 and generate
a separate INSERT statement for each of them. I'd like to use the
..Update functionality.

Anybody got a solution to this?

thanks,
Francois

 

[Guest]

Francois,

Since you are using the OleDB objects, you might try:

OleDbCommand insertCommand = new OleDbCommand("INSERT INTO
MYSCHEMA.MYTABLE (COL1, COL2, COL3) VALUES ?, ?, ?)",
connection);

Then be sure to add your parameters to the parameters collection in the
correct order, based on the "?" placeholder order, not the parameter name.

Kerry Moorman

 

[igitur]

Ok, but what must this command look like:

insertCommand.Parameters.Add("@P1", OleDbType.Integer, 0, "column1");
or
insertCommand.Parameters.Add("?", OleDbType.Integer, 0, "column1");

Hmmm, I tried both now, and I get this error WITHIN the .Update
command... not in my code

System.NullReferenceException: Object reference not set to an instance
of an object.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at
SanlamTrust.SanTrust.DirectExporter.Core.DirectExporter.SingleTableExport(OleDbConnection
cnDestination, DirectExportTable table, Int32& totalRecords) in c:\it
dev\sanlam
trust\santrustplus\directexporter\directexportercore\directexporter.cs:line
182

 

[igitur]

Aaaah, got it working now!

the VALUES (?, ?, ?) were correct, thanks.

I add the parameters like this now:
insertCommand.Parameters.Add("column1", OleDbType.Integer, 0,
"column1");
insertCommand.Parameters.Add("column2", OleDbType.Integer, 0,
"column2");
insertCommand.Parameters.Add("column3", OleDbType.Integer, 0,
"column3");

What fixed it was this:

To get the table schema, I orginally had this:
daDestination.MissingSchemaAction = MissingSchemaAction.AddWithKey;
daDestination.FillSchema(dsDestination, SchemaType.Mapping,
table.TargetTable);

Don't know why, but if do:
daDestination.Fill(dsDestination, table.TargetTable);

this it everything works. The data that is filled into the table in
the above command is just the first row of the table.
(SELECT * FROM MYSCHEMA.MYTABLE FETCH FIRST 1 ROWS ONLY)

Any idea what the .Fill command puts in the DataAdapter that the
..FillSchema command doesn't (except the data itself of course)???

I'm suspecting that the above "fix" will fail if the destination table
is empty (which is why I wanted to use the FillSchema method in the
first place)

regards,
F

Ok, but what must this command look like:

insertCommand.Parameters.Add("@P1", OleDbType.Integer, 0, "column1");
or
insertCommand.Parameters.Add("?", OleDbType.Integer, 0, "column1");

Hmmm, I tried both now, and I get this error WITHIN the .Update
command... not in my code

System.NullReferenceException: Object reference not set to an instance
of an object.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at
SanlamTrust.SanTrust.DirectExporter.Core.DirectExporter.SingleTableExport(OleDbConnection
cnDestination, DirectExportTable table, Int32& totalRecords) in c:\it
dev\sanlam
trust\santrustplus\directexporter\directexportercore\directexporter.cs:line
182

Francois,

Since you are using the OleDB objects, you might try:

OleDbCommand insertCommand = new OleDbCommand("INSERT INTO
MYSCHEMA.MYTABLE (COL1, COL2, COL3) VALUES ?, ?, ?)",
connection);

Then be sure to add your parameters to the parameters collection in the
correct order, based on the "?" placeholder order, not the parameter name.

Kerry Moorman