CODEWITHSUNDEEP
c#asp.netsql-server
Nistor Alexandru
Nistor Alexandru

Reputation: 5393

For each category, display its subcategory

I am kind of new to SQL and asp.net and am trying to create a forum application. I have created two tables. And I am selecting the data in the tables using this query:

SELECT 
    forum_categories.CategoryName, forum_subcategories.SubCategoryName  
FROM     
    forum_categories  
LEFT JOIN 
    forum_subcategories ON forum_categories.CategoryId = forum_subcategories.CategoryId

enter image description here

This is what the query returns:

enter image description here

Now what I need is for each CategoryName I need to display it's subcategories. I am using a ListView for this:

<asp:ListView ID="ListView1" runat="server" DataSourceID="ForumDataSource">
    <ItemTemplate>
        <div id='<%#Eval("CategoryName") %>' class="PostCategories">
            <h2><asp:Label ID="Label1" runat="server" Text='<%#Eval("CategoryName") %>'></asp:Label></h2>
            <table class="ForumPosts">
                <thead>
                    <td>Category</td>
                    <td>Posts</td>
                    <td>Last Post</td>
                </thead>
                <tbody>
                    <tr>
                        <td><asp:Label ID="Label2" runat="server" Text='<%#Eval("SubCategoryName") %>'></asp:Label></td>
                    </tr>
                </tbody>
            </table>
       </div>           
    </ItemTemplate>
    <EmptyDataTemplate>
        <p>No Data Found</p>
    </EmptyDataTemplate>
</asp:ListView>
<asp:SqlDataSource ID="ForumDataSource" runat="server" 
    ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True" 
    ProviderName="System.Data.SqlClient" 
    SelectCommand="SELECT forum_categories.CategoryName, forum_subcategories.SubCategoryName FROM forum_categories  LEFT JOIN forum_subcategories ON  forum_subcategories.CategoryId = forum_categories.CategoryId">
</asp:SqlDataSource>

Now as everyone might have already understood from the code this returns 8 tables.

What I would like to get is for each category to display it's subcategory data.In this case there should be only 3 tables

How should I approach this problem?

Should this be solved using SQL or manipulating the received data with a server side language?

Upvotes: 1

Views: 1246

Answers (1)

GayanSanjeewa
GayanSanjeewa

Reputation: 366

I'm not a big fan of ListViews. But you can try this out,

You need two list views. Outer listview and inner listview.

Outer one will loop through the categories hence it should give you only three tables. Make sure you select only the distinct ones.

Then inner listview will loop through each CategoryID and fetch the sub categories linked to CategoryID.

Upvotes: 2

Related Questions