Saving data from two different data sources using a dataset

[FujiH]

Hello,

I am trying to load data from an OLEDB datasource into a dataset and then saving that data into a SQL table (in VB.NET code)

So I am certain that the OLEDB datasource pulls out the correct data since I am assigning the dataset table as the datasource to a datagridview.

I then create a SQLDataAdapter with an Insert Command object and pass through the dataset table loaded from the OLEDB datasource to the SQLDataAdapter.Update method. The table structure for the SQL table is the same as the OLEDB table so I am expecting the table to be populated with the data but no data is saved. I don't get any error messages returned either.

Do you know what I might be doing wrong or is there an alternative way of doing the same thing? (This needs to be called from code rather than running an SSIS package)

 

[jebenson]

Well, the records in the OleDb table will not have their DataRowState set to New, which is what is needed for them to be inserted by the InsertCommand and the .Update method. You need to actually copy the records into a second datatable, then call the .Update method with that table as the parameter.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[FujiH]

Thanks, I used SqlBulkCopy in the end passing through the datatable populated from the OleDB data source.

One thing that threw me for a while was the fact that ColumnMappings using SqlBulkCopy are case-sensitive in the field names on both the source and destination tables.