Datalist issue with checkbox

[dot_net_rookie]

Hi

I am trying to bind two checkboxes in a datagrid. The 1st Checkbox is category and the 2nd Checkbox is sub category. A category can have several sub categories. When i bind the 1st checkbox the category name is repeated more than once and I need to have only one checkbox for one category. Please find the code below:

                 <asp:DataList ID="title_dl" runat="server" DataSourceID="title_dss" DataKeyField="M_CATPAGES_ID"  >


                    <ItemTemplate>

                        <td><asp:Checkbox ID ="cat_title_chk" AutoPostBack="true" runat="server" Text='<%# Container.DataItem("M_CATPAGES_CAT") %>'></asp:Checkbox> </td>   

                        <td><asp:CheckBox ID="sub_cat_chk" runat="server" Text='<%# Container.DataItem("M_PAGES_PAGES")  %>'></asp:Checkbox> </td>
                     </ItemTemplate>

                    </asp:DataList>

                     <asp:SqlDataSource ID="title_dss" runat="server" ConnectionString="Driver={MySQL ODBC 3.51 Driver};DATABASE=laqualite;option=0;pwd=cadt12;port=0;server=10.20.18.50;uid=root" DataSourceMode="DataReader" ProviderName="System.Data.Odbc" SelectCommand="Select M_PAGES_ID_PK,M_PAGES_PAGES,M_CATPAGES_ID,M_CATPAGES_CAT From mast_pages_tbl,mast_catpages_tbl Where M_CATPAGES_ID = M_PAGES_CATID and M_PAGES_ID_PK <> '27' Order By M_CATPAGES_CAT"></asp:SqlDataSource> 

[jbisono]

well i can tell you right now why.

you are retreiving category and sub category and the same resultset and your data is show as example.
Category Sub_Categor
Network CCNA
Network CCNP
Programming C#
Programming VB

and when you pass that to the datalist is going to embed the data as show above. and what you need is
Network CCNA
CCNP
Programming C#
VB
now are you doing that because somebody can select multiple sub_category at the same time?

[dot_net_rookie]

well i can tell you right now why.

you are retreiving category and sub category and the same resultset and your data is show as example.
Category Sub_Categor
Network CCNA
Network CCNP
Programming C#
Programming VB

and when you pass that to the datalist is going to embed the data as show above. and what you need is
Network CCNA
CCNP
Programming C#
VB
now are you doing that because somebody can select multiple sub_category at the same time?

Exactly that was the output I am looking for as I can select multiple sub categories under a category and the Category should not be repeated more than once

[jbisono]

ok, i do not know if this is the best solution but it work.

I test this try to replace your statement in values, notice the event OnItemDataBound that makes the trick.
aspx

<table>
        <asp:DataList ID="DataList1" runat="server" OnItemDataBound="bounditem">
        
        <ItemTemplate>
        <tr><td>
            <asp:CheckBox ID="chkCategory" runat="server" Text='<% #DataBinder.Eval(Container.DataItem, "OPTION_FNCTN").ToString() %>' /></td><td>
            <asp:CheckBoxList ID="ckhSubCategory" runat="server"></asp:CheckBoxList></td></tr>
        </ItemTemplate>
      
        </asp:DataList>
        </table>

Now in the onload page i just generate the first checkbox which is the category one. notice in the select statement the function DISTINCT.

protected void Page_Load(object sender, EventArgs e)
        {
            string st = "SELECT distinct OPTION_FNCTN FROM MENUOPTS_T WHERE (OPTION_FNCTN <> '') ORDER BY OPTION_FNCTN";
            DataSet ds = new DataSet();
            SqlDataAdapter adap = new SqlDataAdapter(st, MyConn);
            MyConn.Open();
            adap.Fill(ds);
            MyConn.Close();
            DataList1.DataSource = ds;
            DataList1.DataBind();
        }

now i create another function while the datalist is bounded this is the function.

