Problem with merging 2 DataTables
-
I'm trying merge DataTable "db2_table1" into to DataTable "db1_table1". But it doen't seem to work. Any help will be appreciated....
public class MergeTableClass { private OleDbDataAdapter da1, da2; private DataSet ds1; public void MergeTables(string connS1, string connS2) { string connString1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + connS1; string connString2 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + connS2; OleDbConnection oleConn1 = new OleDbConnection(connString1); OleDbConnection oleConn2 = new OleDbConnection(connString2); string cmd1 = "SELECT *FROM db1_table1"; string cmd2 = "SELECT *FROM db2_table1"; da1 = new OleDbDataAdapter(cmd1, oleConn1); da2 = new OleDbDataAdapter(cmd2, oleConn2); ds1 = new DataSet(); da1.Fill(ds1,"db1_table1"); da2.Fill(ds1,"db2_table1"); ds1.Tables["db1_table1"].Merge(ds1.Tables["db2_table1"]); da1.Update(ds1,"db2_table1"); ds1.AcceptChanges(); } } -
I'm trying merge DataTable "db2_table1" into to DataTable "db1_table1". But it doen't seem to work. Any help will be appreciated....
public class MergeTableClass { private OleDbDataAdapter da1, da2; private DataSet ds1; public void MergeTables(string connS1, string connS2) { string connString1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + connS1; string connString2 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + connS2; OleDbConnection oleConn1 = new OleDbConnection(connString1); OleDbConnection oleConn2 = new OleDbConnection(connString2); string cmd1 = "SELECT *FROM db1_table1"; string cmd2 = "SELECT *FROM db2_table1"; da1 = new OleDbDataAdapter(cmd1, oleConn1); da2 = new OleDbDataAdapter(cmd2, oleConn2); ds1 = new DataSet(); da1.Fill(ds1,"db1_table1"); da2.Fill(ds1,"db2_table1"); ds1.Tables["db1_table1"].Merge(ds1.Tables["db2_table1"]); da1.Update(ds1,"db2_table1"); ds1.AcceptChanges(); } }I think you're calling Update() on "db2_table1." Should you be calling it on "db1_table1"?
-
I think you're calling Update() on "db2_table1." Should you be calling it on "db1_table1"?
-
Oh ok. I think you just need to create a new command object with an update command. Then set the UpdateCommand of the adapter equal to that command object.
-
Oh ok. I think you just need to create a new command object with an update command. Then set the UpdateCommand of the adapter equal to that command object.
-
I tried to create a OleDbCommandBuilder but it still didn't work... OleDbCommandBuilder cmdBld = new OleDbCommandBuilder(da1);
What did you do next? da1.UpdateCommand = cmdBld.GetUpdateCommand(); ?
-
What did you do next? da1.UpdateCommand = cmdBld.GetUpdateCommand(); ?
Nothing yet... public void MergeTables(string connS1, string connS2) { string connString1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + connS1; string connString2 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + connS2; OleDbConnection oleConn1 = new OleDbConnection(connString1); OleDbConnection oleConn2 = new OleDbConnection(connString2); string cmd1 = "SELECT *FROM db1_table1"; string cmd2 = "SELECT *FROM db2_table1"; da1 = new OleDbDataAdapter(cmd1, oleConn1); da2 = new OleDbDataAdapter(cmd2, oleConn2); ds1 = new DataSet(); da1.Fill(ds1, "db1_table1"); da2.Fill(ds1, "db2_table1"); ds1.Tables["db1_table1"].Merge(ds1.Tables["db2_table1"]); OleDbCommandBuilder cmdBld = new OleDbCommandBuilder(da1); da1.UpdateCommand = cmdBld.GetUpdateCommand(); @da1.Update(ds1, "db1_table1"); ds1.AcceptChanges(); }
-
Nothing yet... public void MergeTables(string connS1, string connS2) { string connString1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + connS1; string connString2 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + connS2; OleDbConnection oleConn1 = new OleDbConnection(connString1); OleDbConnection oleConn2 = new OleDbConnection(connString2); string cmd1 = "SELECT *FROM db1_table1"; string cmd2 = "SELECT *FROM db2_table1"; da1 = new OleDbDataAdapter(cmd1, oleConn1); da2 = new OleDbDataAdapter(cmd2, oleConn2); ds1 = new DataSet(); da1.Fill(ds1, "db1_table1"); da2.Fill(ds1, "db2_table1"); ds1.Tables["db1_table1"].Merge(ds1.Tables["db2_table1"]); OleDbCommandBuilder cmdBld = new OleDbCommandBuilder(da1); da1.UpdateCommand = cmdBld.GetUpdateCommand(); @da1.Update(ds1, "db1_table1"); ds1.AcceptChanges(); }
Found it.. The problem was that after the Merge none of the rows in data table had the state RowState Changed or Added. The update methon had nothing to apply. This peace of code made the whole class work as it should:
foreach (DataRow row in ds1.Tables["db2_table1"].Rows) row.SetAdded();Thanks