How do I copy 1 row from table A to same schema table b in VB Net

Question

Here's what I've been trying. The problem seems to be in committing the dataset to the database because I can see the data table in the console.

I've tried both the AcceptChanges before and after the Update and when I look at the State it shows as Added.

Here's the code:

   Dim ds As DataSet = New DataSet()
    Dim tblPicture As DataTable
    Dim tblNewPics As DataTable
    Dim tblPictureCount As Integer
    Dim i As Integer

    Dim conn As SqlConnection = New SqlConnection("Server=R850-PC\SQLEXPRESS2012;database=NewParts;Integrated Security=True;Persist Security Info=False")
    Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from Picture Where IsNew = 1", conn)
    da.Fill(ds, "Picture")
    tblPicture = ds.Tables("Picture")
    tblPictureCount = tblPicture.Rows.Count
    tblPictureCount = tblPictureCount - 1

    'SOURCE Picture table to the screen.
    Console.WriteLine("Source Table tblPicture has " & tblPictureCount.ToString & " Rows")

    For i = 0 To tblPictureCount
        Console.WriteLine("Row(" & i.ToString & ") = " & tblPicture.Rows(i)(3))
    Next

    '********************* ADD TO NEW TABLE DATA **************************************************************************************

    Dim da2 As SqlDataAdapter = New SqlDataAdapter("Select * from Picture_Temp", conn)
    da2.Fill(ds, "Picture_Temp")
    tblNewPics = ds.Tables("Picture_Temp")
    tblNewPics = tblPicture.Clone

    For i = 0 To tblPictureCount
        tblNewPics.ImportRow(tblPicture.Rows(i))
        Dim dr2 As DataRow = tblNewPics.Rows(i)
        dr2.SetAdded()
    Next
    da2.Update(ds.Tables("Picture_Temp"))
    tblNewPics.AcceptChanges()

    '******************* DEBUG ... check new database table
    Console.WriteLine()
    Console.WriteLine("Destination TABLE has " & tblNewPics.ToString & " Rows")
    For i = 0 To tblPictureCount
        Console.WriteLine("Row(" & i.ToString & ") = " & tblNewPics.Rows(i)(3))
    Next
    Console.WriteLine("RowState = " & tblNewPics.Rows(i - 1).RowState.ToString)
    Console.ReadLine()

    txtMessages.Text = "Done"

End Sub

Any ideas??

Where is the update statement? Just calling Update won't make it update the database without one, as far as I know. http://stackoverflow.com/questions/6833277/dataadapter-update-does-not-update-the-database — Nikki9696, Dec 31 '15 at 18:42

Steve · Answer 1 · 2015-12-31T18:48:19.067

0

You should remove that Clone line. Making a Clone means that you are creating a new DataTable. So when you call the Update the old one is still empty (or whatever was there at the time of the fill).
There is no need to call Clone if the current table has already the correct schema

Also you need to link a SqlCommandBuilder to your DataAdapter. This object will be responsible to create the appropriate sql statements required to update the table

 Dim builder = new SqlCommandBuilder(da2)
 da2.Update(ds.Tables("Picture_Temp"))

edited Dec 31 '15 at 18:48

answered Dec 31 '15 at 18:42

Steve

213,761
22
232
286

That did it!! Thanks so much for the feedback. I was stuck on it and obviously don't really know what I'm doing but, well the efforts there anyways ... slow but sure I guess. Thanks again for the help! – QEDDave Jan 01 '16 at 22:55

How do I copy 1 row from table A to same schema table b in VB Net

1 Answers1