"igi***@gmail.com" wrote:

> 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
>
>

> 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
>
> "igi***@gmail.com" wrote:
> > 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

> 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
>
> On Oct 18, 10:41 pm, Kerry Moorman
>
> <KerryMoor***@discussions.microsoft.com> wrote:
> > 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
>
> > "igi***@gmail.com" wrote:
> > > 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