Bind CheckList Box Using DataBase

Bind CheckList Box Using DataBase Value and Store Selected Value in 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();
}