CheckedListBoxControl Devexpress C# ---- Saving all items in MysQL Database

Question

Ok This is a new edit of this question, as you will see in my code below I make it so that I iterate through each item and then it is supposed to get the checked and unchecked value of each item and then save them in my MySql database, but well, the moment I save it does save but all items are saved in boolean true, not the boolean I'm assigning which is supposed to be Boolean value. Meaning that if an item is checked the boolean should be true while an item isn't checked the boolean should be false, and that is supposed to be saved, but... well it saves everything in true. How can I solve this problem I'm having?

private void SbtnGuarda_Click(object sender, EventArgs e)
    {
        try
        {
            String id = txtId.Text.Trim();               
            for (int i = 0; i < CLBCpermits.ItemCount; i++)
            {
                object Row = CLBCpermits.GetItem(i);
                DataRowView rew = Row as DataRowView;
                Boolean value = Convert.ToBoolean(CLBCpermits.GetItemCheckState((int)rew["SecOptionId"] - 1));
                int opti = (int)rew["SecOptionId"];                   
                string qry = string.Format("INSERT INTO TblSecProfile (Id, OptActive, SecOptionId) VALUES ('{0}', '{1}', '{2}');", id, value, opti);
                using (MySqlConnection conn = new MySqlConnection(Global.ConnectionStringMySql))
                {
                    conn.Open();

                    using (MySqlCommand comm = new MySqlCommand(qry, conn))
                    {
                        comm.ExecuteNonQuery();
                    }

                    conn.Close();
                }      
            }

            XtraMessageBox.Show("Permits assigned to profile", "Process Finished", MessageBoxButtons.OK, MessageBoxIcon.Information);
            SbtnGuarda.Enabled = false;
            SbtnActualiza.Enabled = true;

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error while giving permits", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

    }

Any help is greatly appreciated and thanks for bearing my like 4th time editing this post.

Answer 1

First of all, use query parameters. Your current code is dangerous. Look up "SQL Injection" if you aren't sure why I say that.

Now, using parameters, you'd have this:

var query = "INSERT INTO TblSecProfile (Id, OptActive, SecOptionId) VALUES (@id, @optActive, @secOptionId)";

And later...

comm.Parameters.Add("@id", MySqlDbType.Int32).Value = id;
comm.Parameters.Add("@optActive", MySqlDbType.Bit).Value = value;
comm.Parameters.Add("@secOptionId", MySqlDbType.Int32).Value = opti;
comm.ExecuteNonQuery();

My guess is that because you are supplying a String value to the BIT typed column in the query, conversion is taking over and converting everything to TRUE.