Search code examples
arraysvb.netdatatableultrawingrid

Copying Rows from One DataTable to another Displays Nothing


I'm trying to copy rows from one DataTable to another, but only copy rows where the data is yet to be saved to the database table.

The SQL side of this is working fine, it's returning the correct number of columns, however, when I add the rows that were found to my second DataTable and set it as the DataSource for my grid, there is no data displayed, although there is a row that has been added, since the row selector is visible.

What am I doing wrong, and why isn't the data being copied with it?

lftable = New DataTable

Try
  For Each dc As DataColumn In lineTable.Columns
    lftable.Columns.Add()
  Next

  Dim ds As New DataSet

  For Each row As DataRow In lineTable.Rows
     Dim da As New OleDbDataAdapter("SELECT * FROM [Order_Freight] WHERE [Order_Number] = ? AND [Product_Code] <> ?", con)
     da.SelectCommand.Parameters.Add("@num", OleDbType.Integer).Value = orderNum
     da.SelectCommand.Parameters.Add("@prod", OleDbType.VarChar).Value = row.Item("Product_Code")
     da.Fill(ds)

     For Each dr As DataRow In ds.Tables(0).Rows
        Dim nRow = lftable.Rows.Add()
        nRow.ItemArray = dr.ItemArray()
     Next
 Next

 ugProducts.DataSource = lfTable

Screenshot of the grid after assigning it the DataSource

enter image description here


Solution

  • Fill the columns correctly:

    For Each dc As DataColumn In lineTable.Columns
        lftable.Columns.Add(new DataColumn(dc.ColumnName, dc.DataType)); 
    Next
    

    You can use the overload of the Row.Add function which allows you provide the ItemArray directly:

    For Each dr As DataRow In ds.Tables(0).Rows
          lftable.Rows.Add(dr.ItemArray)
          'nRow.ItemArray = dr.ItemArray() <-- remove
    Next