DataSet.Merge not saving to DB

[Guest]

MAJOR HELP NEEDED:

I can't seem to track down the problem with this code! No errors are thrown, but the data does not get saved back to the "destination" database. HELP!!!!????

Here's the situation: taking a dataset from SQL, want to merge it with an Access dataset, and save it - THAT'S IT! The data gets merged but does't get saved, and no exceptions are raised. Can anyone see the problem with the code?

I've tried just about everything possible (except the right thing), including with and without transactions, taken the AcceptChanges out, even tried compiling it under full-moon while chanting in a candle-lit room - to no avail... (The response.writes all display the appropriate number of records in the destination data adapter)

string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strAccessDBName
DataSet dsSource = new DataSet()
DataSet dsDestination = new DataSet()

// Open the SOURCE Database Connectio
SqlConnection cnSource = new SqlConnection(Common.GetDBConnectionString())
cnSource.Open()

// Create a DataAdapter for the SOURC
SqlDataAdapter daSourceDataAdapter = new SqlDataAdapter("SELECT * FROM " + strTableName, cnSource)
daSourceDataAdapter.Fill(dsSource, strTableName)

// Open the DESTINATION Database connectio
OleDbConnection cnDestination = new OleDbConnection(strConnection)

// Create a DataAdapter for the DESTINATIO
OleDbDataAdapter daDestinationDataAdapter = new OleDbDataAdapter()
daDestinationDataAdapter.SelectCommand = new OleDbCommand("SELECT * FROM " + strTableName, cnDestination)
cnDestination.Open()

daDestinationDataAdapter.Fill(dsDestination, strTableName)
Response.Write("[Destination: " + strTableName + " - Rows after Fill: " + dsDestination.Tables[strTableName].Rows.Count.ToString() + "]<BR>")

// Create a CommandBuilder based on the DESTINATION DataAdapte
OleDbCommandBuilder cmBuilder = new OleDbCommandBuilder(daDestinationDataAdapter)
Response.Write("CommandBuilder Insert Command: " + cmBuilder.GetInsertCommand().CommandText + "<BR>")

// Set the CommandBuilder's command propertie
daDestinationDataAdapter.InsertCommand = cmBuilder.GetInsertCommand()
daDestinationDataAdapter.UpdateCommand = cmBuilder.GetUpdateCommand()

// Try to merge the SOURCE DataSet into the DESTINATION DataSe
dsDestination.Merge(dsSource, true, System.Data.MissingSchemaAction.Add)
Response.Write("[Destination: " + strTableName + " - Rows after Merge: " + dsDestination.Tables[strTableName].Rows.Count.ToString() + "] ")
dsDestination.AcceptChanges()

// Update the DESTINATION DataAdapter using the DESTINATION DataSe
int intRecords = daDestinationDataAdapter.Update(dsDestination, strTableName)
Response.Write("[Destination: " + strTableName + " - Rows after Update: " + dsDestination.Tables[strTableName].Rows.Count.ToString() + "]<BR>")
Response.Write("Record Affected: " + intRecords.ToString() + "<BR>")

cnDestination.Close()
cnDestination.Dispose()

cnSource.Close()
cnSource.Dispose()

 

[Guest]

Okay, ".NET Gurus" - here's the f-ing answer..

string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strAccessDBName
DataSet dsSource = new DataSet()

// Open the SOURCE Database Connectio
SqlConnection cnSource = new SqlConnection(Common.GetDBConnectionString())
cnSource.Open()

// Create a DataAdapter for the SOURC
SqlDataAdapter daSourceDataAdapter = new SqlDataAdapter("SELECT * FROM " + strTableName, cnSource)
daSourceDataAdapter.AcceptChangesDuringFill = false
daSourceDataAdapter.Fill(dsSource, strTableName)

// Open the DESTINATION Database connectio
OleDbConnection cnDestination = new OleDbConnection(strConnection)

// Create a DataAdapter for the DESTINATIO
OleDbDataAdapter daDestinationDataAdapter = new OleDbDataAdapter()
daDestinationDataAdapter.SelectCommand = new OleDbCommand("SELECT * FROM " + strTableName, cnDestination)
cnDestination.Open()

// Create a CommandBuilder based on the DESTINATION DataAdapte
OleDbCommandBuilder cmBuilder = new OleDbCommandBuilder(daDestinationDataAdapter)

// Set the CommandBuilder's command propertie
daDestinationDataAdapter.InsertCommand = cmBuilder.GetInsertCommand()
daDestinationDataAdapter.UpdateCommand = cmBuilder.GetUpdateCommand()

daDestinationDataAdapter.Update(dsSource, strTableName)

cnDestination.Close()
cnDestination.Dispose()

cnSource.Close()
cnSource.Dispose()