Retrieving,updating saving mysql db data to checkedlistbox

  • (2 Pages)
  • +
  • 1
  • 2

24 Replies - 487 Views - Last Post: 07 February 2019 - 01:32 AM Rate Topic: -----

#1 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Retrieving,updating saving mysql db data to checkedlistbox

Posted 03 February 2019 - 09:14 PM

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:

CheckListbox (class) 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


Operations (class) 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


Thank you in advance for your help.

Is This A Good Question/Topic? 0
  • +

Replies To: Retrieving,updating saving mysql db data to checkedlistbox

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,242
  • Joined: 12-June 08

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 03 February 2019 - 09:58 PM

Why are you going so far around the bush to make an XML document go to some wonky class then to the SQL? Why not just a collection of objects and a loo?
Was This Post Helpful? 0
  • +
  • -

#3 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 04 February 2019 - 06:16 AM

View Postmodi123_1, on 03 February 2019 - 09:58 PM, said:

Why are you going so far around the bush to make an XML document go to some wonky class then to the SQL? Why not just a collection of objects and a loo?

Was This Post Helpful? 0
  • +
  • -

#4 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 04 February 2019 - 06:23 AM

Hi,

I was following a MSDN demo example which : https://code.msdn.mi...2e9095?redir=0. Can you help me with collection anda loo?

Thanks
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,242
  • Joined: 12-June 08

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 04 February 2019 - 06:53 AM

Page does not exist.
Was This Post Helpful? 0
  • +
  • -

#6 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 04 February 2019 - 09:37 AM

https://code.msdn.mi...92e9095?redir=0
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,242
  • Joined: 12-June 08

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 04 February 2019 - 09:48 AM

Okay.. let me back up and ask a few things. First - are you trying to save the data to a database or a stand alone XML file?
Was This Post Helpful? 0
  • +
  • -

#8 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 04 February 2019 - 02:01 PM

Thank you for your prompt response. I am trying to save the data back to a Mysql database. Thanks
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,242
  • Joined: 12-June 08

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 04 February 2019 - 02:17 PM

Then you don't need a XML.. you can blow out any of the 'WriteXmlDocument'.
Was This Post Helpful? 0
  • +
  • -

#10 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 04 February 2019 - 03:02 PM

ok, I will do so. What is next?

Thanks
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,242
  • Joined: 12-June 08

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 04 February 2019 - 03:04 PM

Make normal functions that take the information and do an insert? Before you try and abstract all that out to a class perhaps keep it close to the form.

https://www.dreaminc...-adapter-setup/
https://www.dreaminc...-command-setup/
Was This Post Helpful? 0
  • +
  • -

#12 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 04 February 2019 - 03:10 PM

Thanks for the links. I will review them and I will reply back, if I have any issues.
Was This Post Helpful? 0
  • +
  • -

#13 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 05 February 2019 - 07:11 PM

Hello,

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)
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)

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
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

End Sub



Thank you in advance for your help.
Was This Post Helpful? 0
  • +
  • -

#14 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,242
  • Joined: 12-June 08

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 05 February 2019 - 08:31 PM

Why do you set your SQL statement here:
06
	            Using cmd As New MySqlCommand("SELECT OrderID, ProductID, Orders, CheckedStatus,FROM Store.Orders", cn)



Then do it again here.. and use weird <sql> tags?
07
	                cmd.CommandText =
08
	            <SQL>
09
	                SELECT
10
	                    OrderID,
11
	                    ProductID,
12
	                    Orders,
13
	                    CheckedStatus
14
	                FROM Orders;                
15
	            </SQL>.Value




How many items are you trying to shove in a list box?

Quote

System.OutOfMemoryException: 'List box contains too many items.





Your second return will never happen. Why is it there?
22
	        Return Item
23
	        Return String.Empty

Was This Post Helpful? 0
  • +
  • -

#15 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 06 February 2019 - 06:28 AM

Hello,

I changed the Operations.vb class code to the following: -

Public Function LoadData() As List(Of CheckListBoxItem)
        Dim itemList = New List(Of CheckListBoxItem)
        Dim dt As New DataTable With {.TableName = "Orders"}

        Using cn As MySqlConnection = New MySqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As MySqlCommand = New MySqlCommand With {.Connection = cn}
                cmd.CommandText = "SELECT OrderID, ProductID, Orders, CheckedStatus FROM Stores.Orders"

                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()
                ' Return itemList
            End Using
        End Using

        Return itemList

    End Function



I removed the Function in the CheckedListBoxItem.vb class:

  Public Overrides Function ToString() As String
        Return Item
        Return String.Empty
    End Function


When I run and build the solution, the solutions loads but the Checkedlistbox displays checkboxes each with the words "Stores.CheckedListbox" instead of showing the name of specific item.


Any help will be greatly appreciated.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2