how to insert multiple checkbox list values into database in single column using c#

Question

i use this to select one checkbox to isselected column how i convert this to multi checkboxlist to single column i use many ways more than 3 days without success

 private void BindCheckBoxList()
    {
        DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection(GetConnectionString());
        try
        {
            connection.Open();
            string sqlStatement = "SELECT * FROM boby";
            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

            sqlDa.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                CheckBoxList1.RepeatColumns = 4; // set the number of columns in the CheckBoxList
                CheckBoxList1.DataSource = dt;
                CheckBoxList1.DataTextField = "Name"; // the items to be displayed in the list items
                CheckBoxList1.DataValueField = "Name"; // the id of the items displayed
                CheckBoxList1.DataBind();

                //Setting the Selected Items in the ChecBoxList based from the value in the database
                //to do this, lets iterate to each items in the list
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[i]["IsSelected"].ToString()))
                    {
                        CheckBoxList1.Items[i].Selected = Convert.ToBoolean(dt.Rows[i]["IsSelected"]);
                    }
                }
            }

        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        { 
            connection.Close();
        }
    }

    private void Update(string name, bool isSelected)
    {
        SqlConnection connection = new SqlConnection(GetConnectionString());
        SqlCommand cmd;
        string sqlStatement = string.Empty;
        try
        {
            connection.Open();
            sqlStatement = "UPDATE handymen SET IsSelected = @IsSelected WHERE Name = @BizName";
            cmd = new SqlCommand(sqlStatement, connection);
            cmd.Parameters.AddWithValue("@Name", name);
            cmd.Parameters.AddWithValue("@IsSelected", isSelected);
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert/Update error";
            msg += ex.Message;
            throw new Exception(msg);

        }
        finally
        {
            connection.Close();
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindCheckBoxList();
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string str = string.Empty;

        for (int i = 0; i < CheckBoxList1.Items.Count; i++)
        {
            if (CheckBoxList1.Items[i].Selected)
            {
                str = CheckBoxList1.Items[i].Text;
                Update(str, CheckBoxList1.Items[i].Selected);
            }
        }
        //ReBind the List to retain the selected items on postbacks

        BindCheckBoxList();
    }

Answer 1

Try this

protected void Button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection("Data Source=ANSA-PC\\SQLEXPRESS;Initial Catalog=pms;Integrated Security=True");
    String str = "";

    for (int i = 0; i <=CheckBoxList1.Items.Count-1; i++)
    {

        if (CheckBoxList1.Items[i].Selected)
        {

            if (str == "")
            {
                str = CheckBoxList1.Items[i].Text;
            }
            else
            {
                str += "," + CheckBoxList1.Items[i].Text;

            }

        }
    }
    con.Open();
    SqlCommand cmd = new SqlCommand("insert into aa(a)values('" +str + "')", con);
    cmd.ExecuteNonQuery();
    ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('ansari:u also got it')</script>");
}

Answer 2

Have you considered using a bit mask? Below is an explanation using selected categories as an example (not sure if categories will work in your situation but it illustrates the concept):

Begin by assigning a single binary digit to each value-

cat1  cat2  cat3 cat4
----  ----  ---- ----
1     2     4    8

Next you will add an integer column to your main table, we will call it options. When the number is converted to binary each digit will represent whether categories 1, 2, 3 or 4 are set. Example:

5 = 0101 in binary = cat1 is set, cat2 is not set, cat3 is set, cat4 is not set

id | name         | options
---------------------------
1  | name1        | 5
2  | name2        | 2
3  | name3        | 7
4  | name4        | 6

We can now use bitwise operations against the options column to determine what options are allowed. Examples:

To get all records that have category 2 set when we don't care about the other categories, perform the following operation:

2 & options = 2

This would return records 2,3 and 4.

---

To get all records that have cat2 and cat3 set we would perform the following bitwise operation:

6 & options = 6

This would return records 3 and 4

---

To get all records that have category 1 and 3 set we would perform the following bitwise operation:

5 & options = 5

This would return records 1 and 3.

---

ONLY category 3 set:

4 | Options = 4

---

Category 3 NOT set:

4 & options = 0

---

This is probably a hard concept to grasp so please let me know if you have any questions. It seems to me that it might be the simplest way to accomplish what you are trying to do once you can grasp the concept.

Answer 3

Well, if you're going to do that, the best way would be to combine them into a bitwise composite, perhaps like this:

int value = 0;      //or short or long, depending on the number of bits
int bitDegree = 1;  //or short or long, depending on the number of bits

for (int i = 0; i < dt.Rows.Count; i++)
{
    if (!string.IsNullOrEmpty(dt.Rows[i]["IsSelected"].ToString())
        && Convert.ToBoolean(dt.Rows[i]["IsSelected"]))
    {
        value += bitDegree;
    }

    bitDegree *= 2;
}

If you can, however, it would be best practice to use multiple Bit columns in your database instead of combining them.

Answer 4

Personally I wouldn't do it that way, its bad design. I'd create another table with all the values in there rather than trying to stuff them all into one column.

If you HAVE to though you could convert them to ints and separate them with a comma?

eg. 1,0,0,1,1 etc