Copy data between databases [modified]
-
Hi, I've spent hours trying to copy data from a table in database A to a similar table in database B. My code doesn't return any errors but I simply don't get any rows in my db. Can someone tell me how to do this ? I'm using mysql as database.
Dim CnERP As MySql.Data.MySqlClient.MySqlConnection = Febelfin.Administratie.Propertys.DatabaseConnectie Dim CnSite As New MySql.Data.MySqlClient.MySqlConnection CnSite.ConnectionString = "Database=databaseB;Data Source=host;User Id=nick; Password=test" 'databaseconnectie was predefined ... Dim Cmd As New MySql.Data.MySqlClient.MySqlCommand Cmd.CommandText = "SELECT * FROM opleiding WHERE opl_publiceren=1" Cmd.Connection = CnERP Dim DTerp, DTsite As New DataTable Dim AdaptErp As New MySql.Data.MySqlClient.MySqlDataAdapter(Cmd) AdaptErp.Fill(DTerp) Cmd.Connection = CnSite Dim Cmd2 As New MySql.Data.MySqlClient.MySqlCommand Cmd2.CommandText = "SELECT * FROM opleiding_nick" Cmd2.Connection = CnSite Dim adaptSite As New MySql.Data.MySqlClient.MySqlDataAdapter(Cmd2) adaptSite.Fill(DTsite) Dim CBsite As New MySql.Data.MySqlClient.MySqlCommandBuilder(adaptSite) For Each dr As DataRow In DTerp.Rows DTsite.ImportRow(dr) dr.SetAdded() dr.AcceptChanges() Next DTsite.AcceptChanges() CnSite.Open() adaptSite.Update(DTsite) CnSite.Close() DTsite.AcceptChanges().... so i've tried a lot, but nothing seems to work. I'm probably forgetting something. How come it does not insert the rows in the destination db ? thanks ! -- modified at 5:22 Tuesday 6th November, 2007 -
Hi, I've spent hours trying to copy data from a table in database A to a similar table in database B. My code doesn't return any errors but I simply don't get any rows in my db. Can someone tell me how to do this ? I'm using mysql as database.
Dim CnERP As MySql.Data.MySqlClient.MySqlConnection = Febelfin.Administratie.Propertys.DatabaseConnectie Dim CnSite As New MySql.Data.MySqlClient.MySqlConnection CnSite.ConnectionString = "Database=databaseB;Data Source=host;User Id=nick; Password=test" 'databaseconnectie was predefined ... Dim Cmd As New MySql.Data.MySqlClient.MySqlCommand Cmd.CommandText = "SELECT * FROM opleiding WHERE opl_publiceren=1" Cmd.Connection = CnERP Dim DTerp, DTsite As New DataTable Dim AdaptErp As New MySql.Data.MySqlClient.MySqlDataAdapter(Cmd) AdaptErp.Fill(DTerp) Cmd.Connection = CnSite Dim Cmd2 As New MySql.Data.MySqlClient.MySqlCommand Cmd2.CommandText = "SELECT * FROM opleiding_nick" Cmd2.Connection = CnSite Dim adaptSite As New MySql.Data.MySqlClient.MySqlDataAdapter(Cmd2) adaptSite.Fill(DTsite) Dim CBsite As New MySql.Data.MySqlClient.MySqlCommandBuilder(adaptSite) For Each dr As DataRow In DTerp.Rows DTsite.ImportRow(dr) dr.SetAdded() dr.AcceptChanges() Next DTsite.AcceptChanges() CnSite.Open() adaptSite.Update(DTsite) CnSite.Close() DTsite.AcceptChanges().... so i've tried a lot, but nothing seems to work. I'm probably forgetting something. How come it does not insert the rows in the destination db ? thanks ! -- modified at 5:22 Tuesday 6th November, 2007I am not sure about the code you wrote , but one simple way to solve the problem fast is 1- write the code to get the data from the 1st table and print it on the screen, to make sure that the data is complete and correct 2- Now write a code to insert data on the second database and test it independantely ; to make sure that the insert is Ok! 3- Now form one procedure from the two parts you created in point 1 and 2 NOTE : be aware about the constraints , primary keys , and referencing to other tables [You may want to delete the row from the original table and try to write it back to its original place using the same code you use to insert on the second database ; this will make you sure that there is no problem with the new code ] Important : USE ONLY YOUR TEST TABLES NOT THE ORIGINAL ONES YOU DEPEND ON FOR YOUR BUSENESS OR SO. and have packups all the time --BelloSoft