Retrieving,updating saving mysql db data to checkedlistbox

  • (2 Pages)
  • +
  • 1
  • 2

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

#16 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6737
  • View blog
  • Posts: 27,741
  • Joined: 12-December 12

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 06 February 2019 - 08:56 AM

Rather than removing ToString I would test briefly with Return "Foo" to see if you get a list of the word "Foo". Also check if the number of rows in the checked list is the same as the number of checked list items that you are expecting, this would be reassuring. (I would consider both as 'progress'.)
Was This Post Helpful? 0
  • +
  • -

#17 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 - 09:26 AM

Hi Andrewsw,

Yes, on running and building the solution, the solution returns the word "Foo" next to every checkbox.
Was This Post Helpful? 0
  • +
  • -

#18 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6737
  • View blog
  • Posts: 27,741
  • Joined: 12-December 12

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 06 February 2019 - 09:42 AM

... and are there as many items as you anticipate?

Anyway, so instead of "Foo" what text do you wish to appear next to each checkedlist item?
Was This Post Helpful? 0
  • +
  • -

#19 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 - 10:23 AM

Hi Andrewsw,

Yes, I confirm that the number of items which I anticipated are corrects. I would like the Order items to appear next to each checkedlist item.


Thanks
Was This Post Helpful? 0
  • +
  • -

#20 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6737
  • View blog
  • Posts: 27,741
  • Joined: 12-December 12

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 06 February 2019 - 10:45 AM

So... attempt something in ToString to achieve your desired output.
Was This Post Helpful? 0
  • +
  • -

#21 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 - 02:26 PM

Hi Andrewsw,

It worked well after I changed the vb.net code to the following i.e. the Order items now are displayed: -

Public Overrides Function ToString() As String
        Return Orders
    End Function


I have another issue where, I have a save button where I would to check/uncheck checkboxes and save the changes back to the MySQL database. The occurs when I click the save button and an error message appears: -

MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Orders] SET [CheckedStatus] = 0 WHERE OrderID = 0' at line 1'


The vb.net code in the Operations.vb class is: -

  Public Sub UpdateItems(ByVal items As List(Of CheckListBoxItem))

        Using cn As MySqlConnection = New MySqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As MySqlCommand = New MySqlCommand With {.Connection = cn}

                cmd.CommandText = "UPDATE SQL goes here with parameters"
                ' create parameters here

                cmd.CommandText = "UPDATE [Orders] SET [CheckedStatus] = @CheckedStatus WHERE OrderID= @MetricID"
                cmd.Parameters.Add(New MySqlParameter() With {.ParameterName = "@OrderID", .MySqlDbType = MySqlDbType.Int32})
                cmd.Parameters.Add(New MySqlParameter() With {.ParameterName = "@ProductID", .MySqlDbType = MySqlDbType.Int32})
                cmd.Parameters.Add(New MySqlParameter() With {.ParameterName = "@Orders", .MySqlDbType = MySqlDbType.VarChar})
                cmd.Parameters.Add(New MySqlParameter() With {.ParameterName = "@CheckedStatus", .MySqlDbType = MySqlDbType.Bit})
                cmd.Parameters.Add(New MySqlParameter() With {.ParameterName = "@Quantity", .MySqlDbType = MySqlDbType.VarChar})

                cn.Open()
                For Each item As CheckListBoxItem In items
                    ' set parameter values to items below then
                    ' do cmd.ExecuteNonReader

                    cmd.Parameters("@CheckedStatus").Value = item.Checked
                    cmd.Parameters("@OrderID").Value = item.PrimaryKey
                    cmd.ExecuteNonQuery()
                       Console.WriteLine($"{item.OrderID}, {item.ProductD}, {item.Orders}, {item.CheckedStatus},{item.Quantity} ")
                Next

            End Using

        End Using
    End Sub




In the Forms1.vb, the vb.net code for the save button is: -