protected void bounditem(object sender, DataListItemEventArgs e)
        {
            if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
            {
                string stGet = "SELECT OPTION_DESC FROM MENUOPTS_T WHERE (OPTION_FNCTN = '"+((CheckBox)e.Item.FindControl("chkCategory")).Text+"')";
                DataSet dsGet = new DataSet();
                SqlDataAdapter adapGet = new SqlDataAdapter(stGet, MyConn);
                MyConn.Open();
                adapGet.Fill(dsGet);
                MyConn.Close();
                ((CheckBoxList)e.Item.FindControl("chkSubCategory")).DataSource = dsGet;
                ((CheckBoxList)e.Item.FindControl("chkSubCategory")).DataTextField = "OPTION_DESC";
                ((CheckBoxList)e.Item.FindControl("chkSubCategory")).DataValueField = "OPTION_DESC";
                ((CheckBoxList)e.Item.FindControl("chkSubCategory")).DataBind();
            }
        }

i bet you there are a few ways to do this but, this is the first one that comes to my mind.

Regards

[dot_net_rookie]

ok, i do not know if this is the best solution but it work.

I test this try to replace your statement in values, notice the event OnItemDataBound that makes the trick.
aspx

<table>
        <asp:DataList ID="DataList1" runat="server" OnItemDataBound="bounditem">
        
        <ItemTemplate>
        <tr><td>
            <asp:CheckBox ID="chkCategory" runat="server" Text='<% #DataBinder.Eval(Container.DataItem, "OPTION_FNCTN").ToString() %>' /></td><td>
            <asp:CheckBoxList ID="ckhSubCategory" runat="server"></asp:CheckBoxList></td></tr>
        </ItemTemplate>
      
        </asp:DataList>
        </table>

Now in the onload page i just generate the first checkbox which is the category one. notice in the select statement the function DISTINCT.

protected void Page_Load(object sender, EventArgs e)
        {
            string st = "SELECT distinct OPTION_FNCTN FROM MENUOPTS_T WHERE (OPTION_FNCTN <> '') ORDER BY OPTION_FNCTN";
            DataSet ds = new DataSet();
            SqlDataAdapter adap = new SqlDataAdapter(st, MyConn);
            MyConn.Open();
            adap.Fill(ds);
            MyConn.Close();
            DataList1.DataSource = ds;
            DataList1.DataBind();
        }

now i create another function while the datalist is bounded this is the function.

protected void bounditem(object sender, DataListItemEventArgs e)
        {
            if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
            {
                string stGet = "SELECT OPTION_DESC FROM MENUOPTS_T WHERE (OPTION_FNCTN = '"+((CheckBox)e.Item.FindControl("chkCategory")).Text+"')";
                DataSet dsGet = new DataSet();
                SqlDataAdapter adapGet = new SqlDataAdapter(stGet, MyConn);
                MyConn.Open();
                adapGet.Fill(dsGet);
                MyConn.Close();
                ((CheckBoxList)e.Item.FindControl("chkSubCategory")).DataSource = dsGet;
                ((CheckBoxList)e.Item.FindControl("chkSubCategory")).DataTextField = "OPTION_DESC";
                ((CheckBoxList)e.Item.FindControl("chkSubCategory")).DataValueField = "OPTION_DESC";
                ((CheckBoxList)e.Item.FindControl("chkSubCategory")).DataBind();
            }
        }

i bet you there are a few ways to do this but, this is the first one that comes to my mind.

Regards

Hi Thank you very much for the solution...You have been very helpful..... I have a small problem in running the code due to an issue in the query in bounditem... I have reworked on the query as follows :

SELECT a.SubCat_Name FROM SubCategory Table as a,Category Table as b WHERE Cat_Name= '" & DirectCast(e.Item.FindControl("chkCategory"), CheckBox).Text & "') and a.SubCat_Cat_id=b.Cat_Id

I am binding the Category and Sub category from different tables

Category Table
Cat_Id
Cat_Name

SubCategory Table
SubCat_Id
SubCat_Name
SubCat_Cat_id

The SubCat_Cat_id from the SubCategory table is referred to the Cat_Id in Category Table.... But I am unable to get the results displayed but when i run the query in Sql Server it works fine...Please help me out....

[jbisono]

Try this

Select a.SubCat_Name from SubCategory as a inner join category as b on a.SubCat_Cat_id = b.Cat_id
where b.Cat_name = '" & DirectCast(e.Item.FindControl("chkCategory"), CheckBox).Text & "'

I do not know if your really need DirectCast cause you are just asking for the checkbox label text which is string.

regards.

[dot_net_rookie]

Thank you very much for the help provided ...The issue is fixed and its working fine...............