Add row in DataGridView with data-bound control

Question

I have a form with two datagridview that gets items from a mysql database.

My goal is to click an item of the first DGV and get it inserted into the second, then (pressing a button) update the table on the database.

The problem is that I cannot add a row (I get exception: Can not add rows programmatically to the set of rows in DataGridView when data-bound control) and, if I programmatically fill the last row (the row for manual inserting) the value is not acquired.

But if I fill manually the last row and press the "update button" the database become updated.

This is the code of "CellContentClick"

Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
    Dim DvgRow% = e.RowIndex
    Dim DvgCol% = e.ColumnIndex
    Dim SelCell$ = Me.DataGridView1.Item(DvgCol, DvgRow).Value.ToString.Trim()
    Dim IntColSel$ = Me.DataGridView1.Columns(DvgCol).HeaderText

    Dim Dgv2Rows% = Me.DataGridView2.RowCount

    If IntColSel.ToUpper = "REGION" Then
        'Here I get the value in the last row but is ignored being updated
        Me.DataGridView2.Item(0, Me.DataGridView2.RowCount - 1).Value = SelCell 
        'Here I get exception
        Me.DataGridView2.Rows.Add() 
    End If
End Sub

This is the code to fill the 2nd DGV:

Private Sub GetInDGV2(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button13.Click

    With DataGridView2
        .DataSource = Nothing
        .Rows.Clear()
        .Columns.Clear()
    End With
    cnString = "datasource=" + Server_Name + ";username= " + UserDB + _
        ";password=" + Password + ";database=" + Database_Name + ""
    sqlQRY2 = "Select Region from regions"
    conn = New MySqlConnection(cnString)
    Try
        conn.Open()
        da2 = New MySqlDataAdapter(sqlQRY2, conn)
        Dim cb2 As MySqlCommandBuilder = New MySqlCommandBuilder(da2)
        da2.Fill(ds2, "regions")
        DataGridView2.DataSource = ds2
        DataGridView2.DataMember = "regions"
    Catch ex As Common.DbException
        MsgBox(ex.ToString)
    Finally
        conn.Close()
    End Try
End Sub

I saw this question aswered but I wasn't able to translate from C an test it.

Thanks for any help.

Graffito Graffito · Accepted Answer · 2015-08-07T10:24:22

As DataGridview2 is bounded to a DataTable2, you cannot directly modify the cells. You must update DataTable2 by adding a new DataRow. Once added, DataGRidView2 will be automatically refreshed.

To get the RowValues required to populate your new DataRow, you may access to DataTable1 DataRow. To Get the DataRow of underlying DataTable1, corresponding to the current row of dataGridView1:

Dim selectedrow As DataRow = DirectCast(DataGridView1.CurrentRow.DataBoundItem, DataRowView).Row ;

Then create a new DataRow in DataTable2 with the values got from slectedrow.

Dim dataTable2 As DataTable = ds2("Regions") 
Dim newrow As DataRow = dataTable2.NewRow() 
For i As Integer = 0 To selectedrow.Count - 1
  newrow(i) = selectedrow(i)
Next
dataTable2.Rows.Add(newrow)

Add row in DataGridView with data-bound control

1 Answers