none
Retrieving and saving mysql database data to checkedlistbox in visual studio vb.net RRS feed

  • Question

  • Hello,

    I have trying to use KarenInstructor 's MSDN tutorial How to save and retrieve values from checkedlistbox to database  to retrieve and save mysql database data to checkedlistbox in visual studio vb.net. However, the tutorial used a Microsoft Access database as the reference database. I am unable to modify the oledb.adapter connection string to work for a MySQL connection string. The code is : -

    Public Class frmCheckListBoxFormDemo
        Private BuilderAccdb As New OleDb.OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
            }
        Private Sub LoadMe(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
    
            clbCheckedListBox.SuspendLayout()
    
            Dim dt As New DataTable
    
            Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = BuilderAccdb.ConnectionString
                }
    
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText =
                        <SQL>
                            SELECT 
                                Identifier, 
                                ItemIndex, 
                                Item, 
                                CheckedStatus 
                            FROM 
                                Items
                        </SQL>.Value
                    }
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
    
                End Using
            End Using
    
            Dim LastIndex As Integer = 0
    
            Try
                For Each row As DataRow In dt.Rows
    
                    clbCheckedListBox.Items.Add(row.Field(Of String)("Item"))
                    LastIndex = clbCheckedListBox.Items.Count - 1
    
                    If row.Field(Of Boolean)("CheckedStatus") Then
                        clbCheckedListBox.SetItemChecked(LastIndex, True)
                    End If
    
                Next
    
                ActiveControl = clbCheckedListBox
                clbCheckedListBox.SelectedIndex = 0
            Finally
                clbCheckedListBox.ResumeLayout()
            End Try
    
        End Sub
    

    My goal is to populate a checkedlistbox with the Surname of people and a checkbox from the MySQL database. One should be able to check the checkboxes and save the updates back to the MySQL database.

    Thank you in advance for your assistance

    Thursday, January 31, 2019 3:03 AM

