Adding row to a DataGridView

Question

Yes, I know there are other posts on this but I can't understand what to change or where to make the solutions fit my code.

Error:

Rows cannot be programmatically added to the DataGridView's rows collection when the control is data-bound.

Code:

Public Sub dsLoadEvents()
    dsConnectionE = New OleDbConnection
    dsConnectionE.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=DataSourceDB.accdb"
    dsDataAdapterE = New OleDbDataAdapter
    dsDataAdapterE.SelectCommand = New OleDbCommand
    dsDataAdapterE.SelectCommand.Connection = dsConnectionE
    dsDataAdapterE.SelectCommand.CommandText = "SELECT * FROM Event"
    dsDataAdapterE.SelectCommand.CommandType = CommandType.Text
    dsConnectionE.Open()
    dsDataSetE = New DataSet
    dsDataAdapterE.Fill(dsDataSetE, "dataSetEvents")
    dsConnectionE.Close()
    Form3.dgdEvents.AutoGenerateColumns = True
    Form3.dgdEvents.DataSource = dsDataSetE
    Form3.dgdEvents.DataMember = "dataSetEvents"
End Sub

That is where the Events are loaded when the program is first opened. I am working on adding things to the database however I tried using the following code to add it to the DataGridView but it gave me the error above.

Form3.dgdMembers.Rows.Add(New String() {Form3.tbceid.Text, Form3.tbfn.Text, Form3.dtpesd.Value, Form3.dtpdob.Value, Form3.tbal2.Text, Form3.tbal1.Text, Form3.tbpgfn.Text, Form3.tbpgsn.Text, Form3.tbcpp.Text, Form3.tbelid.Text})

Possible duplicate of [Rows cannot be programmatically added to the datagridview's row collection when the control is data-bound](http://stackoverflow.com/questions/8708057/rows-cannot-be-programmatically-added-to-the-datagridviews-row-collection-when) — Jerrad, Apr 26 '17 at 11:22
Like the post says, you're supposed to add the row to the datasource (dsDataSetE), not the datagrid (dgdMembers). — Jerrad, Apr 26 '17 at 11:51
@Jerrad Since when I change dgdMembers to dsDataSetE it says "Rows is not a member of DataSet". — ItsSimplyEddie, Apr 26 '17 at 11:58
`dsDataSetE.Tables(0)` should get you a table you can add rows to. — Jerrad, Apr 26 '17 at 12:02

score 0 · Answer 1 · answered Apr 28 '17 at 10:32

Here is an example where I load data in a class, pass back a DataSet (we could just use a DataTable), load the DataTable into the DataGridView in form load.

In a button click event we cast the DataGridView DataSource to a DataTable and add a new row using hard coded data but of course this can come from TextBox controls as you are doing.

Class

Public Class Sample1
    Private Builder As New OleDbConnectionStringBuilder With
    {
        .Provider = "Microsoft.ACE.OLEDB.12.0",
        .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
    }
    Public Property dsDataSetE As DataSet
    Public Function LoadCustomers() As DataSet

        dsDataSetE = New DataSet

        Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
            Dim commandText As String = "SELECT Identifier, ContactTitle, Country, CompanyName FROM Customers"
            Dim dsDataAdapterE = New OleDbDataAdapter(commandText, Builder.ConnectionString)
            cn.Open()
            dsDataAdapterE.Fill(dsDataSetE, "Customers")
            dsDataSetE.Tables("Customers").Columns("Identifier").ColumnMapping = MappingType.Hidden
        End Using

        Return dsDataSetE

    End Function
End Class

Form code

Public Class StackOverFlowForm1
    Private Operations As New Sample1
    Private Sub StackOverFlowForm1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim ds As DataSet = Operations.LoadCustomers
        DataGridView1.DataSource = ds.Tables("Customers")
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        CType(DataGridView1.DataSource, DataTable) _
            .Rows.Add(New Object() {Nothing, "Owner", "USA", "My company"})
    End Sub
End Class

The above is one method to add data to a data bound DataGridView. We could have the code within the class placed into the form but it's best to separate backend operations from the front end code.

Hope this helps.

Adding row to a DataGridView

1 Answers1