[RESOLVED] Retrieving and saving mysql database data to checkedlistbox in visual studio vb.net

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.

I dont have time to look through all your code, but one quick glance suggests you should be using a DataSet with parent/child datatables, 2 bindingsource, and 2 datagridview. Add a column to child datatable as boolean either before or after the load procedure so you can 'Select' rows in child data. Drop the ChkLstBox

Hi,

not sure if this is what you want, this will return all the Items in a CheckedLB = True or False

Code:

---

Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click
        Dim strSQL As String
        strSQL = "INSERT INTO (Field1 ,Field2,Field3 ,Field4 ) Table1 VALUES("

        For i As Integer = 0 To CheckedListBox1.Items.Count - 1
            Dim checked As Boolean = CheckedListBox1.GetItemChecked(i)
            Dim value As String = CheckedListBox1.Items(i).ToString
            strSQL = strSQL & "'" & String.Format("{0},{1}", checked, value)
        Next
        strSQL = strSQL & "')"
        MsgBox(strSQL)

    End Sub

---

HTH

Thank you for your response. I have updated my code, but it is showing an error message after the solution build is run. The error is System.OutOfMemoryException: 'List box contains too many items.' I searched on Google for this error and it seem to do with the GetString() returns an item which has a null value. I am not sure how to fix the issue. I hope that you can assist me with this issue. My code is:

Operation.vb (class)

Code:

---

  Public Function LoadData() As List(Of CheckListBoxItem)
        Dim itemList = New List(Of CheckListBoxItem)


        Using cn As MySqlConnection = New MySqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New MySqlCommand("SELECT OrderID, ProductID, Orders, CheckedStatus,FROM Store.Orders", cn)
                cmd.CommandText =
            <SQL>
                SELECT
                    OrderID,
                    ProductID,
                    Orders,
                    CheckedStatus
                FROM Orders;               
            </SQL>.Value

                Dim dt As New DataTable With {.TableName = "Orderss"}

                cn.Open()
                Dim reader = cmd.ExecuteReader()

                If reader.HasRows Then
                    While reader.Read
                        itemList.Add(New CheckListBoxItem With
                                    {
                                        .OrderID = reader.GetInt32(0),
                                        .ProductID = reader.GetInt32(1),
                                        .Orders = reader.GetString(2),
                                        .CheckedStatus = reader.GetBoolean(3)
                                    })

                    End While
                End If
                reader.Close()
               
            End Using
        End Using

        Return itemList

    End Function

---

CheckedListItem.vb (class)

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 Property OrderID As Integer
    Public Property Item As String
    Public Property ProductID As Integer
    Public Property Orders As String
    Public Property CheckedStatus As Boolean
    Public Property Quantity As String
    Public Property ColumnName As String
    Public Property 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 Item
        Return String.Empty
    End Function
End Class

---

Form1.vb

Code:

---

Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles MyBase.Load
  Dim ops = New Operations()

        ' read data from database table
        Dim Items = ops.LoadData()

        Dim LastIndex As Integer = 0

       
        For Each item As CheckListBoxItem In Items
            CheckedListBox1.Items.Add(item)

            LastIndex = CheckedListBox1.Items.Count - 1
            CheckedListBox1.SetItemChecked(LastIndex, item.Checked)

        Next

        AddHandler CheckedListBox1.ItemCheck, AddressOf CheckedListBox1_ItemCheck

---

Thank you in advance for your assistance.

Thank you for your response. I have updated my code, but it is showing an error message after the solution build is run. The error is System.OutOfMemoryException: 'List box contains too many items.' I searched on Google for this error and it seem to do with the GetString() returns an item which has a null value. I am not sure how to fix the issue. I hope that you can assist me with this issue. My code is:

Operation.vb (class)

Code:

---

  Public Function LoadData() As List(Of CheckListBoxItem)
        Dim itemList = New List(Of CheckListBoxItem)


        Using cn As MySqlConnection = New MySqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New MySqlCommand("SELECT OrderID, ProductID, Orders, CheckedStatus,FROM Store.Orders", cn)
                cmd.CommandText =
            <SQL>
                SELECT
                    OrderID,
                    ProductID,
                    Orders,
                    CheckedStatus
                FROM Orders;               
            </SQL>.Value

                Dim dt As New DataTable With {.TableName = "Orderss"}

                cn.Open()
                Dim reader = cmd.ExecuteReader()

                If reader.HasRows Then
                    While reader.Read
                        itemList.Add(New CheckListBoxItem With
                                    {
                                        .OrderID = reader.GetInt32(0),
                                        .ProductID = reader.GetInt32(1),
                                        .Orders = reader.GetString(2),
                                        .CheckedStatus = reader.GetBoolean(3)
                                    })

                    End While
                End If
                reader.Close()
               
            End Using
        End Using

        Return itemList

    End Function

---

