ASP.NET - How to store checkbox list values in database?

Asked By shazi on 01-Jul-11 03:17 AM
Hi ,

   I want to store check boxlist values in database . I am giving one field name as features and data type nvarchar 50.
   
   I can store only one values that is first values in check box list if i click all values also .

   How can i get it ?

    Pls any reply?
Ashish Kapoor replied to shazi on 01-Jul-11 03:21 AM
i had the same problem once in one of my projects... i donno if its a bug or something... 
to tackle this u can manually check what all values have been checked, and then insert these values as strings into the database
Reena Jain replied to shazi on 01-Jul-11 03:25 AM
hi,

Loop through the CheckBoxList, get the value of each checkbox which is 'checked' and seperate each item with a comma. here is the sample code for you
string interests = string.Empty;
     foreach (ListItem item in this.CheckBoxList1.Items)
       if (item.Selected)
         interests += item + ",";
 
 SqlCommand cmd = new SqlCommand("Insert into Demo values('" + interests + "')", cn); //Write Your Query to insert into database table
     cn.Open();
     int iCount = cmd.ExecuteNonQuery();
     cn.Close();

I would like to provide you the retrieve code also
void LoadCheckList()
  {
    string intersts = "";
    SqlCommand cmd = new SqlCommand("Select Interests from Demo", cn); // Write your Query i have taken as sample
    SqlDataReader dr;
    cn.Open();
    dr = cmd.ExecuteReader();
    if (dr.Read())
    {
      intersts = dr["Interests"].ToString();
    }
    cn.Close();
    string[] arr = intersts.Split(',');
    foreach (ListItem item in this.CheckBoxList1.Items)
    {
      foreach (string s in arr)
      {
        if (item.Text== s)
        {
          item.Selected = true;
        }
      }
    }
  }

Hope this will help you
shazi replied to Reena Jain on 01-Jul-11 03:38 AM
This is useful for me

Thnak You
welcome:)
Reena Jain replied to shazi on 01-Jul-11 04:18 AM
end of post
Riley K replied to shazi on 01-Jul-11 09:56 AM
I have done this for ListBox , similarly you can do with CheckBox list

protected void btnSave_Click(object sender, EventArgs e)
    {
      StringCollection sc = new StringCollection();

      foreach (ListItem item in lstItems.Items)// iterate through listitems
      {
        if (item.Selected)   // if item is selected
        {
          sc.Add(item.Text);
        }
      }
      InsertRecords(sc);
    }
// In the above I am adding all the selected items to string collection tehn passign it to a function InsertRecords
    protected void InsertRecords(StringCollection sc)
    {
      StringBuilder sb = new StringBuilder(string.Empty);

      foreach (string item in sc)
      {
       const  string sqlStatement = "Insert into employees values";
        sb.AppendFormat("{0}('{1}')", sqlStatement, item);

      }

      try
      {
        con.Open();
        SqlCommand cmd = new SqlCommand(sb.ToString(), con);
        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();
        Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Saved Records Successfully');",true);
        foreach (ListItem item in lstItems.Items)
        {
          if (item.Selected)
          {
            item.Selected = false;
          }                
        }
      }
      catch (Exception ex)
      {
        string msg = "Insert Error";
        msg += ex.Message;
        throw new Exception(msg);
      }

      finally
      {
        con.Close();
      }



    }
   
That's it