locked
Group Database records by value of column and display in Repeater RRS feed

  • Question

  • User-108355162 posted

    Hi guys!

    Yesterday I have been asking about grouping data in a Gridview.
    It is working thanks to your help, but my problem now is that the GridView can't display the data horizontally:

    I want the items to be displayed in a row like "A, B, C, D, ..."
    instead of
    A
    B
    C
    D
    .
    .

    Otherwise the page will get much too long.
    So I thought a nested repeater might be better.
    I have been searching the Internet for a solution, there are a few, but most of them are 15 years old and I can't get them to run.
    Can anyone of you show me how the nested repeater and the code behind needs to be implememted? 
    VB would be great.

    Or is there a way to make the Grid show the data horizontaly? 

    Productnumber Link Productgroup
    Car1 Link1 Cars
    Car2 Link2 Cars
    Plane1 Link3 Planes
    Plane2 Link4 Planes
    Bike1 Link5 Bikes
    Bike2 Link6 Bikes

    I want to group this by "Productgroup" and show it as a table like this:

    Cars
    Car1 Link1 Cars
    Car2 Link2 Cars
    Planes
    Plane1 Link3 Planes
    Plane2 Link4 Planes
    Bikes
    Bike1 Link5 Bikes
    Bike2 Link6 Bikes

    Thanks again for your help!

    Tom

    Thursday, March 21, 2019 10:08 AM

Answers

  • User839733648 posted

    Hi Thomas Elser,

    You could use Repeater ItemDataBound event to get the Productgroup and insert it to the repeater.

    I've made a sample and you coul refer to.

    .aspx.

    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
        <style>
            table {
                border-collapse: collapse;
            }
    
            table, td, th {
                border: 1px solid black;
            }
        </style>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:Repeater ID="Repeater1" runat="server" OnItemDataBound="Repeater1_ItemDataBound">
                    <HeaderTemplate>
                        <table>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <tr>
                            <td>
                                <asp:Label ID="lblProductnumber" runat="server" Text='<%# Eval("Productnumber") %>' />
                            </td>
                            <td>
                                <asp:Label ID="lblLink" runat="server" Text='<%# Eval("Link") %>' />
                            </td>
                            <td>
                                <asp:Label ID="lblProductgroup" runat="server" Text='<%# Eval("Productgroup") %>' />
                            </td>
                        </tr>
                    </ItemTemplate>
                    <FooterTemplate>
                        </table>
                    </FooterTemplate>
                </asp:Repeater>
            </div>
        </form>
    </body>
    </html>
    

    code-behind

    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Configuration
    
    		Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
    			If Not IsPostBack Then
    				BindRepeater()
    			End If
    		End Sub
    		Private Sub BindRepeater()
    			Dim constr As String = ConfigurationManager.ConnectionStrings("yourConnectionString").ConnectionString
    			Using con As New SqlConnection(constr)
    				Using cmd As New SqlCommand("SELECT * FROM tb_ProductTest", con)
    					Using sda As New SqlDataAdapter(cmd)
    						cmd.CommandType = CommandType.Text
    						Dim dt As New DataTable()
    						sda.Fill(dt)
    						Repeater1.DataSource = dt
    						Repeater1.DataBind()
    					End Using
    				End Using
    			End Using
    		End Sub
    
    		Private currentGroup As String
    		Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As RepeaterItemEventArgs)
    			If e.Item.ItemType = ListItemType.Item Then
    				currentGroup = CType(e.Item.FindControl("lblProductgroup"), Label).Text
    				e.Item.Controls.AddAt(0, New LiteralControl("<tr><td  colspan='3'>'" & currentGroup & "'</td></tr>"))
    
    			End If
    
    		End Sub
    

    result:

    Best Regards,

    Jenifer

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 22, 2019 8:19 AM
  • User-108355162 posted

    Hi Jenifer,

    in your code the Handler for the Page_Load event was missing:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

    Now it's working fine.

    Thank you

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 25, 2019 11:29 AM

