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 Connection
SqlConnection cnSource = new SqlConnection(Common.GetDBConnectionString());
cnSource.Open();

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

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

// Create a DataAdapter for the DESTINATION
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 DataAdapter
OleDbCommandBuilder cmBuilder = new OleDbCommandBuilder(daDestinationDataAdapter);
Response.Write("CommandBuilder Insert Command: " + cmBuilder.GetInsertCommand().CommandText + "<BR>");

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


// Try to merge the SOURCE DataSet into the DESTINATION DataSet
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 DataSet
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();

 

[Nico Debeuckelaere]

You'll have to delete the line
dsDestination.AcceptChanges();

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 Connection
SqlConnection cnSource = new SqlConnection(Common.GetDBConnectionString());
cnSource.Open();

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

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

// Create a DataAdapter for the DESTINATION
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: " +

// Create a CommandBuilder based on the DESTINATION DataAdapter
OleDbCommandBuilder cmBuilder = new OleDbCommandBuilder(daDestinationDataAdapter);
Response.Write("CommandBuilder Insert Command: " +

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


// Try to merge the SOURCE DataSet into the DESTINATION DataSet
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 DataSet
int intRecords = daDestinationDataAdapter.Update(dsDestination, strTableName);
Response.Write("[Destination: " + strTableName + " - Rows after Update: "

 

[Guest]

Thanks for the input. Unfortunately, removing the dsDestination.AcceptChanges() (as mentioned in the original post) does not do the trick..

Any other ideas?

 

[Guest]

I am having almost exactly the same problem with my program, and haven't been able to find a solution. The code is displayed below. In my case, I can see that the datarows are being modified in the loop, and the program executes through with no exceptions raised and no changes made back to the SQL database. If I remove the AcceptChanges method, da.Update raised the exception "Concurrency violation: the UpdateCommand affected 0 records

If anyone figures this out, I'd highly appreciate posting it back to this thread. If I figure it out, likewise

Thanks

Andr

private void MyProcess(string sCriteria

tr

string sSQL = "SELECT JobInstructions , ServiceID FROM tblMainServices " +
" WHERE (JobInstructions LIKE '%" + sCriteria + "%') AND (DateSold >= '1/1/2004') AND (AlreadyCompleted = 0) and (AccountKey < 8000);"
//Connect to database
SqlConnection cn = new SqlConnection(sConnect)
SqlDataAdapter da = new SqlDataAdapter()
SqlCommand cmd = cn.CreateCommand()
da.SelectCommand = new SqlCommand(sSQL, cn)
da.SelectCommand.CommandTimeout=0
SqlCommandBuilder sqlCB = new SqlCommandBuilder(da)
DataSet ds = new DataSet()
da.Fill(ds, "Services")

foreach (DataRow dsRow in ds.Tables["Services"].Rows


string sJobInstructions = dsRow["JobInstructions"].ToString()
string sServiceId = dsRow["ServiceID"].ToString()

int iStartIndex = sJobInstructions.IndexOf(sCriteria)
while (iStartIndex != -1

sJobInstructions = sJobInstructions.Remove(iStartIndex, sCriteria.Length)
iStartIndex = sJobInstructions.IndexOf(sCriteria)

dsRow["JobInstructions"] = sJobInstructions

ds.Tables["Services"].AcceptChanges()
sSQL = "UPDATE tblMainServices SET JobInstructions = @JobInstructions "
"WHERE (serviceID = @ServiceID);"
SqlCommand upcmd = new SqlCommand(sSQL, cn)
upcmd.CommandText = sSQL
upcmd.Parameters.Add("@JobInstructions", SqlDbType.NVarChar, 400, "JobInstructions")
upcmd.Parameters.Add("@ServiceID", SqlDbType.NVarChar,1, "ServiceID")
da.UpdateCommand = upcmd
da.Update(ds, "services")

ds.Tables["Services"].Clear()


catch (Exception ex)

 

[Guest]

I figured out my problem, I'd set the varchar length of my ServiceID parameter to 1 instead of 10. That's why I was getting a concurrency exception without the ds.AcceptChanges command

It took a trip through the SQL Profiler to figure out my problem - the T-SQL code problem was plain as day there