Answers

  • Hello,

    Although I've not written a code sample for CheckedListBox and MySql, the main differences are

    • The Data provider e.g. for MS-Access OleDb, for SQL-Server SqlClient, in your case the data provider for MySql. All providers have a connection and command which is all you need. A 
    • All databases have SELECT, UPDATE and INSERT but each have slight variations on their syntax.

    I would ask you to look at the product code sample (it's SQL-Server but remember bullet 1), forget about Adapters. In the product code sample look at the Operations class for data operations, check out the class CheckListBoxItem.

    In the form, refer to the event checkedListBox1_ItemCheck where the update happens. Also examine the data operation in form closing event.

    This code sample

    https://code.msdn.microsoft.com/VBNET-Working-with-992e9095?redir=0

    I will be away from my computer until the morning so if there are more questions after examining the code I will look in the morning.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by wirejp Monday, February 4, 2019 1:05 AM
    Thursday, January 31, 2019 3:20 AM
    Moderator
  • Hello, 

    Sorry but I have never worked with MySql only Oracle, SQL-Server and MS-Access. If I had experience with MySql I would gladly assist but don't. 

    So you will need to do what everyone does when learning how to work with a specific database is to learn the syntax. What does not change is the interaction between read data and the CheckedListBox control. 

    In regards to WriteXmlDocument, the SELECT statement there is pure SQL-Server, doubt that MySql has an equivalent to FOR XML PATH.

    In short, when we get down to the SQL code itself e.g. there are parts that are SQL-Server specific and that means when hitting invalid SQL (for MySql) you will need to find what works in MySql.

    Public Function TableNames(ByVal cn As SqlConnection) As List(Of String)
        TableNameList = New List(Of String)
    
        Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
            cmd.CommandText =
                <SQL>
                    SELECT TABLE_NAME 
                    FROM INFORMATION_SCHEMA.TABLES 
                    WHERE TABLE_TYPE = 'BASE TABLE';
                </SQL>.Value
    
            Dim reader = cmd.ExecuteReader()
            If reader.HasRows Then
                While reader.Read
                    TableNameList.Add(reader.GetString(0))
                End While
            End If
            reader.Close()
        End Using
    
        Return TableNameList
    End Function
    

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by wirejp Monday, February 4, 2019 1:05 AM
    Sunday, February 3, 2019 11:03 PM
    Moderator

All replies

  • Hello,

    Although I've not written a code sample for CheckedListBox and MySql, the main differences are

    • The Data provider e.g. for MS-Access OleDb, for SQL-Server SqlClient, in your case the data provider for MySql. All providers have a connection and command which is all you need. A 
    • All databases have SELECT, UPDATE and INSERT but each have slight variations on their syntax.

    I would ask you to look at the product code sample (it's SQL-Server but remember bullet 1), forget about Adapters. In the product code sample look at the Operations class for data operations, check out the class CheckListBoxItem.

    In the form, refer to the event checkedListBox1_ItemCheck where the update happens. Also examine the data operation in form closing event.

    This code sample

    https://code.msdn.microsoft.com/VBNET-Working-with-992e9095?redir=0

    I will be away from my computer until the morning so if there are more questions after examining the code I will look in the morning.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by wirejp Monday, February 4, 2019 1:05 AM
    Thursday, January 31, 2019 3:20 AM
    Moderator
  • Hi Kareninstructor,

    Thank you for your prompt response, your advice and the weblink for VB.NET Working with CheckedListBox and SQL-Server table. I really appreciate your help. I have been able to retrieve data from the MySQL database into the Checkedlistbox. However, I am unable to insert or update the data in the Checkedlistbox and save the updated changes back to the MySQL database. If the retrieved MySQL data in the Checkedlistbox is checked, I can uncheck these checked "Checked Status" boxes, but I am unable to check any unchecked "Checked Status" boxes. When I click the "GetClicked" button, I received the following message: "You have an error in your SQL syntax, check the manual that corresponds to your MySQL server syntax for the right syntax to use near. 'XML PATH('Orders'), TYPE) For XML PATH(""), ROOT('Orders')' at line 1" (I used the  WriteXmlDocument code in the class Operations.vb and guess that this may not be correct since I do not have SQL server installed). I am working with a MySQL database with the following tables: - a Product table (Primary table) linked to the Orders table (child table). The Product table has the following fields: ProductID, Product Name and the Orders table has the following fields: OrderID, ProductID (foreign key), Description, Checked Status, and Quantity. The Checkedlistbox displays the Description and the Checked Status in visual studio. In visual studio, the Form has a binding source and binding navigator menu to navigate the records. When one loads the MySQL database data into the Checkedlistbox, the Checkedlistbox shows all of the Orders, as one navigates from one record to the next record using navigation buttons of the binding navigation menu. I would like the Checkedlistbox to show the Filtered results of each ProductID, as the user navigates the records in the Product Form1.vb. Can you assist me with the vb.net code for this? Referencing the example which was provided for Product Sample Code, my code is shown below: -

    Class CheckListbox vb.net code:

    Public Class CheckListBoxItem
        ''' <summary>
        ''' Identifier for database table
        ''' </summary>
        Public PrimaryKey As Integer
        ''' <summary>
        ''' Display member for CheckedListBox and a field in the table
        ''' </summary>
        Public Description As String
        Public ColumnName As String
        Public Checked As Boolean
        ''' <summary>
        ''' Used to determine if a item changed after loaded in the CheckedListBox
        ''' </summary>
        Public IsDirty As Boolean
        Public Overrides Function ToString() As String
            Return Description
        End Function
    End Class


    Class Operations vb.net code: 

    Imports MySql.Data.MySqlClient
    Imports System.Xml
    Public Class Operations
        ''' <summary>
        ''' Replace with your Datasource name
        ''' </summary>
        Private Datasource As String = "Localhost"
        ''' <summary>
        ''' Database in which data resides, see SQL_Script.sql
        ''' </summary>
        Private Catalog As String = "database"
        ''' <summary>
        ''' Connection string for connecting to the database
        ''' </summary>
        Private ConnectionString As String = ""
        Public HasErrors As Boolean
        Public ErrorMessage As String
        ' Friend ErrorMessage As String
    
        ''' <summary>
        ''' Setup the connection string
        ''' </summary>
        Public Sub New()
            ' To avoid storing the connection string in your code,  
            ' you can retrieve it from a configuration file.
            ConnectionString = "server=localhost;Port=3306;database=database;userid=root;password=password;persist security info=True"
        End Sub
    
        ''' <summary>
        ''' Get all records to show in the CheckedListBox
        ''' </summary>
        ''' <returns></returns>
        Public Function GetAll() As DataTable
            Dim dtDescription = New DataTable()
    
            Using conn As MySqlConnection = New MySqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As MySqlCommand = New MySqlCommand With {.Connection = conn}
    
                    cmd.CommandText = "SELECT OrderID, ProductID,Description, CheckedStatus FROM database.Description"
    
                    conn.Open()
                    dtDescription.Load(cmd.ExecuteReader())
    
                End Using
            End Using
    
            Return dtDescription
    
        End Function
        Public Function CanOrder(ByVal OrderID As Integer, ByVal Adding As Boolean) As Boolean
            Dim result = False
            Using cn As MySqlConnection = New MySqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As MySqlCommand = New MySqlCommand With {.Connection = cn}
                    cmd.CommandText = $"SELECT Quantity FROM Description WHERE OrderID = {OrderID}"
    
                    cn.Open()
                    Dim quantity = CInt(Fix(cmd.ExecuteScalar()))
                    result = quantity > 0
    
    
                    If Adding Then
                        quantity -= 1
                    Else
                        quantity += 1
                    End If
    
                    '                    
                    '                     * We could forego this check which would give a negative Quantity which then
                    '                     * a query could run that has Quantity less than zero which would be how many
                    '                     * are needed for backorders
                    '                     
                    If quantity > 0 Then
                        cmd.CommandText = "UPDATE [database].[Description] SET [Quantity] = @Quantity WHERE id = @OrderID"
                        cmd.Parameters.AddWithValue("@Quantity", quantity)
                        cmd.Parameters.AddWithValue("@OrderID", OrderID)
                        cmd.ExecuteNonQuery()
                    End If
                End Using
            End Using
    
            Return result
    
        End Function
        Public Function BackOrder() As DataTable
            Dim dt = New DataTable()
            Using cn As MySqlConnection = New MySqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As MySqlCommand = New MySqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT Description.OrderID, Description.Orders, Description.Quantity, FROM Description.Description " & "INNER JOIN Products ON Description.ProductID = Products.ProductID " & "WHERE (Description.Quantity < 0)"
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
                End Using
            End Using
    
            Return dt
    
        End Function
        Public Function GetDescriptionColumnName() As List(Of CheckListBoxItem)
            Dim columnNames = New List(Of CheckListBoxItem)()
    
            Using conn As MySqlConnection = New MySqlConnection With {.ConnectionString = ConnectionString}
    
                Using cmd As New MySqlCommand("SELECT OrderID, ProductID,Description, CheckedStatus FROM database.Description", conn)
    
                    conn.Open()
    
                    Dim reader = cmd.ExecuteReader()
                    If reader.HasRows Then
                        Do While reader.Read()
                            columnNames.Add(New CheckListBoxItem() With {.ColumnName = reader.GetString(0)})
                        Loop
                    End If
    
                End Using
            End Using
    
            Return columnNames
        End Function
    
        ''' <summary>
        ''' Write selected column to xml
        ''' </summary>
        ''' <param name="fields"></param>
        ''' <param name="fileName"></param>
        ''' <remarks>
        ''' I used a try-catch in case someone does not have proper permissions
        ''' or the file is open exclusively from a former run.
        ''' </remarks>
        Public Sub WriteXmlDocument(ByVal fields As String, ByVal fileName As String)
            Using cn As MySqlConnection = New MySqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As MySqlCommand = New MySqlCommand With {.Connection = cn}
                    cmd.CommandText = $"SELECT ( SELECT {fields} " & "FROM database.ProductMeterics  FOR XML PATH('Description') , TYPE) " & "FOR XML PATH('') , ROOT('Description')"
    
                    cn.Open()
    
                    Try
                        Dim reader = cmd.ExecuteReader()
                        Dim doc As New XmlDocument()
                        If reader.HasRows Then
                            Do While reader.Read()
                                Dim xmldecl As XmlDeclaration
                                xmldecl = doc.CreateXmlDeclaration("1.0", Nothing, Nothing)
                                xmldecl.Standalone = "yes"
                                'Add the new node to the document.
                                Dim root As XmlElement = doc.DocumentElement
                                doc.InsertBefore(xmldecl, root)
                                doc.Save(fileName)
                            Loop
                        End If
                        'doc.Load(reader)
                        'Create an XML declaration. 
    
    
                    Catch ex As Exception
                        HasErrors = True
                        ErrorMessage = ex.Message
                    End Try
                End Using
            End Using
        End Sub
    
        ''' <summary>
        ''' Update records
        ''' </summary>
        ''' <param name="items"></param>
        Public Sub Update(ByVal items As List(Of CheckListBoxItem))
    
            Using conn As MySqlConnection = New MySqlConnection With {.ConnectionString = ConnectionString}
    
                Using cmd As New MySqlCommand("SELECT OrderID, ProductID,Description, CheckedStatus FROM database.Description", conn)
    
                End Using
    
    
                Using cmd As MySqlCommand = New MySqlCommand With {.Connection = conn}
                    cmd.CommandText = "UPDATE [Description] SET [CheckedStatus] = @CheckedStatus WHERE OrderID = @OrderID"
                    cmd.Parameters.Add(New MySqlParameter() With {.ParameterName = "@CheckedStatus", .MySqlDbType = MySqlDbType.Bit})
                    cmd.Parameters.Add(New MySqlParameter() With {.ParameterName = "@OrderID", .MySqlDbType = MySqlDbType.Int32})
    
                    conn.Open()
    
                    For Each item As CheckListBoxItem In items
                        cmd.Parameters("@CheckedStatus").Value = item.Checked
                        cmd.Parameters("@OrderID").Value = item.PrimaryKey
                        cmd.ExecuteNonQuery()
                    Next
                End Using
            End Using
        End Sub
    
    
    End Class

    Form1.vb vb.net code: 

    Imports MySql
    Imports MySql.Data.MySqlClient
    
    Public Class Form1
        Inherits Form
    
    Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim ops = New Operations()
    
            ' read data from database table
            Dim dtDescription = ops.GetAll()
    
            Dim LastIndex As Integer = 0
    
            ' Here we iterate the rows in the DataTable 
            For Each row As DataRow In dtDescription.Rows
                ProductCheckedListBox1.Items.Add(New CheckListBoxItem() With {.Description = row.Field(Of String)("Description"), .PrimaryKey = row.Field(Of Integer)("OrderID"), .IsDirty = False})
    
                LastIndex = ProductCheckedListBox1.Items.Count - 1
                ProductCheckedListBox1.SetItemChecked(LastIndex, row.Field(Of Boolean)("CheckedStatus"))
    
            Next
    
            AddHandler ProductCheckedListBox1.ItemCheck, AddressOf ProductCheckedListBox1_ItemCheck
    End Sub
    
    
    
    Public Sub GetCheckedButton_Click(sender As Object, e As EventArgs) Handles GetCheckedButton.Click
            Dim fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Orders.xml")
    
            If ProductCheckedListBox1.CheckedItems.Count > 0 Then
                'Get selected column names into a comma delimited string with Description for use
                'in the SELECT statement in Operations
                Dim colNames = New List(Of String)()
                For index As Integer = 0 To ProductCheckedListBox1.Items.Count - 1
                    If ProductCheckedListBox1.GetItemChecked(index) Then
                        colNames.Add(String.Concat("Description", CType(ProductCheckedListBox1.Items(index), CheckListBoxItem).ColumnName))
                    End If
                Next
    
                Dim result = String.Join(",", colNames.ToArray())
                Dim ops = New Operations()
                ops.WriteXmlDocument(result, fileName)
                If ops.HasErrors Then
                    MessageBox.Show(ops.ErrorMessage)
                Else
                    MessageBox.Show("File saved")
                End If
            End If
    
        End Sub
    
    
        Private Sub ProductCheckedListBox1_ItemCheck(sender As Object, e As ItemCheckEventArgs)
            Dim items As CheckedListBox = CType(sender, CheckedListBox)
            If items.CheckedItems.Count > (maxNumberOfCheckedItems - 1) Then
                e.NewValue = CheckState.Unchecked
            End If
        End Sub
        Private Sub CheckedListBox_ItemCheck(sender As Object, e As ItemCheckEventArgs)
            CType(ProductCheckedListBox1.Items(e.Index), CheckListBoxItem).IsDirty = True
    
            If e.NewValue = CheckState.Checked Then
                If Not pOps.CanOrder(ProductCheckedListBox1.PrimaryKey(e.Index), True) Then
                    MessageBox.Show($"Sorry, the product [{ProductCheckedListBox1.Description(e.Index)}] is out of stock")
                    e.NewValue = CheckState.Unchecked
                End If
            Else
                pOps.CanOrder(ProductCheckedListBox1.PrimaryKey(e.Index), False)
            End If
        End Sub
    
    
        Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
    
            Dim items = New List(Of CheckListBoxItem)()
    
            For index As Integer = 0 To ProductCheckedListBox1.Items.Count - 1
                If CType(ProductCheckedListBox1.Items(index), CheckListBoxItem).IsDirty Then
                    items.Add(New CheckListBoxItem() With {.PrimaryKey = CType(ProductCheckedListBox1.Items(index), CheckListBoxItem).PrimaryKey, .Checked = ProductCheckedListBox1.GetItemChecked(index), .Description = CType(ProductCheckedListBox1.Items(index), CheckListBoxItem).Description})
                End If
            Next
    
            If items.Count > 0 Then
                Dim ops = New Operations()
                ops.Update(items)
            End If
        End Sub
    
    
    
    End Class

     



    • Edited by wirejp Sunday, February 3, 2019 9:32 PM
    Sunday, February 3, 2019 8:35 PM
  • Hello, 

    Sorry but I have never worked with MySql only Oracle, SQL-Server and MS-Access. If I had experience with MySql I would gladly assist but don't. 

    So you will need to do what everyone does when learning how to work with a specific database is to learn the syntax. What does not change is the interaction between read data and the CheckedListBox control. 

    In regards to WriteXmlDocument, the SELECT statement there is pure SQL-Server, doubt that MySql has an equivalent to FOR XML PATH.

    In short, when we get down to the SQL code itself e.g. there are parts that are SQL-Server specific and that means when hitting invalid SQL (for MySql) you will need to find what works in MySql.

    Public Function TableNames(ByVal cn As SqlConnection) As List(Of String)
        TableNameList = New List(Of String)
    
        Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
            cmd.CommandText =
                <SQL>
                    SELECT TABLE_NAME 
                    FROM INFORMATION_SCHEMA.TABLES 
                    WHERE TABLE_TYPE = 'BASE TABLE';
                </SQL>.Value
    
            Dim reader = cmd.ExecuteReader()
            If reader.HasRows Then
                While reader.Read
                    TableNameList.Add(reader.GetString(0))
                End While
            End If
            reader.Close()
        End Using
    
        Return TableNameList
    End Function
    

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by wirejp Monday, February 4, 2019 1:05 AM
    Sunday, February 3, 2019 11:03 PM
    Moderator