All replies

  • User475983607 posted

    I recommend the Repeater Data Control over the GridView for this type if UI design.    The Repeater gives you more control over the UI.

    https://docs.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.repeater?view=netframework-4.7.2

    Thursday, March 21, 2019 10:29 AM
  • User-2054057000 posted

    I don't think you need nested repeater to do this. You can simply use 1 repeater and bind it with SQL Group By query.

    The query would be:

    SELECT count(*), productnumber, link
    FROM product
    GROUP BY Productgroup;

    Thursday, March 21, 2019 10:32 AM
  • User-108355162 posted

    Thanks, but I need some code for the nested scenario. The ones I found don't deliver.

    Thursday, March 21, 2019 10:34 AM
  • User475983607 posted

    Thanks, but I need some code for the nested scenario. The ones I found don't deliver.

    The before and after diagram above does not require a nested data control.  Are you trying to toggle (show/hide) sections by clicking the header?  Is so, this is a fairly simply JavaScript solution.  There are also fancy UI solutions like an Accordion.

    https://getbootstrap.com/docs/4.3/components/collapse/#accordion-example

    https://jqueryui.com/accordion/

    https://ajaxcontroltoolkit.devexpress.com/Accordion/Accordion.aspx

    Thursday, March 21, 2019 10:41 AM
  • User-108355162 posted

    No, all I want is that the records are grouped with the Groupheader (Produktgruppe) above them, please see above.

    Cars
    Car1    Car2   Car3 

    Bikes
    Bike1   Bike2   Bike3

    or even better:

    Cars                Bikes
    Car1                Bike1
    Car2                Bike2
    Car3                Bike2

    Planes            Rockets
    Plane1            Rocket1
    Plane2            Rocket2
    Plane3

    Thursday, March 21, 2019 10:47 AM
  • User839733648 posted

    Hi Thomas Elser,

    You could use Repeater ItemDataBound event to get the Productgroup and insert it to the repeater.

    I've made a sample and you coul refer to.

    .aspx.

    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
        <style>
            table {
                border-collapse: collapse;
            }
    
            table, td, th {
                border: 1px solid black;
            }
        </style>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:Repeater ID="Repeater1" runat="server" OnItemDataBound="Repeater1_ItemDataBound">
                    <HeaderTemplate>
                        <table>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <tr>
                            <td>
                                <asp:Label ID="lblProductnumber" runat="server" Text='<%# Eval("Productnumber") %>' />
                            </td>
                            <td>
                                <asp:Label ID="lblLink" runat="server" Text='<%# Eval("Link") %>' />
                            </td>
                            <td>
                                <asp:Label ID="lblProductgroup" runat="server" Text='<%# Eval("Productgroup") %>' />
                            </td>
                        </tr>
                    </ItemTemplate>
                    <FooterTemplate>
                        </table>
                    </FooterTemplate>
                </asp:Repeater>
            </div>
        </form>
    </body>
    </html>
    

    code-behind

    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Configuration
    
    		Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
    			If Not IsPostBack Then
    				BindRepeater()
    			End If
    		End Sub
    		Private Sub BindRepeater()
    			Dim constr As String = ConfigurationManager.ConnectionStrings("yourConnectionString").ConnectionString
    			Using con As New SqlConnection(constr)
    				Using cmd As New SqlCommand("SELECT * FROM tb_ProductTest", con)
    					Using sda As New SqlDataAdapter(cmd)
    						cmd.CommandType = CommandType.Text
    						Dim dt As New DataTable()
    						sda.Fill(dt)
    						Repeater1.DataSource = dt
    						Repeater1.DataBind()
    					End Using
    				End Using
    			End Using
    		End Sub
    
    		Private currentGroup As String
    		Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As RepeaterItemEventArgs)
    			If e.Item.ItemType = ListItemType.Item Then
    				currentGroup = CType(e.Item.FindControl("lblProductgroup"), Label).Text
    				e.Item.Controls.AddAt(0, New LiteralControl("<tr><td  colspan='3'>'" & currentGroup & "'</td></tr>"))
    
    			End If
    
    		End Sub
    

    result:

    Best Regards,

    Jenifer

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 22, 2019 8:19 AM
  • User-108355162 posted

    Hi Jenifer,

    thanks for that.
    Unfortunately that doesn't render any output?
    I tried it with my connection, page is empty.... Any idea?

    Thanks, Tom

    Friday, March 22, 2019 12:18 PM
  • User475983607 posted

    Unfortunately that doesn't render any output?
    I tried it with my connection, page is empty.... Any idea?

    Not without source code that reproduces the issue.

    Friday, March 22, 2019 4:14 PM
  • User-108355162 posted

    I used your code, just adjusted connectionString.

    Code behind:

    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Configuration
    
    Partial Class _Default
        Inherits System.Web.UI.Page
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            If Not IsPostBack Then
                BindRepeater()
            End If
        End Sub
        Private Sub BindRepeater()
            Dim constr As String = ConfigurationManager.ConnectionStrings("ConnectionStringTest").ConnectionString
            Using con As New SqlConnection(constr)
                Using cmd As New SqlCommand("SELECT * FROM tb_ProductTest", con)
                    Using sda As New SqlDataAdapter(cmd)
                        cmd.CommandType = CommandType.Text
                        Dim dt As New DataTable()
                        sda.Fill(dt)
                        Repeater1.DataSource = dt
                        Repeater1.DataBind()
                    End Using
                End Using
            End Using
        End Sub
    
        Private currentGroup As String
        Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As RepeaterItemEventArgs)
            If e.Item.ItemType = ListItemType.Item Then
                currentGroup = CType(e.Item.FindControl("lblProductgroup"), Label).Text
                e.Item.Controls.AddAt(0, New LiteralControl("<tr><td  colspan='3'>'" & currentGroup & "'</td></tr>"))
    
            End If
    
        End Sub
    End Class

    ASPX

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:Repeater ID="Repeater1" runat="server" OnItemDataBound="Repeater1_ItemDataBound">
                    <HeaderTemplate>
                        <table>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <tr>
                            <td>
                                <asp:Label ID="lblProductnumber" runat="server" Text='<%# Eval("Productnumber") %>' />
                            </td>
                            <td>
                                <asp:Label ID="lblLink" runat="server" Text='<%# Eval("Link") %>' />
                            </td>
                            <td>
                                <asp:Label ID="lblProductgroup" runat="server" Text='<%# Eval("Productgroup") %>' />
                            </td>
                        </tr>
                    </ItemTemplate>
                    <FooterTemplate>
                        </table>
                    </FooterTemplate>
                </asp:Repeater>
            </div>
        </form>
    </body>
    </html>

    Connection is OK, putting a Gridview and SqlDatSource on the page is working:

    Id Productnumber Link Productgroup
    1 Car1 Link1 Cars
    2 Car2 Link2 Cars
    3 Bike1 Link3 Bikes
    4 Bike2 Link4 Bikes
    Friday, March 22, 2019 5:19 PM
  • User839733648 posted

    Hi Thomas Elser,

    Have you checked that if there is any information on the console tab when using F12 developer tools?

    The codes you've used work well on my side.

    Best Regards,

    Jenifer

    Monday, March 25, 2019 6:51 AM
  • User-108355162 posted

    Hi Jenifer,

    no, only the Gridview renders. Nothing in F12 to see.

    I copied the files to a different computer with VS2012 to make sure that it got nothing to do with may machine, same result there. Repeater doesn't produce any output, only the grid. And also the Repeater on it's own (Grid deleted).

    Strange ...

    Thanks for your help & time!

    Best regards, Thomas

    Monday, March 25, 2019 7:36 AM
  • User839733648 posted

    Hi Thomas Elser,

    I suggest that you could use breakpoints to check if the datatable in code-behind has datas.

    Also, I suggest that you could bind the database directly using repeater to see if the repeater works.

    Besides, could you please provide the codes whicn work using Gridview?

    Best Regards,

    Jenifer

    Monday, March 25, 2019 9:14 AM
  • User-108355162 posted

    Hi Jenifer,

            <asp:GridView ID="Grid1" runat="server" AutoGenerateColumns="true" DataSourceID="SqlDataSource1"></asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString='<%$ ConnectionStrings:ConnectionStringTest %>' SelectCommand="SELECT [Id], [Productnumber], [Link], [Productgroup] FROM [tb_ProductTest]"></asp:SqlDataSource>
    

    When I bind the repeater to the same SqlDataSource the data show up fine.

    Monday, March 25, 2019 9:30 AM
  • User839733648 posted

    Hi Thomas Elser.

    In my opinion, it is the databind from code-behind has affected repeater not showing.

    But the behind-code works well on my side.

    I still suggest that you could use breakpoints to check what is going wrong in code-behind.

    Best Regards,

    Jenifer

    Monday, March 25, 2019 9:50 AM
  • User-108355162 posted

    Hi Jenifer,

    in your code the Handler for the Page_Load event was missing:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

    Now it's working fine.

    Thank you

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 25, 2019 11:29 AM