[RESOLVED] checklistBox in MySQL (Save / Update)

Hi vbforums users!

I have the following code for Insert the checked items into mySQL:

Code:

---

  For i = 0 To CheckedListBox1.Items.Count - 1
            con = New MySqlConnection(cs)
            Dim cd As String = "INSERT INTO id_checklist(value,project,question) VALUES (@value,@project,@question)"

            cmd = New MySqlCommand(cd)

            cmd.Connection = con
            cmd.Parameters.AddWithValue("value", CheckedListBox1.GetItemChecked(i).ToString)
            cmd.Parameters.AddWithValue("project", txtproject.Text)
            cmd.Parameters.AddWithValue("question", CheckedListBox1.Items(i).ToString)

            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        Next

---

And works great, but my problem is to UPDATE the same checkedlistbox.. MySQL put all the value's in FALSE.. This is my code:

Code:

---

For i = 0 To CheckedListBox1.Items.Count - 1
            con = New MySqlConnection(cs)
            con.Open()
            'Dim cb1 As String = "UPDATE id_checklist SET value = value - '" & CheckedListBox2.GetItemChecked(i).ToString & "' WHERE project= '" & ListView1.Items(i).SubItems(1).Text & "'"
            Dim cb1 As String = "UPDATE id_checklist SET value ='" & CheckedListBox1.GetItemChecked(i).ToString & "' WHERE project= '" & txtproject.Text & "'"

            cmd = New MySqlCommand(cb1)
            cmd.Connection = con

            cmd.ExecuteReader()
            con.Close()
        Next
        MsgBox("Complete..")

---

any help?
Best Regards!!

Why are you doing the right thing for the insert and using parameters and then doing the wrong thing for the update and using string concatenation? Why are you also calling ExecuteReader for the updated when you're not reading anything? The code for the update should look EXACTLY the same as that for the insert with the exception of the SQL code. Nothing else changes. The SQL for the update should look like this:

sql Code:

UPDATE id_checklist SET value = @value WHERE project = @project AND question = @question

My previous post sorts out your specific issue but there are two other things that should be fixed in your code.

Firstly, why, why, why would you be storing the values as text? I'm not aware of any database that doesn't have a data type intended for Boolean values and MySQL is no different. It has `bool` and `boolean`, plus it has BIT that can store up to 64 Boolean values in one field. You should be using one of those data types and then actually saving the Boolean value returned by GetItemChecked, not the text returned by calling ToString on it. If you wouldn't use a String variable to store Boolean values in your VB code then why would you use columns intended for text to store Boolean values in your database?

Secondly, it is terribly bad practice to do as you're doing and create a new command for each item and open and close the connection for each one. The proper way to do that is to create one command and open the connection once, then simply set the parameters and execute inside the loop, finally closing the connection when you're all done.

With those two points in mind, here's how your first code snippet should be rewritten:

vb.net Code:

con = New MySqlConnection(cs)
 
Dim cd As String = "INSERT INTO id_checklist(value,project,question) VALUES (@value,@project,@question)"
 
cmd = New MySqlCommand(cd, con)
 
cmd.Parameters.Add("@value", MySqlDbType.Byte)
cmd.Parameters.AddWithValue("@project", txtproject.Text)
cmd.Parameters.Add("@question", MySqlDbType.VarString, 50)
 
con.Open()
 
For i = 0 To CheckedListBox1.Items.Count - 1
    cmd.Parameters("@value").Value = CheckedListBox1.GetItemChecked(i)
    cmd.Parameters("@question").Value = CheckedListBox1.GetItemText(i)
 
    cmd.ExecuteNonQuery()
Next
 
con.Close()

You may have to change the MySqlDbType values for the parameters depending on your actual database types.

Sr jmcilhinney, how are you??

Thank you very much for your comments and best- practices policies..

1. I save the string values ​​because MySQL return that value is "FALSE" or " TRUE" (output this values in crystal Reports viewer).
This development is about a checklist (with questions) and finally generate me a report.. I modified the value Bit ( 1) to store true or false when the checklistbox clicking change.. why is the better way to make this?

2. Thank you for the recommendation to open and close the connection .. this bad practice affects the database performance?

Best Regards!, the code works great!!

Quote:

---

Originally Posted by feroguz

Sr jmcilhinney, how are you??

Thank you very much for your comments and best- practices policies..

1. I save the string values ​​because MySQL return that value is "FALSE" or " TRUE" (output this values in crystal Reports viewer).
This development is about a checklist (with questions) and finally generate me a report.. I modified the value Bit ( 1) to store true or false when the checklistbox clicking change.. why is the better way to make this?

2. Thank you for the recommendation to open and close the connection .. this bad practice affects the database performance?

Best Regards!, the code works great!!

---

1. If you wanted to store the values True and False in VB, would you use a Boolean variable or a String variable. Whatever considerations you used to answer that question apply equally to the database. The fact that you want to display text values in your report is irrelevant to how you store the data. You simply translate the data between the database and the report, e.g. write the query to convert the Boolean values to text.

2. Absolutely. Opening and closing a connection takes time. ADO.NET is built in such a way that connections should not remain open when not being used but if multiple commands are being executed in succession then the connection is being used so it should not be closed in between.

Wow!!
Great comments jmcilhinney..
Now I know in the case of boolean , now is Easier to creating MySQL Load option ( Select) to request in the database if it is active or inactive in the listbox ..

How could I do to get my Select ( Load) to fill the active or inactive checklist option ?

Great, best regards!! :wave:

Quote:

---

Originally Posted by feroguz

Wow!!
Great comments jmcilhinney..
Now I know in the case of boolean , now is Easier to creating MySQL Load option ( Select) to request in the database if it is active or inactive in the listbox ..

How could I do to get my Select ( Load) to fill the active or inactive checklist option ?

Great, best regards!! :wave:

---

You'll notice that, based on the title, this thread is about saving the contents of a CheckedListBox to a database. You know how to do that so your issue is resolved, so the thread should have been marked Resolved and any new questions asked in new threads with titles that described the new topic. If it's helpful to provide a link back to an earlier thread then you can certainly do that. In future, please keep each thread to a single topic and each topic to a single thread, which makes the forums easier to use for everyone.

As the question is here now though, let's address it. You would query the database and then loop through the result set and add each item to the CheckedListBox. Based on the way you're saving the data, I would suggest using a data reader, so calling ExecuteReader on your command. If you need help on how to loop through a result set using a data reader, follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data.

For each record, you would first Add an item to the CheckedListBox and then call SetItemChecked based on the Boolean value contained in the record. I would suggest calling BeginUpdate before you start and EndUpdate when you're done, so that the control doesn't repaint after each item.