Answered Update multiple sql database tables using a single Data Table

[andrewmanuja]

Hi,

I got a Data Table which is generated at the Form_Load event. The data then populate in to a dataGridView.

The user is allowed to add values to a dataGridView column (issueQuantity) and when saving the data, I want to update two tables in the SQL server.

However, when I'm running the program, it will update only the first table but not the other.

Please find my code herewith.

SqlConnection connect = Connection.GetConnection();
            table.AcceptChanges();
            //Mark selected rows ready for insert
            foreach (DataRow row in table.Rows)
            {
                if (row["Select Item"] as bool? == true)
                {
                    row.SetAdded();                   
                }
            }

            //Save Data to Destination

            using (var command = new SqlCommand(@"UPDATE tbl_CostCentreDrugStock SET tbl_CostCentreDrugStock.availableQuantity += @aissueQuantity
                                                            FROM tbl_CostCentreDrugStock
                                                            INNER JOIN tbl_CostCentreOrderStatus
                                                            ON tbl_CostCentreDrugStock.costCentreID = tbl_CostCentreOrderStatus.costCentreID
                                                            WHERE tbl_CostCentreOrderStatus.ccOrderID =@accOrderID AND tbl_CostCentreDrugStock.drugID =@adrugID", connect))

             using (var adapter = new SqlDataAdapter { InsertCommand = command })
            {               
                command.Parameters.Add("@adrugID", SqlDbType.Int, 0, "drugID");
                command.Parameters.Add("@accOrderID", SqlDbType.Int, 0, "ccOrderID");
                command.Parameters.Add("@aissueQuantity", SqlDbType.Int, 0, "issueQuantity");

                try
                {
                    adapter.Update(table);
                    MessageBox.Show("Data Saved");
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }
                command.Parameters.Clear();
            }

            
            using (var command = new SqlCommand(@"UPDATE tbl_DrugQuantity SET availableQuantity -= @aissueQuantity
                                                            WHERE drugID = @adrugID", connect))

            using (var adapter = new SqlDataAdapter { InsertCommand = command })
            {               
                command.Parameters.Add("@adrugID", SqlDbType.Int, 0, "drugID");
                command.Parameters.Add("@aissueQuantity", SqlDbType.Int, 0, "issueQuantity");

                try
                {
                    adapter.Update(table);
                    MessageBox.Show("Data Saved");
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }
                command.Parameters.Clear();             
            }

Appreciate you feedback on how to accomplish this task please.

Kind regards,

Andrew

 

[jmcilhinney]

That's because, by default, the Update method implicitly calls AcceptChanges on each row it saves, meaning that there are no changes to save after the first Update call. You need to set the AcceptChangesOnUpdate property of the first data adapter to False in order to retain the changes for the second data adapter.

 

[andrewmanuja]

Hi jmcilhinney,

Thanks for your quick respond.
I did add the said AcceptChangesDuringUpdate property to false but no change in the expected outcome.
Please refer my code below;

SqlConnection connect = Connection.GetConnection();
            table.AcceptChanges();
            //Mark selected rows ready for insert
            foreach (DataRow row in table.Rows)
            {
                if (row["Select Item"] as bool? == true)
                {
                    row.SetAdded();                   
                }
            }

            //Save Data to Destination

          
            using (var command = new SqlCommand(@"UPDATE tbl_CostCentreDrugStock SET tbl_CostCentreDrugStock.availableQuantity += @aissueQuantity
                                                            FROM tbl_CostCentreDrugStock
                                                            INNER JOIN tbl_CostCentreOrderStatus
                                                            ON tbl_CostCentreDrugStock.costCentreID = tbl_CostCentreOrderStatus.costCentreID
                                                            WHERE tbl_CostCentreOrderStatus.ccOrderID =@accOrderID AND tbl_CostCentreDrugStock.drugID =@adrugID", connect))
              
                                                            
            using (var adapter = new SqlDataAdapter { InsertCommand = command })
            {               
                command.Parameters.Add("@adrugID", SqlDbType.Int, 0, "drugID");
                command.Parameters.Add("@accOrderID", SqlDbType.Int, 0, "ccOrderID");
                command.Parameters.Add("@aissueQuantity", SqlDbType.Int, 0, "issueQuantity");
                  
                try
                {
                    adapter.AcceptChangesDuringUpdate = false;
                    adapter.Update(table);
                  
                    MessageBox.Show("Data Saved");
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }
                command.Parameters.Clear();
            }

            
            using (var command = new SqlCommand(@"UPDATE tbl_DrugQuantity SET availableQuantity -= @aissueQuantity
                                                            WHERE drugID = @adrugID", connect))

            using (var adapter = new SqlDataAdapter { InsertCommand = command })
            {               
                command.Parameters.Add("@adrugID", SqlDbType.Int, 0, "drugID");
                command.Parameters.Add("@aissueQuantity", SqlDbType.Int, 0, "issueQuantity");

               try
                {
                     adapter.Update(table);
                    
                    MessageBox.Show("Data Saved");
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }
                command.Parameters.Clear();             
            }

Appreciate your valuable feedback. Hope I have done according to what you have specified above.

Kind regards,

Andrew

 

[andrewmanuja]

That's because, by default, the Update method implicitly calls AcceptChanges on each row it saves, meaning that there are no changes to save after the first Update call. You need to set the AcceptChangesOnUpdate property of the first data adapter to Falsein order to retain the changes for the second data adapter.

Hi jmcilhinney ,

Thank you very much for the guidance.
Finally, was able to fix the issue ussing "adapter.AcceptCHangesDuringUpdate" property, correctly.

Kind regards,
Andrew

 

[NoUserHere]

Don't forget to mark your question as resolved.