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

Last Reply 15 days ago By pandeyism

Posted 15 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);
            }
        }
    }

 

 

Posted 15 days ago Modified on 15 days ago

Hi itsme,

Refer below sample.

HTML

<asp:DataList ID="DataList1" runat="server" RepeatColumns="3" RepeatDirection="Horizontal"
    OnItemCommand="DataList1_ItemCommand" 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" />
                    </td>
                </tr>
            </tr>
        </table>
    </ItemTemplate>
</asp:DataList>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        string query = "SELECT DISTINCT Category,Sub_category,Id FROM Category_tbl";
        DataTable dt = GetData(query, " ");
        DataList1.DataSource = dt;
        DataList1.DataBind();
    }
}

private static DataTable GetData(string query, string category)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand(query, con);
    if (!string.IsNullOrEmpty(category))
    {
        cmd.Parameters.AddWithValue("@category", category);
    }
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    return dt;
}

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 category = dataRowView["Category"].ToString();
        DataList subDataList = e.Item.FindControl("DataList2") as DataList;
        string query = "SELECT DISTINCT Category,Sub_category,Id FROM Category_tbl WHERE Category = @category";
        DataTable dt = GetData(query, category);
        subDataList.DataSource = dt;
        subDataList.DataBind();
    }
}

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

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim query As String = "SELECT DISTINCT Category,Sub_category,Id FROM Category_tbl"
        Dim dt As DataTable = GetData(query, " ")
        DataList1.DataSource = dt
        DataList1.DataBind()
    End If
End Sub

Private Shared Function GetData(ByVal query As String, ByVal category As String) As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim con As SqlConnection = New SqlConnection(constr)
    Dim cmd As SqlCommand = New SqlCommand(query, con)
    If Not String.IsNullOrEmpty(category) Then
        cmd.Parameters.AddWithValue("@category", category)
    End If
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    Dim dt As DataTable = New DataTable()
    da.Fill(dt)
    Return dt
End Function

Protected Sub DataList1_ItemDataBound(ByVal sender As Object, ByVal e As DataListItemEventArgs)
    If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
        Dim dataRowView As DataRowView = TryCast(e.Item.DataItem, DataRowView)
        Dim category As String = dataRowView("Category").ToString()
        Dim subDataList As DataList = TryCast(e.Item.FindControl("DataList2"), DataList)
        Dim query As String = "SELECT DISTINCT Category,Sub_category,Id FROM Category_tbl WHERE Category = @category"
        Dim dt As DataTable = GetData(query, category)
        subDataList.DataSource = dt
        subDataList.DataBind()
    End If
End Sub

Protected Sub DataList1_ItemCommand(ByVal source As Object, ByVal e As DataListCommandEventArgs)
    If e.CommandName = "viewdetails" Then
        ClientScript.RegisterClientScriptBlock(Me.[GetType](), "", "alert('" & "Id is : " & e.CommandArgument.ToString() & "')", True)
    End If
End Sub

Screenshot

 


Posted 15 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);