[RESOLVED] Dataadapter.Update not working..

[nagasrikanth]

Hello...

I know there are enough threads regarding this subject. But I didn't find any help with those. Thats why raising again over here..

Im having a very strange problem.. I just copied the source code from one working application to this new application. It was working fine over there but not over here in new application. Neither it was showing any error nor updating the data. Below is the source code (method) that im using ..

public static void AppendViolations(string dsPath, DataTable dtSource, string PartitionID)
        {
            string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" + dsPath;
            string SqlStatement = "select * from IncaViolations";

            OleDbConnection cn = new OleDbConnection(ConnectionString);
            OleDbCommand cmd = new OleDbCommand(SqlStatement, cn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.AcceptChangesDuringFill = true;  //no use even if i try false also
            OleDbCommandBuilder cmdbuild = new OleDbCommandBuilder(da);
            DataSet ds = new DataSet();
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da.Fill(ds, "IncaViolations");
            foreach (DataRow dr in dtSource.Rows)
            {
                try
                {
                    ds.Tables["IncaViolations"].ImportRow(dr);
                }
                catch (Exception ex)
                {
                    ErrorLog = ErrorLog + ex.Message.ToString() + Environment.NewLine;
                    CompleteLog = CompleteLog + ex.Message.ToString() + Environment.NewLine;
                }

            }

            da.InsertCommand = cmdbuild.GetInsertCommand();
            da.UpdateCommand = cmdbuild.GetUpdateCommand();
            da.DeleteCommand = cmdbuild.GetDeleteCommand();

            try
            {
                int i=da.Update(ds, "IncaViolations");

                System.Windows.Forms.MessageBox.Show(i.ToString());
                CompleteLog = CompleteLog + "No Of Voilations Updated in " + PartitionID + " : " + ds.Tables["IncaViolations"].Rows.Count + Environment.NewLine;
            }
            catch (Exception ex1)
            {

                ErrorLog = ErrorLog + ex1.Message.ToString() + Environment.NewLine;
                CompleteLog = CompleteLog + ex1.Message.ToString() + Environment.NewLine;
            }

            // trans.Commit();

        }

Can some body help in figure out the problem.

Thank you,
Srikanth.

[jmcilhinney]

First up, get rid of these lines because they're pointless:

            da.InsertCommand = cmdbuild.GetInsertCommand();
            da.UpdateCommand = cmdbuild.GetUpdateCommand();
            da.DeleteCommand = cmdbuild.GetDeleteCommand();

As for the question, I have no idea why you're Filling a new DataTable and importing its rows into an existing DataTable and then saving the existing DataTable. That's not going to make any changes to the existing DataTable that can then be saved. Is that what you're expecting to happen? I think you need to explain to us how this is supposed to work.

[nagasrikanth]

Well .. let me explain you my goal..

I need to Import some records from one access DB to another Access db, which are completly identical interms of its fields and keys.

and coming to my code, Im taking the path of the destination db, and the data to update (in a datatable) from source db as parameters and looping through all the records from source table and importing rows in to destination table.

After importing all the rows, Im using update command of dataadapter to update the newly imported rows.

I hope Im doing the correct way only.. Is there any alternate and best way for my goal ??

However, thanks for quick response.

Thank you.
Srikanth

[jmcilhinney]

What you're doing is kinda correct but this is the problem:

da.AcceptChangesDuringFill = true;

You're doing the exact opposite of what you need to do. AcceptChangesDuringFill is true by default and you need to set it to false. What happens is this. When you call Fill the adapter reads the data from the database and, for each record, it adds a new DataRow to the DataTable. To begin with, all those new rows have a RowState of Added, indicating that they are ready to be inserted. That is exactly what you want. If AcceptChangesDuringFill is false then it stops there. If it's true however, it will call AcceptChanges on the DataTable and all the RowStates get changed to Unchanged. Unchanged rows get ignored when you call Update.

[nagasrikanth]

Sorry !!!No use !!! even with

da.AcceptChangesDuringFill = false;

...

[jmcilhinney]

I don't know exactly what you're doing wrong but here's the simplest way to transfer data from one database to another. There's one Datatable, one DataAdapter and two Connections:

using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT statement here",
                                                       "source connection string here"))
{
    adapter.InsertCommand = new OleDbCommand("INSERT statement here",
                                             new OleDbConnection("destination connection string here"));
    adapter.AcceptChangesDuringFill = false;
 
    // Add parameters to InsertCommand here.
 
    DataTable table = new DataTable();
 
    adapter.Fill(table);
    adapter.Update(table);
}

[nagasrikanth]

Thats cool..

Buy how do we write the insert statement that will insert multiple records ??

Do you want me to loop through each record??

--Srikanth

[jmcilhinney]

Buy how do we write the insert statement that will insert multiple records ??

Go to the Database Development forum and read the FAQ. You'll find links to threads that will show you how.

Do you want me to loop through each record??

Did I loop through each record?

[nagasrikanth]

i got that...

I found that, the row state of the newly addded rows are showing as "Unchanged" only. Hence I have removed that ImportRow Method and placed the following code..

DataRow drow = ds.Tables[0].NewRow();
                    drow.ItemArray=dr.ItemArray;
                    ds.Tables[0].Rows.Add(drow);

It worked..

However, Thanks for your wonderful support "jmcilhinney".

-Srikanth