none
Copy Rows From (Table1 in Database1) to (Table1 in Database2) RRS feed

  • Question

  • I have 2 Copy's of the database

    Db1 location c:\Db1.accdb 

    Db2 location c:\Db2.accdb

    I connect to Db1 and returns data into DataTable from Tabel1 , then I connect to Db2 and try to insert That Datatable to the table1 in Db2 , it's not working , I try to show the datatable in Gride and its there !! ,, what I miss in my code ?

    here is code

    Private Sub Btn_Click(Sender as object,e as eventArgs) Handles Btn.Click 'Dt1 will hold the the rows from Tabel1 in Db1 'Which Contains says (20 Rows) Dim Dt1 As New DataTable Dim StrCon1 as string = 'Connection String to Db1 Dim SQL1 As String = "Select * from Tabel1" Dim Con1 As New OleDbConnection(StrCon1) Dim AdpDb1 As New OleDbDataAdapter(SQL1,Con1) AdpDb1.MissingSchemaAction =MissingSchemaAction.AddWithKey AdpDb1.Fill(Dt1) 'Dt2 will hold the the rows from Tabel1 in Db2 'Empty Tabel Dim Dt2 As New DataTable Dim StrCon2 as string = 'Connection String to Db2 Dim SQL2 As String = "Select * from Tabel1" Dim Con2 As New OleDbConnection(StrCon2) Dim AdpDb2 As New OleDbDataAdapter(SQL2,Con2) AdpDb2.MissingSchemaAction = MissingSchemaAction.AddWithKey AdpDb2.MissingMappingAction = MissingMappingAction.Passthrough AdpDb2.Fill(Dt2) For Each Dr As DataRow In Dt1.Rows Dt2.ImportRow(Dr) Next Dim CmBuilder As New OleDbCommandBuilder(AdpDb2) AdpDb2.InsertCommand = CmBuilder.GetInsertCommand AdpDb2.Update(Dt2) End Sub

    Db1 and Db2  exact copy ,where Db1 contains data and Db2 Empty.



    • Edited by Mohd.Hassan Friday, February 27, 2015 1:14 AM
    Friday, February 27, 2015 12:05 AM

Answers

  • If the tables have the same columns and names than you are probably only missing that the rows are set at filling to changed. 

    You can change that by setting the acceptchangesduringfill property to false.

    (Acceptchanges is probably after the method dispose the most misunderstood member in .Net. 

    It does not accept the changes. It set the rows to unchanged.)

    Default it is true at a fill.


    Success
    Cor

    • Marked as answer by Mohd.Hassan Saturday, February 28, 2015 1:35 AM
    Friday, February 27, 2015 11:08 AM
  • ya , I get the missing part , thanks Cor Ligthert

    I just change the for each to be :

    For Each Dr As DataRow In dt1.Rows
      Dim NewRow As DataRow
      NewRow = Dt2.NewRow()
      NewRow(0) = Dr(0)
      NewRow(1) = Dr(1)
      NewRow(2) = Dr(2)
      NewRow(3) = Dr(3)
      Dt2.Rows.Add(NewRow)
    Next
    Its working now .
    What method used to change the state of rows while use fill method ? I try RejectChange() it didn't work ?


    • Edited by Mohd.Hassan Friday, February 27, 2015 2:38 PM
    • Marked as answer by Mohd.Hassan Saturday, February 28, 2015 1:35 AM
    Friday, February 27, 2015 2:37 PM

All replies