CheckedListItem.vb (class)

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 Property OrderID As Integer
    Public Property Item As String
    Public Property ProductID As Integer
    Public Property Orders As String
    Public Property CheckedStatus As Boolean
    Public Property Quantity As String
    Public Property ColumnName As String
    Public Property 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 Item
        Return String.Empty
    End Function
End Class

---

Form1.vb

Code:

---

Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles MyBase.Load
  Dim ops = New Operations()

        ' read data from database table
        Dim Items = ops.LoadData()

        Dim LastIndex As Integer = 0

       
        For Each item As CheckListBoxItem In Items
            CheckedListBox1.Items.Add(item)

            LastIndex = CheckedListBox1.Items.Count - 1
            CheckedListBox1.SetItemChecked(LastIndex, item.Checked)

        Next

        AddHandler CheckedListBox1.ItemCheck, AddressOf CheckedListBox1_ItemCheck

---

Thank you in advance for your assistance.

Hi,

I think you are using the wrong Control for the Job.

here a sample with a Listview, I used the Northwind DB and the Orders table and added a Boolean Value 'Paid'
to that table.



here an Image
Attachment 165399

here the Code for Loading

Code:

---

Imports System.Data.OleDb

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        With ListView1
            If .Columns.Count = 0 Then
                .View = View.Details
                .CheckBoxes = True
                .Columns.Add("OrderID", 65)
                .Columns.Add("CustomerID", 65)
                .Columns.Add("Paid", 40)
            End If
        End With
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sDB As String = "E:\Northwind.mdb"
        Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=" & sDB & ";"

        Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
        Cn.Open()

        Dim sSQL As String = "Select  OrderID, CustomerID, OrderDate, Paid From Orders "


        Dim Cmd As New OleDb.OleDbCommand(sSQL, Cn)
        Dim Dr As OleDb.OleDbDataReader
        Dr = Cmd.ExecuteReader

        With ListView1
            If .Columns.Count = 0 Then
                .View = View.Details
                .Columns.Add("OrderID", 60, HorizontalAlignment.Left)
                .Columns.Add("CustomerID", 60, HorizontalAlignment.Right)
                .Columns.Add("Paid", 60, HorizontalAlignment.Right)
            End If
            .Items.Clear()
            .BeginUpdate()

            Do While Dr.Read
                Dim Li As New ListViewItem
                Li.UseItemStyleForSubItems = False
                Li.Text = Dr("OrderID").ToString
                Li.SubItems.Add(Dr("CustomerID").ToString)
                Li.SubItems.Add(Dr("Paid").ToString)

                If Dr("Paid").ToString = "True" Then
                    Li.Checked = True
                    Li.SubItems(2).Text = "Yes"
                Else
                    Li.Checked = False
                    Li.SubItems(2).Text = "No"
                    Li.SubItems(2).ForeColor = Color.AntiqueWhite
                    Li.SubItems(2).BackColor = Color.SlateGray
                End If
                .Items.Add(Li)
            Loop
            .EndUpdate()
        End With
        Dr.Close()
    End Sub

End Class

---

HTH

Hi ChrisE,

Thank you for the vb.net code. I will change my control from Checkedlistbox to a List Box. I have a couple of questions. I have Microsoft Access 2013 and I looked at the NorthWind db. it uses an OrderID with AutoNumber as the datatype. How did you add the Order Numbers and similarly with the Customer Names (I have seen vb.net code where they use DataMember and DataValue to show item name rather than the primary key or foreign key). Could you kindly provide a vb.net code. Thanks.

Hi ChrisE,

Thank you for the vb.net code. I will change my control from Checkedlistbox to a List Box. I have a couple of questions. I have Microsoft Access 2013 and I looked at the NorthWind db. it uses an OrderID with AutoNumber as the datatype. How did you add the Order Numbers and similarly with the Customer Names (I have seen vb.net code where they use DataMember and DataValue to show item name rather than the primary key or foreign key). Could you kindly provide a vb.net code. Thanks.

Quote:

---

Originally Posted by wire_jp

Hi ChrisE,

Thank you for the vb.net code. I will change my control from Checkedlistbox to a List Box. I have a couple of questions. I have Microsoft Access 2013 and I looked at the NorthWind db. it uses an OrderID with AutoNumber as the datatype. How did you add the Order Numbers and similarly with the Customer Names (I have seen vb.net code where they use DataMember and DataValue to show item name rather than the primary key or foreign key). Could you kindly provide a vb.net code. Thanks.

---

don't see how a Listbox is going to do a better Job.

Quote:

---

How did you add the Order Numbers and similarly with the Customer Names (I have seen vb.net code where they use DataMember and DataValue to show item name rather than the primary key or foreign key). Could you kindly provide a vb.net code. Thanks.

---

you can change to SQL to what you want, just adapt the sample I allready gave you

my apologies, I meant that I will adopt a Listview control.

Thank you for the help. The code works great!