dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Need to copy contents between datasets and insert to database

  1. #1
    Join Date
    Mar 2009
    Posts
    4

    Need to copy contents between datasets and insert to database

    I need to copy the contents of one database and insert to another. My objective is to archive records before a given date, but I simplified my code with a mock program (below) that still does not seem to work. There are no error messages, it just doesn't update/insert records into my Access database. Can someone please help? Thanks!

    Dim daA As New OleDb.OleDbDataAdapter("Select * From TableA", My.Settings.TestDBConnectionString)
    Dim dsA As New DataSet : dsA.Tables.Add(0)
    daA.Fill(dsA.Tables(0))

    Dim daB As New OleDb.OleDbDataAdapter("Select * From TableB", My.Settings.TestDBConnectionString)
    Dim dsB As New DataSet : dsB.Tables.Add(0)

    For Each dr As DataRow In dsA.Tables(0).Rows
    dsB.Tables(0).ImportRow(dr)
    Next dr

    Dim CB As New OleDb.OleDbCommandBuilder(daB)
    daB.InsertCommand = CB.GetInsertCommand


    daB.Update(dsB.Tables(0))

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    That looks like you are trying to copy records between tables in a database, not between databases.

    If you want to "archive" the entire database, then why not just copy the entire database file to a new location/name?

  3. #3
    Join Date
    Mar 2009
    Posts
    4
    Thanks for the prompt reply. My mistake, yes I am moving records from one TABLE to another. I am assembling a program that runs continuously on a server and archives past records (events) on the first of every month, so it won't have to be done manually.

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    In that case, just run an INSERT query. Something like
    Code:
    INSERT INTO Archive_Table_Name SELECT * FROM Production_Table_Name
    You can do that directly from your .NET program.

  5. #5
    Join Date
    Mar 2009
    Posts
    4
    Thanks again. This brings my code to:

    Dim daA As New OleDb.OleDbDataAdapter("Select * From TableA", My.Settings.TestDBConnectionString)
    Dim dsA As New DataSet : dsA.Tables.Add(0)
    daA.Fill(dsA.Tables(0))

    Dim Cm As New OleDb.OleDbCommand("INSERT INTO TableB SELECT * FROM TableA")
    daA.InsertCommand = Cm

    daA.Update(dsA.Tables(0))

    what am i missing?

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    You need to execute the INSERT. I like to use strings to hold my queries.
    Code:
    Dim sSQL As String
    
    sSQL = "INSERT INTO TableB SELECT * FROM TableA"
    
    Dim Cm As New OleDb.OleDbCommand(sSQL,My.Settings.TestDBConnectionString)
    Cm.ExecuteNonQuery()
    This will copy EVERYTHING from the source table to the destination table, so before running this each time you might want to either DROP the destination table and recreate it, or delete all the records out of it so you don't get a zillion and a half duplicates.

  7. #7
    Join Date
    Mar 2009
    Posts
    4
    It worked. Thanks!

  8. #8
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    For safety sake, I would also suggest copying the entire database to a backup folder once a month or so.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center