-
April 20th, 2018, 12:50 PM
#1
[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.
Last edited by 2kaud; April 21st, 2018 at 03:44 AM.
Reason: Added code tags
-
April 21st, 2018, 03:45 AM
#2
Re: Managing MySQL Database with Adapters, DataTables, BindingSources and DataGrids
[When posting code, please use code tags. Go Advanced, select the formatted code and click '#'].
Cheers!
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
-
April 23rd, 2018, 07:29 AM
#3
Re: Managing MySQL Database with Adapters, DataTables, BindingSources and DataGrids
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
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|