CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2014
    Posts
    3

    [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

  2. #2
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,822

    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)

  3. #3
    Join Date
    Sep 2014
    Posts
    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
  •  





Click Here to Expand Forum to Full Width

Featured