Asp.net nested repeater with variable columns/rows

Question

I want to display a sort of list of people and some information linked to the day of the month in a choosen interval, like this:

Emp.   | 01   | 02   | 03   |
Albert | aaa  | bbb  | ccc  |
Dana   | ccc  | aaa  | bbb  |
John   | bbb  | ccc  | aaa  |

I set up a repeater like this:

<asp:TextBox ID="txtDataDa" Type="date" runat="server" ToolTip="Data Da" autocomplete="off"></asp:TextBox>
<asp:TextBox ID="txtDataA" Type="date" runat="server" ToolTip="Data A" autocomplete="off"></asp:TextBox>
<asp:Button ID="btnVai" runat="server" Text="Vai" OnClick="btnVai_Click"/>

        <asp:Repeater ID="rptParent" runat="server">
                        <HeaderTemplate>
                            <table style="width: 100%;">
                                <tr>
                                    <td>Dip</td>
                        </HeaderTemplate>
                        <ItemTemplate>
                            <td>
                                <asp:Label ID="lblGiorno" Text='<%# DataBinder.Eval(Container.DataItem, "data", "{0:dd}")%>' runat="server" />
                            </td>
                            <%--<asp:Repeater ID="rptChild" runat="server">
                                <ItemTemplate>
                                </ItemTemplate>
                            </asp:Repeater>--%>
                        </ItemTemplate>
                        <FooterTemplate>
                            </tr>
                        </table>
                        </FooterTemplate>
                    </asp:Repeater>

The Eval bit inside the label get a day from a datatable generated from a start and an end date set with 2 calendar control, I manage this with this bit in code behind:

Protected Sub btnVai_Click(sender As Object, e As EventArgs)
        Dim data1, data2 As DateTime
        data1 = Date.ParseExact(txtDataDa.Text, "yyyy-MM-dd", CultureInfo.CreateSpecificCulture("en-US")).Date
        data2 = Date.ParseExact(txtDataA.Text, "yyyy-MM-dd", CultureInfo.CreateSpecificCulture("en-US")).Date

        Dim dtGiorni As New DataTable
        ' Define columns
        dtGiorni.Columns.Add("data", GetType(System.DateTime))
        dtGiorni.Columns.Add("giorno", GetType(System.String))
        ' Add a row of data
        Dim data As DateTime = data1.AddDays(-1)
        Dim ita As Globalization.CultureInfo = New Globalization.CultureInfo("it-IT")

        While data < data2
            data = data.AddDays(1)
            Dim nomeGiorno As String = ita.DateTimeFormat.GetDayName(data.DayOfWeek)
            dtGiorni.Rows.Add(data, nomeGiorno.Substring(0, 1).ToUpper() + nomeGiorno.Substring(1))
        End While

        rptParent.DataSource = dtGiorni
        rptParent.DataBind()
    End Sub

If I select like 1th of march and 31th it shows correctly all the header colums with the day written inside. Now I want to create an undefined number of row based on the result of a mssql query to display the people in the first column and all the info in the right day. I think I have to use a nested repeater but I don't know how to set it up with the rest of the table. Any help, please? Thanks

EDIT: I don't mind to use another method instead of repeater, but please give some hint to start

Stephen Wrighton · Answer 1 · 2019-03-21T14:48:16.913

I would not do it that way without an overriding reason.

I would either use a GridView with AutoGenerateColumns set to true or build the html on the server entirely (to be put into a literal defined on the aspx).

You could technically do it with a repeater, but it would be a lot of extra work for little reward. And would not be very readable code.

Edit: How to wire up a gridview

<asp:TextBox ID="txtDataDa" Type="date" runat="server" ToolTip="Data Da" autocomplete="off"></asp:TextBox>
<asp:TextBox ID="txtDataA" Type="date" runat="server" ToolTip="Data A" autocomplete="off"></asp:TextBox>
<asp:Button ID="btnVai" runat="server" Text="Vai" OnClick="btnVai_Click"/>
<asp:gridview id="grdList" runat="server" autogeneratecolumns="True" 
        emptydatatext="No data available." useaccessibleheader="false" >
</asp:gridview>

Then for code behind:

  Protected Sub btnVai_Click(sender As Object, e As EventArgs)
        Dim data1, data2 As DateTime
        data1 = Date.ParseExact(txtDataDa.Text, "yyyy-MM-dd", CultureInfo.CreateSpecificCulture("en-US")).Date
        data2 = Date.ParseExact(txtDataA.Text, "yyyy-MM-dd", CultureInfo.CreateSpecificCulture("en-US")).Date

       Using cn As New ODBCDataConnection(conString)
          cn.open
          Using cmd As OdbcCommmand = cn.createcommand

          cmd.commandtext = "your mysql statement"
          // Use parameters for date range

          grdList.DataSource = cmd.ExecuteReader()
          grdList.DataBind()

          end using
       End Using

    End Sub

Pivot will help you structure your data. learn.microsoft.com/en-us/sql/t-sql/queries/….

It changes a deep table to a wide one, and with dynamic sql you can have a variable number of columns in the pivot. stackoverflow.com/questions/198716/pivot-in-sql-2005#199763

but how to do it with a gridview? Especially for showing days on the top — Gabriele Cozzolino, Mar 21 '19 at 07:45
I know how to wire up the gridview, what I cannot figure out is how to show the data in the way I want. Days per columns and people per rows. Say my query is "select surname,DATA,DESCR from piano left join ANAGRAFICO on IDDIP=ID join orari on piano.IDORARIO=orari.ID where (DATA>='20190301' and data<='20190331') " — Gabriele Cozzolino, Mar 21 '19 at 13:11
That's what pivot is for. https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017. It changes a deep table to a wide one, and with dynamic sql you can have a variable number of columns in the pivoy https://stackoverflow.com/questions/198716/pivot-in-sql-2005#199763 — Stephen Wrighton, Mar 21 '19 at 13:44
Thank you @Stephen for pointing me in the right direction! Now I have one doubt about the pivot query I came up with, I just edit the answer or ask a new one? — Gabriele Cozzolino, Mar 21 '19 at 14:27
ok, maybe convert your comment to an answer so I can check it as the correct one — Gabriele Cozzolino, Mar 21 '19 at 14:45

Asp.net nested repeater with variable columns/rows

1 Answers1