Re-populating checkboxlist in a GridView from SQL comma-separated string

Question

Here is my case.

I've saved data from CheckBoxList:

foreach (ListItem li in CheckBoxList1.Items)
{
    Steps = String.Join(", ", CheckBoxList1.Items.Cast<ListItem>()
                                     .Where(i => i.Selected));
}

It works fine but what I want is to get back this data from database in checkBoxList inside a DataGrid.

And bellow are many things I tried. Please help:

 <div align="center" style="margin-top: 50px;">
                <asp:GridView ID="GridView1" runat="server" BackColor="White" DataKeyNames="SNo"
                    OnRowUpdating="GridView1_RowUpdating" AutoGenerateColumns="False"
                    OnRowCancelingEdit="GridView1_RowCancelingEdit"
                    OnRowEditing="GridView1_RowEditing"
                    BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4"
                    ForeColor="Black" GridLines="Vertical" ShowFooter="True" OnRowDataBound="GridView1_RowDataBound">

                    <AlternatingRowStyle BackColor="White" />
                    <Columns>
                        <asp:BoundField DataField="TransactionName" HeaderText="Transaction Name" />
                        <asp:BoundField DataField="Category" HeaderText="Category" />
                        <asp:TemplateField HeaderText="Steps">
                            <ItemTemplate>
                                <asp:Label ID="lblBrand" runat="server" Text='<%#Eval("Steps") %>'>
                                </asp:Label>
                                <asp:CheckBoxList ID="CheckBoxList2" runat="server">
                                </asp:CheckBoxList>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:CheckBoxList ID="CheckBoxList1" runat="server">
                                </asp:CheckBoxList>
                            </EditItemTemplate>
                        </asp:TemplateField>
                        <asp:CommandField ShowEditButton="True" />

                    </Columns>
                    <EditRowStyle Height="10px" Width="2px" />
                    <FooterStyle BackColor="#CCCC99" />
                    <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
                    <RowStyle BackColor="#F7F7DE" />
                    <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
                    <SortedAscendingCellStyle BackColor="#FBFBF2" />
                    <SortedAscendingHeaderStyle BackColor="#848384" />
                    <SortedDescendingCellStyle BackColor="#EAEAD3" />
                    <SortedDescendingHeaderStyle BackColor="#575357" />

                </asp:GridView>

The C# code behind

  private void Bind_CheckBoxList()
{
    DataTable dt;
    String SQL = "SELECT * from TransactionDetail";


    string sConstr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(sConstr))
    {
        using (SqlCommand comm = new SqlCommand(SQL, conn))
        {
            conn.Open();
            using (SqlDataAdapter da = new SqlDataAdapter(comm))
            {
                dt = new DataTable("tbl");
                da.Fill(dt);
            }
            conn.Close();
        }
    }
    GridView1.DataSource = dt;
    GridView1.DataBind();

}


    if (e.Row.RowType == DataControlRowType.DataRow)
    {

        CheckBoxList c = (CheckBoxList)e.Row.FindControl("CheckBoxList2");
        DataTable dt;
        String SQL = "SELECT * FROM TransactionDetail";

        string sConstr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(sConstr))
        {
            using (SqlCommand comm = new SqlCommand(SQL, conn))
            {
                conn.Open();
                using (SqlDataAdapter da = new SqlDataAdapter(comm))
                {
                    if (c != null)
                    {
                        Label test = (Label)(e.Row.FindControl("lblBrand"));
                        Steps = test.Text;

                        string[] items = Steps.Split(new[] { ", " }, StringSplitOptions.None);

                        foreach (ListItem li in c.Items)
                            li.Selected = items.Contains(li.Text);

                    }
                    dt = new DataTable("tbl");
                    da.Fill(dt);
                }
            }
            c.DataSource = dt;
            c.DataTextField = what to write here to pass the array Text;
            c.DataValueField = "SNo";
            c.DataBind();
        }
    }

You basically do the reverse, split the string, loop all the items in the array and check if they exist in the CheckBoxList in the OnRowDataBound event. — VDWWD
Your code itself is correct. Tested it and it works. Check the items in the items array after the split and see if they match the ones in c — VDWWD

Hassan Alhag Hassan Alhag · Accepted Answer · 2017-02-27T10:53:16

Hey thanks i solve it by creating another table for Steps and same for Documents Required ,,, its now showing the checkBoxList that i want into the datagrid and bellow the Code

 if ((e.Row.RowState & DataControlRowState.Edit) == 0)
        {
            CheckBoxList c = (CheckBoxList)e.Row.FindControl("CheckBoxList2");
            CheckBoxList CheckBoxListDR = (CheckBoxList)e.Row.FindControl("CheckBoxList3");
            DataTable dt;
            String SQL = "SELECT distinct S.Steps, D.DocumentsRequired, T.TransactionID FROM [StepsIsSelectedTable] S JOIN TransactionDetail T " +
                " ON S.TransactionIDSteps = T.TransactionID JOIN DocumentsRequiredIsSelected D " +
                "ON D.TransactionIDDR = T.TransactionID  WHERE " +
                "TransactionIDDR ='" + TreeView1.SelectedValue + "' and S.StepsIsSelected='True' and D.DocumentsRequiredIsSelected='True'" +
                " group by S.Steps, D.DocumentsRequired ,T.TransactionID";

            string sConstr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(sConstr))
            {
                using (SqlCommand comm = new SqlCommand(SQL, conn))
                {
                    conn.Open();
                    using (SqlDataAdapter da = new SqlDataAdapter(comm))
                    {
                        dt = new DataTable("tbl");
                        da.Fill(dt);
                    }
                }

                c.DataSource = dt;
                CheckBoxListDR.DataSource = dt;
                c.DataTextField = "Steps";
                c.DataValueField = "TransactionID";
                CheckBoxListDR.DataTextField = "DocumentsRequired";
                CheckBoxListDR.DataValueField = "TransactionID";
                c.DataBind();
                c.SelectedValue = "TransactionID";
                CheckBoxListDR.DataBind();
                CheckBoxListDR.SelectedValue = "1";
            }
        }

Re-populating checkboxlist in a GridView from SQL comma-separated string

1 Answers