Dataadapter for each datagridview wont work

Question

I have 3 datagridviews

datagridview1, datagridview2, datagridview3

each datagridview is being bind using different data adapters and different bindings

datapter1, dataadapter2, dataadapter3, binding1 binding2 and binding3

'binding dgv1 
datapter1.SelectCommand = mycmd
datapter1.Fill(Dset, "something")
binding1.DataSource = Dset.Tables("something1")
datagridview1.DataSource = binding1

'binding dgv2 
datapter2.SelectCommand = mycmd
datapter2.Fill(Dset, "something")
binding2.DataSource = Dset.Tables("something2")
datagridview2.DataSource = binding2

'binding dgv3 
datapter3.SelectCommand = mycmd
datapter3.Fill(Dset, "something")
binding3.DataSource = Dset.Tables("something3")
datagridview3.DataSource = binding3

When the time I wanted to update my datagridview1

Dim firstbuilder As New MySqlCommandBuilder(Me.dataadapter1)
Me.dataadapter1.Update(Me.binding1.DataSource)
Me.binding1.ResetBindings(False)

it prompts me with an error that

Missing the DataColumn 'located in datagridview3' in the DataTable 'of datagridview3' for the source column 'located in datagridview3'

I don't see why I have this error since I used different bindings and different dataadapters for each datagridview. Need some help to fix the issue...

Answer 1

I have struggled with the same issue for days some years ago... I created a custom Class in the end. Basically I created separate DataAdapters and -Commandbuillder for each DataTable. Not sure if this is the most efficient way, but it works:

'1 Dataset for the full DB
Public dbDataSet As New DataSet With {.CaseSensitive = False}

'1 Datatable, Dataadapter en Commandbuilder per TABLE
Private Listof_Datatables As New List(Of DataTable)
Private Listof_Dataadapters As New List(Of OleDbDataAdapter)
Private Listof_Command As New List(Of OleDbCommand)
Private Listof_Commandbuilder As New List(Of OleDbCommandBuilder)

Public Sub Load_Table(tablename As String)
    'Load the AccessTable to the DataSet
    Dim SQLstring As String = "SELECT * FROM " & tablename.ToUpper
    Dim dbCon As New OleDbConnection(_DBconnString_full)
    dbCon.Open()
    Listof_Dataadapters.Add(New OleDbDataAdapter)
    Listof_Command.Add(New OleDbCommand(SQLstring, dbCon))
    Listof_Dataadapters(Listof_Dataadapters.Count - 1).SelectCommand = Listof_Command(Listof_Command.Count - 1)
    Listof_Commandbuilder.Add(New OleDbCommandBuilder(Listof_Dataadapters(Listof_Dataadapters.Count - 1)))
    Listof_Commandbuilder(Listof_Commandbuilder.Count - 1).QuotePrefix = "["
    Listof_Commandbuilder(Listof_Commandbuilder.Count - 1).QuoteSuffix = "]"
    Listof_Dataadapters(Listof_Dataadapters.Count - 1).Fill(dbDataSet, tablename)
    Listof_Datatables.Add(dbDataSet.Tables(tablename))

    'Get primary keys
    Dim UCollist As New List(Of String)
    Dim mySchema As DataTable = CType(dbCon, OleDbConnection).GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New Object() {Nothing, Nothing, tablename})
    Dim columnOrdinalForName As Integer = mySchema.Columns("COLUMN_NAME").Ordinal
    For Each r As DataRow In mySchema.Rows
        UCollist.Add(r.ItemArray(columnOrdinalForName).ToString)
    Next

    'Set primary keys
    Dim keycolumn(UCollist.Count) As DataColumn
    For index = 0 To UCollist.Count - 1
        keycolumn(index) = dbDataSet.Tables(tablename).Columns(UCollist(index))
    Next
    dbDataSet.Tables(tablename).PrimaryKey = keycolumn

    dbCon.Close()
End Sub
Public Sub Update_Tables()
    Dim NrAdap As Integer = Listof_Dataadapters.Count
    For Adapterindex = 0 To NrAdap - 1
        Listof_Dataadapters(Adapterindex).Update(dbDataSet.Tables(Adapterindex))
    Next
End Sub