none
How to Update 2 tables in 1 DataTable RRS feed

  • Question

  • i have create method like below

    method Save()
    {
    transaction a

    SaveA(a); // insert
    SaveB(a); // not insert
    }

    method SaveA(Transaction a)
    {
    // code here
    DA.Update(this);
    }

    method SaveB(Transaction b)
    {
    // code here
    DA.Update(this);
    }

    i don't know why SaveB(a) don't insert data into database (i try to debug and it process DA.Update(this)Wink. SaveB is correct, i have comment SaveA(a); then SaveB(a) can process ?

    thanks!
    EK
    Tuesday, September 11, 2007 8:35 AM

All replies

  • Hi,

     

    Could it be that your calling DA.Update(this) twice so that after the first DA.Update(this) has executed and the second DA.Update(this) runs the second call is doing the exact same thing as the first one, and so it appears as though nothing has happened.

     

     

    Tuesday, September 11, 2007 10:43 AM
  • in method A and B call different StoreProcedure i want to know how to solved this?

    EK
    Wednesday, September 12, 2007 7:46 AM
  • do you call AcceptChanges anywhere in method A?

     

    Wednesday, September 12, 2007 7:59 AM
  • i call AcceptChange() after Update(this)

    example:

    adapter.Update(this);
    this.AcceptChanges();
    Wednesday, September 12, 2007 9:10 AM
  • Hi,

     

    Yes thats your problem. The adapter.Update() method looks at the rows in the dataset and extracts the rows that have been modified and uses those rows to update the database. This is to save the update method trying to update rows in the database that haven't been updated locally. The AcceptChanges() call is called after an update (like you have done) and it sets all the rows to a 'current' status (i.e. there are now no rows that are considered 'modified'). So in your next call to update there are no modified rows for the update method.

     

    You need to call the accept changes once all your code has updated the database, in your case after method 2 has been called.

    Wednesday, September 12, 2007 10:20 AM
  • i have comment AcceptChange(); in methos but it still don't Update(); in methiod B.

    i read at msdn it said after call Update(); it will auto call AcceptChange(); right?
    if yes how to solved this, is i need to create new datatable ?
    Thursday, September 13, 2007 3:39 AM
  • Hi,

     

    I never knew the Update method called AcceptChanges. Had a look and there is a boolean property in the DataAdapter called AcceptChangesDuringUpdate, which need to be set to false. Hopefully that will be it now.

     

    Thursday, September 13, 2007 7:44 AM
  • Code Snippet

    public bool SaveA(SqlTransaction transaction)

    {

    bool result;

    SqlConnection connection = transaction.Connection;
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.InsertCommand = SqlHelper.CreateCommand(connection, "sp_Save_A", new string[] { "A1", "A2", "A3" }); // this SP return SELECT_IDENTIDY() name B1
    adapter.InsertCommand.Transaction = transaction;
    adapter.AcceptChangesDuringUpdate = false;


         try
         {

    adapter.Update(this);

              result = true;

    }

    catch

         {

         result = false;
    }


    return result;

    }



    Code Snippet

    public bool SaveB(SqlTransaction transaction)

    {

    bool result;

    SqlConnection connection = transaction.Connection;
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.InsertCommand = SqlHelper.CreateCommand(connection, "sp_Save_B", new string[] { "B1", "A1", "A2", "A3" });
    adapter.InsertCommand.Transaction = transaction;
    adapter.AcceptChangesDuringUpdate = false;


         try
         {

    adapter.Update(this);

              result = true;

    }

    catch

         {

         result = false;
    }


    return result;

    }


    i have exception "Update requires a valid UpdateCommand when passed DataRow collection with modified rows." at line adapter.Update(this); on Method SaveB
    Friday, September 14, 2007 4:06 AM
  • Hi,

     

    Does the adapter have an Update command ?

     

    Monday, September 17, 2007 11:15 AM