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();
    }

that's a bad idea,use 1:n relationship..

Srinivas Reddy Thatiparthy
– Srinivas Reddy Thatiparthy

2010-08-04 17:06:22 +00:00
Commented Aug 4, 2010 at 17:06 — Srinivas Reddy Thatiparthy
– Srinivas Reddy Thatiparthy, Commented Aug 4, 2010 at 17:06

Justin Morgan · Accepted Answer · 2010-08-04 17:30:03Z

2

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.

edited Aug 4, 2010 at 17:30

answered Aug 4, 2010 at 17:16

Justin Morgan

30.7k13 gold badges82 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user301212 Over a year ago

i have 30 checkboxs do to think i need to do this 30 times?

Justin Morgan Over a year ago

Well, if all their values are columns in dt, this for loop should hit them all. Just make sure the datatypes of value and the corresponding column in your database have at least as many bits as you have checkboxes. Regular int has 32, short has 16, and long has 64. They match up to the SQL datatypes int, smallint, and bigint respectively.

Iain Ward · Accepted Answer · 2010-08-04 17:06:42Z

1

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

answered Aug 4, 2010 at 17:06

Iain Ward

9,9765 gold badges37 silver badges42 bronze badges

1 Comment

user301212 Over a year ago

i have 30 checkboxs to every Name i new in asp.net how i convert them to ints

Abe Miessler · Accepted Answer · 2010-08-04 18:07:06Z

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.

i like your way it look simple how i start to do it can you give me sample code for 2 categories and i convert it

Kirk · Accepted Answer · 2013-03-28 18:03:59Z

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>");
}

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related