locked
Move data from one table to another RRS feed

  • Question

  • I am trying to archive specific rows of data in a table to another table, then delete the data from the original table. I am a newby and perhaps I'm approaching it wrong, but I cannot seem to get it to work. I would be very greatful if someone could offer some advise! I am using Visual Studio 2008 in Visual Basic. My database is written in SQLServer 2008 Express. Example:

    IF column 'ID' has a row value of 'SomeValue', I want to move that row to an archive table and delete the row from the original table. Here is the code I have been working with:



    Dim instance1 As DataTable1
    Dim instance2 As DataTable2
    Dim row As DataSet.Table1Row
    instance1 = DataSet.Table1
    instance2 = DataSet.Table2

    For
    Each row In instance1 

    If instance1.IDColumn Is "SomeValue" Then

    instance2.ImportRow(row)
    instance1.RemoveRow(row)
     

    End If 

    Next

     

    Friday, December 4, 2009 2:39 PM

Answers

  • There is an article on how to do this here:

    http://support.microsoft.com/kb/305346

    Though this looks similar to what you have using the ImportRow.

    You did not mention *how* this is not working. I assume the problem is with the RemoveRow?

    Due to the way the For/Each statement iterates through rows, it is not normally a good idea to remove rows within the for/each statement as you have.

    You may want to consider marking the rows as deleted, then accepting the deletions outside of the loop as shown here:

    http://www.c-sharpcorner.com/UploadFile/mahesh/DeleteDataTableRows08242006123435PM/DeleteDataTableRows.aspx

    Hope this helps.
    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    • Marked as answer by Jeff Shan Friday, December 11, 2009 1:25 AM
    Friday, December 4, 2009 4:13 PM
  • Hi welterbf123,

    I think you can look into the following example for some information
    Imports System.Data.SqlClient
    Public Class Form1
        Const constr As String = "server=(local);database=TestDB;uid=sa;pwd=sa"
        Dim dt As New DataTable
        Dim dt2 As New DataTable
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
            Dim adapter As SqlDataAdapter = New SqlDataAdapter("select * from customer", constr)
            adapter.Fill(dt)
            adapter.Fill(dt2)
            DataGridView1.DataSource = dt
            dt2.Rows.Clear()
            DataGridView2.DataSource = dt2
    
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            For Each dr As DataRow In dt.Rows
                If dr("AGE").ToString.Trim = "a" Then
                    Dim datarow As DataRow = dt2.NewRow
                    For i As Integer = 0 To dt.Columns.Count - 1
                        datarow(i) = dr(i)
                    Next
                    dt2.Rows.Add(datarow)
                    dr.Delete()
                End If
            Next
            dt.AcceptChanges()
        End Sub
    End Class
    Hope this helps

    Regards
    Jeff Shan
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Jeff Shan Friday, December 11, 2009 1:25 AM
    Thursday, December 10, 2009 8:50 AM

All replies

  • There is an article on how to do this here:

    http://support.microsoft.com/kb/305346

    Though this looks similar to what you have using the ImportRow.

    You did not mention *how* this is not working. I assume the problem is with the RemoveRow?

    Due to the way the For/Each statement iterates through rows, it is not normally a good idea to remove rows within the for/each statement as you have.

    You may want to consider marking the rows as deleted, then accepting the deletions outside of the loop as shown here:

    http://www.c-sharpcorner.com/UploadFile/mahesh/DeleteDataTableRows08242006123435PM/DeleteDataTableRows.aspx

    Hope this helps.
    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    • Marked as answer by Jeff Shan Friday, December 11, 2009 1:25 AM
    Friday, December 4, 2009 4:13 PM
  • Thank you so much! Yes, you are correct. It fails at the 'accept changes' level. I will try  this as soon as I get  home. I'm sure this will help. Thanks again.
    Saturday, December 5, 2009 4:48 AM
  • I've been working on this and the delete function is working well but the importrow function is not importing the row as it should. What could I be doing wrong? This is the code I have come up with so far:

    'Sort out the NLE employees and move those records to tblNLE

     

    Dim tblNameAddr As DataTable
    Dim tblNLE As DataTable
    Dim i As Integer
    Dim dr As DataRow

    tblNameAddr = DataTrackerDataSet.tblNameAddr
    tblNLE = DataTrackerDataSet.tblNLE


    For
    Each dr In tblNameAddr.Rows
    If (dr("UnitID") = "NLE") Then
    i = (dr("UnitID") = "NLE")
    tblNLE.ImportRow(tblNameAddr(i))
    dr.Delete()
    End If

     

    Next

    Tuesday, December 8, 2009 5:26 AM
  • Hi welterbf123,

    I think you can look into the following example for some information
    Imports System.Data.SqlClient
    Public Class Form1
        Const constr As String = "server=(local);database=TestDB;uid=sa;pwd=sa"
        Dim dt As New DataTable
        Dim dt2 As New DataTable
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
            Dim adapter As SqlDataAdapter = New SqlDataAdapter("select * from customer", constr)
            adapter.Fill(dt)
            adapter.Fill(dt2)
            DataGridView1.DataSource = dt
            dt2.Rows.Clear()
            DataGridView2.DataSource = dt2
    
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            For Each dr As DataRow In dt.Rows
                If dr("AGE").ToString.Trim = "a" Then
                    Dim datarow As DataRow = dt2.NewRow
                    For i As Integer = 0 To dt.Columns.Count - 1
                        datarow(i) = dr(i)
                    Next
                    dt2.Rows.Add(datarow)
                    dr.Delete()
                End If
            Next
            dt.AcceptChanges()
        End Sub
    End Class
    Hope this helps

    Regards
    Jeff Shan
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Jeff Shan Friday, December 11, 2009 1:25 AM
    Thursday, December 10, 2009 8:50 AM
  • Thank you. I will try this. You've all been very helpful!

    Thursday, December 10, 2009 1:26 PM
  • I tried it and it worked! Thank you so much for your help!

    Friday, December 11, 2009 1:09 AM