Public Sub CheckedListBoxSaveButton_Click(sender As Object, e As EventArgs) Handles CheckedListBoxSaveButton.Click
        If CheckedListBox1.CheckedItems.Count > 0 Then
            Dim ops As New Operations
            ops.UpdateItems(
                (
                    From item In CheckedListBox1.CheckedItems
                    Select CType(item, CheckListBoxItem)).ToList
                )
        Else
            MessageBox.Show("Nothing checked")
        End If
    End Sub


Was This Post Helpful? 0
  • +
  • -

#22 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 - 02:26 PM

Hi Andrewsw,

It worked well after I changed the vb.net code to the following i.e. the Order items now are displayed: -

Public Overrides Function ToString() As String
        Return Orders
    End Function


I have another issue where, I have a save button where I would to check/uncheck checkboxes and save the changes back to the MySQL database. The occurs when I click the save button and an error message appears: -

MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Orders] SET [CheckedStatus] = 0 WHERE OrderID = 0' at line 1'


The vb.net code in the Operations.vb class is: -

  Public Sub UpdateItems(ByVal items As List(Of CheckListBoxItem))

        Using cn As MySqlConnection = New MySqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As MySqlCommand = New MySqlCommand With {.Connection = cn}

                cmd.CommandText = "UPDATE SQL goes here with parameters"
                ' create parameters here

                cmd.CommandText = "UPDATE [Orders] SET [CheckedStatus] = @CheckedStatus WHERE OrderID= @MetricID"
                cmd.Parameters.Add(New MySqlParameter() With {.ParameterName = "@OrderID", .MySqlDbType = MySqlDbType.Int32})
                cmd.Parameters.Add(New MySqlParameter() With {.ParameterName = "@ProductID", .MySqlDbType = MySqlDbType.Int32})
                cmd.Parameters.Add(New MySqlParameter() With {.ParameterName = "@Orders", .MySqlDbType = MySqlDbType.VarChar})
                cmd.Parameters.Add(New MySqlParameter() With {.ParameterName = "@CheckedStatus", .MySqlDbType = MySqlDbType.Bit})
                cmd.Parameters.Add(New MySqlParameter() With {.ParameterName = "@Quantity", .MySqlDbType = MySqlDbType.VarChar})

                cn.Open()
                For Each item As CheckListBoxItem In items
                    ' set parameter values to items below then
                    ' do cmd.ExecuteNonReader

                    cmd.Parameters("@CheckedStatus").Value = item.Checked
                    cmd.Parameters("@OrderID").Value = item.PrimaryKey
                    cmd.ExecuteNonQuery()
                       Console.WriteLine($"{item.OrderID}, {item.ProductD}, {item.Orders}, {item.CheckedStatus},{item.Quantity} ")
                Next

            End Using

        End Using
    End Sub




In the Forms1.vb, the vb.net code for the save button is: -

Public Sub CheckedListBoxSaveButton_Click(sender As Object, e As EventArgs) Handles CheckedListBoxSaveButton.Click
        If CheckedListBox1.CheckedItems.Count > 0 Then
            Dim ops As New Operations
            ops.UpdateItems(
                (
                    From item In CheckedListBox1.CheckedItems
                    Select CType(item, CheckListBoxItem)).ToList
                )
        Else
            MessageBox.Show("Nothing checked")
        End If
    End Sub


Was This Post Helpful? 0
  • +
  • -

#23 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6737
  • View blog
  • Posts: 27,741
  • Joined: 12-December 12

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 06 February 2019 - 02:48 PM

The parameters you are stating and providing don't look like they match up.
Was This Post Helpful? 0
  • +
  • -

#24 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 - 03:23 PM

Hi Andrewsw,

Sorry to bother, but I do not understand your comment. I removed the Quantity parameter, but the error still occurs. I forgot to mention that when I load the solution, I can uncheck any checkboxes, but I am unable to check checkboxes. I am not sure why this happens.

Thanks
Was This Post Helpful? 0
  • +
  • -

#25 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6737
  • View blog
  • Posts: 27,741
  • Joined: 12-December 12

Re: Retrieving,updating saving mysql db data to checkedlistbox

Posted 07 February 2019 - 01:32 AM

? Your UPDATE statement has two parameters. You then give values for 4/5, different, parameters.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2