Bind CheckList Box Using DataBase


Introduction

Bind CheckList Box Using DataBase Value and Store Selected Value in DataBase

Background

Create Two Table

1.    Flat
    field: 1) id 2) flatname
2.    User
    field: 1) userid 2) username
3.    UserFlateValue
    field: 1)id 2) userid

Using the code
//Bind CheckBox on page load
public void BindCheckBox()
{
        con.Open();
            string sql = "select id,flatname from flat";
            cmd = new SqlCommand(sql, con);
            da.SelectCommand = cmd;
            da.Fill(ds);
            CheckBoxList1.DataSource = ds;
            CheckBoxList1.DataBind();
            con.Close();
}

//This code for store selected data in database
protected void Submit_Click(object sender, EventArgs e)
{
        int YrStr = 0;
        int userid = 1;
        for (int i = 0; i < CheckBoxList1.Items.Count; i++)
        {
            if (CheckBoxList1.Items[i].Selected)
            {
                YrStr =Convert.ToInt32( CheckBoxList1.Items[i].Value);
                con.Open();
                string sql1 = "insert into flatper values("+YrStr+","+userid+")";
                cmd = new SqlCommand(sql1, con);
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
}

//bind Dropdown for user data
public void binddropdown()
{
            DataSet ds1 = new DataSet();
            con.Open();
            string sql2 = "select userid,name from [user]";
            cmd = new SqlCommand(sql2, con);
            da.SelectCommand = cmd;
            da.Fill(ds1);
            DropDownList1.DataSource = ds1;
            DropDownList1.DataBind();
            con.Close();
}

//display check box which selected in dropdown user
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
            DataSet ds2 = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter();

            DataSet ds3 = new DataSet();
            con.Open();
            string sql4 = "select * from flat where id in (select id from flatper where userid = " + DropDownList1.Text + ")";
            cmd = new SqlCommand(sql4, con);
            da.SelectCommand = cmd;
            da.Fill(ds3);
            CheckBoxList2.DataSource = ds3;
            CheckBoxList2.DataBind();
            con.Close();
}