Filling DataGridView row by row only populates las

Question

I'm trying to fill a datagridview with one column of integers using a loop. I'm pretty sure I've got the loop and excel reference correct, as I've used this on other projects, but it won't display correctly. Here's the code:

Dim da As OleDbDataAdapter
        Dim ds As DataSet

        xlWorkbook2 = xlApp.Workbooks.Open(FormFile)
        xlWsheet2 = xlWorkbook2.Sheets(sheetname)

        'open connection to nit spreadsheet'
        Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & FormFile & """;Extended Properties=""Excel 12.0;HDR=YES;""")

        'Fill dataviewgrid1 with element symbols, string'
        da = New OleDbDataAdapter("select * from [" & sheetname & "$A13:A" & lrow & "]", cn)
        ds = New System.Data.DataSet
        da.Fill(ds)
        DataGridView1.DataSource = ds.Tables(0)

        'Fill dataviewgrid2 with compositions, integers'
        With xlWsheet2
            For xrow As Integer = 13 To lrow
                DataGridView2.ColumnCount = 1
                DataGridView2.Rows.Add()
                DataGridView2.Item(0, xrow - 12).Value = xlWsheet2.Cells(xrow, 2).Value
                Console.WriteLine(xlWsheet2.Cells(xrow, 2).Value)
            Next
        End With

So dgv1 fills fine, no problem there. Dgv2 does not fill, or rather I get the proper number of rows, but only the last cell is populated. Because I've got the console.writeline command in there, I see that the code IS reading the excel spreadsheet successfully, so this must be a display thing? I've checked most of the easy display options, but nothings seems to change? Anyone have any ideas?

Answer 1

I'm pretty sure your datagridview has the AllowUserToAddRows property set to True. If so, every time you use DataGridView2.Rows.Add(), that row is added before the last one; that's because the last row is the one the user uses to manually add a row to your grid. But in your loop you are always editing this one row.

If your datagridview is going to allow users to manually add a row, you have to set the value of the previous to last row. A cleaner way to do this would be like this:

Dim index as Integer
With xlWsheet2
    DataGridView2.ColumnCount = 1
    For xrow As Integer = 13 To lrow
        index = DataGridView2.Rows.Add()
        DataGridView2.Item(0, index).Value = xlWsheet2.Cells(xrow, 2).Value
        Console.WriteLine(xlWsheet2.Cells(xrow, 2).Value)
    Next
End With

or simply

With xlWsheet2
    DataGridView2.ColumnCount = 1
    For xrow As Integer = 13 To lrow
        DataGridView2.Rows.Add(xlWsheet2.Cells(xrow, 2).Value)
        Console.WriteLine(xlWsheet2.Cells(xrow, 2).Value)
    Next
End With

(BTW, there's no need to do DataGridView2.ColumnCount = 1 on each loop)

Both of these ways will still work if you ever decide to change the AllowUserToAddRows to False.