Hello,

I am using a MySQL database which is linked to a WinForm application in visual studio enterprise 2017.
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 pop-up dialog box 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). 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? My code is shown below: -

Class CheckListbox vb.net code: -
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: -
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: -

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
Thank you in advance for your help.