Populate (Bind) Nested DataList from single (one) table using C# and VB.Net in ASP.Net

Last Reply 10 days ago By pandeyism

Posted 10 days ago

I am trying to show all sub category in a single category in datalist but I am having a single table for category and sub category.

CREATE TABLE [dbo].[Category_tbl] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [Category]     VARCHAR (50) NULL,
    [Sub_category] VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
Category            Sub_category     Id
Doctors	            Dentists	      1
Doctors	            Cardiologists     2
Daily Needs	        Grocery	          3
Daily Needs	        Chemists	  4
Repairs	            AC	              5
Repairs	            Laptop	          6
On Demand Service   Carpenters	      7
On Demand Service   Plumber	          8

 

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
    <div style="margin-left: 30%">
        <asp:DataList ID="DataList1" runat="server" RepeatColumns="3" RepeatDirection="Horizontal" OnItemCommand="DataList1_ItemCommand" Width="600px" OnItemDataBound="DataList1_ItemDataBound">
    <ItemTemplate>
        <table class="auto-style1" border="1">
            <tr>
                <tr>
                    <td class="auto-style2">
                        <asp:Label ID="lblid" runat="server" Font-Bold="True" Text='<%# Eval("Id") %>' Visible="false"></asp:Label>
                    </td>
                </tr>
                <tr>
                    <td class="auto-style2">
                        <asp:Label ID="category" runat="server" Font-Bold="True" Text='<%# Eval("Category") %>'
                            CssClass="btn-lg"></asp:Label>
                    </td>
                </tr>
                <tr>
                    <td class="auto-style2">
                        <asp:DataList ID="DataList2" runat="server">
                            <ItemTemplate>
                                <table class="auto-style1" border="1">
                                    <tr>
                                        <td class="auto-style2">
                                            <asp:Label ID="id" runat="server" Font-Bold="True" Text='<%# Eval("Id") %>' Visible="false"></asp:Label>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td class="auto-style2">
                                            <asp:Label ID="Bname" runat="server" Font-Bold="True" Text='<%# Eval("Sub_category") %>'
                                                CssClass="btn-lg"></asp:Label>
                                        </td>
                                    </tr>
                                </table>
                            </ItemTemplate>
                        </asp:DataList>
                    </td>
                </tr>
                <tr>
                    <td class="auto-style2">
                        <asp:Button ID="Button1" runat="server" CommandArgument='<%# Eval("Id") %>' CommandName="viewdetails"
                            CssClass="btn btn-warning" Font-Bold="true" Text="View Details" Visible="false" />
                    </td>
                </tr>
            </tr>
        </table>
    </ItemTemplate>
</asp:DataList>
    </div>
</asp:Content>

 

    public partial class SS_SearchCategory : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {                
                string constr = ConfigurationManager.ConnectionStrings["Q_SS_regnConnectionString1"].ConnectionString;
                SqlConnection con = new SqlConnection(constr);
                SqlCommand cmd = new SqlCommand("select Distinct Category,Sub_category,Id from Category_tbl", con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                DataList1.DataSource = dt;
                DataList1.DataBind();
            }
        }

        protected void DataList1_ItemDataBound(object sender, DataListItemEventArgs e)
        {
            if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
            {
                DataRowView dataRowView = e.Item.DataItem as DataRowView;
                string id = dataRowView["Id"].ToString();
                DataList subDataList = e.Item.FindControl("DataList2") as DataList;
                DataTable dt = SubCategoryData();
                DataRow[] dr = dt.Select("Id = " + id);
                subDataList.DataSource = dr.CopyToDataTable();
                subDataList.DataBind();
            }
        }
        public DataTable SubCategoryData()
        {            
            string constr = ConfigurationManager.ConnectionStrings["Q_SS_regnConnectionString1"].ConnectionString;
            SqlConnection con = new SqlConnection(constr);
            SqlCommand cmd = new SqlCommand("select Distinct Category,Sub_category,Id from Category_tbl  ", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            DataList1.DataSource = dt;
            return dt;
        }

        protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
        {
            if (e.CommandName == "viewdetails")
            {
                ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('" + "Id is : " + e.CommandArgument.ToString() + "')", true);
            }
        }
    }

 

 

You are viewing reply posted by: pandeyism 10 days ago.
Posted 10 days ago
itsme says:
SqlCommand cmd = new SqlCommand("select Distinct Category,Sub_category,Id from Category_tbl", con);

 If you want avoid duplicate data so you need to select only category you can't select id and sub-category becase od ther having different data. refer below query.

SqlCommand cmd = new SqlCommand("select Distinct Category from Category_tbl", con);