Question How can I read DataTable rows one by into a DatagridView

[Mazhar]

Instead of assigning Datagridview.datasource =MyDataTable , I want to read DataTable rows one by one into Datagridview (Due to some reasons). I tried as under but it shows the last row only.

can any body help pls?

 Private Sub BtnShowBills_Click(sender As System.Object, e As System.EventArgs) Handles BtnShowBills.Click
        GetConnect()
        Using cmd As New SqlCommand("USP_ShowVendorDetailBill", Conn)
            cmd.CommandType = CommandType.StoredProcedure
            Conn.Open()
            cmd.Parameters.AddWithValue("@PurBillNo", TextBox1.Text)
          
  Using reader As SqlDataReader = cmd.ExecuteReader
                Dim dt As New DataTable
                Dim i As Integer = 0
                
                dt.Load(reader)
                For i = 0 To dt.Rows.Count - 1
                    PurchaseGrid.Rows.Item(0).Cells(0).Value = dt.Rows(i)("ProductID")
                    PurchaseGrid.Rows.Item(0).Cells(1).Value = dt.Rows(i)("PurDescription") 
                    PurchaseGrid.Rows.Item(0).Cells(2).Value = dt.Rows(i)("PurQty")
                    PurchaseGrid.Rows.Item(0).Cells(3).Value = dt.Rows(i)("PurRate") 
                    PurchaseGrid.Rows.Item(0).Cells(4).Value = dt.Rows(i)("Amount")  

                Next
End Using
        End Using
    End Sub

 

[jmcilhinney]

Why would you want to do that? There is no good reason to do so. You should absolutely populate a DataTable and assign it to the DataSource of the grid unless you have a compelling reason to do otherwise.

Assuming that you do have such a reason, don't use a DataTable at all. It's silly to call Load to populate a DataTable from a data reader and then loop through the Rows of the DataTable when you could simply loop through the data reader directly.

As for your issue, the problem is this:

                For i = 0 To dt.Rows.Count - 1
                    [B][U][COLOR="#FF0000"]PurchaseGrid.Rows.Item(0)[/COLOR][/U][/B].Cells(0).Value = dt.Rows(i)("ProductID")
                    [B][U][COLOR="#FF0000"]PurchaseGrid.Rows.Item(0)[/COLOR][/U][/B].Cells(1).Value = dt.Rows(i)("PurDescription") 
                    [B][U][COLOR="#FF0000"]PurchaseGrid.Rows.Item(0)[/COLOR][/U][/B].Cells(2).Value = dt.Rows(i)("PurQty")
                    [B][U][COLOR="#FF0000"]PurchaseGrid.Rows.Item(0)[/COLOR][/U][/B].Cells(3).Value = dt.Rows(i)("PurRate") 
                    [B][U][COLOR="#FF0000"]PurchaseGrid.Rows.Item(0)[/COLOR][/U][/B].Cells(4).Value = dt.Rows(i)("Amount")  

                Next

Instead of adding a new row to the grid for each row in the table, you just keep overwriting the values in the first row of the grid. In that case, of course you're only going to see the last record because it's the last one you write. If you want to see all the records then you have to write each of them to a new row in the grid. If you really want to avoid binding then your code should look something like this:

While reader.Read()
    PurchaseGrid.Rows.Add(reader("ProductID"),
                          reader("PurDescription"),
                          reader("PurQty"),
                          reader("PurRate"),
                          reader("Amount"))
End While