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

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

Code:

---

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:

Code:

---

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:

Code:

---

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.

[When posting code, please use code tags. Go Advanced, select the formatted code and click '#'].

Cheers!

I figured it out with the help of a coworker and Google :)

I created different buttons that represent different groups that we want to change that record to. So I made the button's tag equal to the group number I want to change to. Then I have this code:

Code:

---

'Grab button that was pressed and assign group
        Dim button As Button = DirectCast(sender, Button)
        Dim groupnum As Integer = button.Tag

        'Get index of row selected
        If dgMain.SelectedRows.Count > 0 Then
            Dim index As Integer = dgMain.SelectedCells(0).RowIndex
            Dim indexRow As DataGridViewRow = dgMain.Rows(index)

            'Get AdNum of row selected
            Dim primaryKey As String = Convert.ToString(indexRow.Cells("table_num").Value)

            'Find that row in the datatable
            Dim drIndex As DataRow() = dtMain.Select("table_num='" & primaryKey & "'")

            'Change the group to the one we selected
            For Each row As DataRow In drIndex
                dtMain.Rows(dtMain.Rows.IndexOf(row))("group_num") = groupnum
            Next

            'Update the database
            cbMain = New MySqlCommandBuilder(daMain)
            daMain.UpdateCommand = cbMain.GetUpdateCommand(True)
            daMain.Update(dsMain, dtMain.TableName)
        End If

---