[RESOLVED] Managing MySQL Database with Adapters, DataTables, BindingSources and DataGrids

[Huisman7]

Hello everyone,

I'm trying something a little new to me and could use some help.
I have a MySQL table, let's call it Table 1 with 5 fields
Table_Num, Date, Details, Funds_In, Funds_Out, Group_Num

I use the following code to fill my datagrid

MySQLCon.open
Dim daMain as MySQLDataAdapter = New MySQLDataAdapter("SELECT * FROM Table1 WHERE Group_Num = 0", MySQLCon)
Dim dtMain as new DataTable
daMain.fill(dtMain)
Dim bsMain as new BindingSource
bsMain.datasource = dtMain
dgMain.datasource = bsMain

Now I have a button that I want to push and it would do the following:
Get whatever row I have selected in dgMain datagrid and change the value of Group_Num to 1 (it is defaulted to 0)
This is how I get the Table_Num of the row I have selected:

Dim tableNum As Integer
If dgMain.CurrentRow IsNot Nothing Then
      If dgMain.CurrentRow.Selected = True Then
          tableNum = dgMain.Rows(dgMain.CurrentRow.Index).Cells(0).Value
      End If
End If

I then want that row to leave the dgMain and move to dgOther.
dgOther is bound to dtOther using daOther and bsOther with this ("SELECT * FROM Table1 WHERE Group_Num = 1", MySQLCon)

So I can easily accomplish this by updating the table with SQL statement
("UPDATE Table1 SET Group_Num = 1 WHERE Table_Num = tableNum", MySQLCon)

but then I would have to query the database again to update the datagrids.

I have read that since I already have this MySQL table bound to DataTables, I can just update them somehow and refresh the grids.......so I don't have to keep querying the database which could get slow the bigger it gets.

I was able to make it move datagrids by doing this:

Dim tablerow() As DataRow = dtMain.Select("Table_Num='" & tableNum & "'")
For Each row In tablerow
       dtOther.ImportRow(row)
       dtMain.AcceptChanges()
Next

But I don't really know what to do from here. The table itself doesn't change of course.....

I hope it makes sense what I'm trying to do and someone can help me.