ASP.Net Nested Repeater Logic Hell

Question

Looking for help with a tripple nested repeater logic issue I have.

Background
I am building a timetable system for booking rooms and resources pulling data from a SQL Server database.

Table Structure

Table 1 - tblrooms  
room_id (INT) PK  
room_name (varchar(50))  
room_resource (INT)  

Table 2 - tblperiods  
period_id (INT) PK  
period_time_start (DATETIME)  
period_time_end (DATETIME)  
period_name (nvarchar(50))  

Table 3 - tblbookings  
booking_id (INT) PK  
period_id (INT)  
room_id (INT)  
booking_status (INT)  
booking_date (DATETIME)  
booking_subject (nvarchar(50))

Problem I have for the most part got the repeaters pulling the data out as required however only the first column is ever populated and I am really struggling to work out how to pull the booking data out and build the timetable as required. (please see attached screenshot of what I have already got working)

ASPX Page Code

<asp:Repeater ID="drPeriods" runat="server" OnItemDataBound="drPeriods_OnItemDataBound">

        <HeaderTemplate>
        <table class="table table-striped table-bordered table-condensed">
            <tr>
                <th style="width:16.66%"><asp:Label ID="lblResourceHeader" runat="server" /></th>
        </HeaderTemplate>

         <ItemTemplate>

                <th style="width:16.66%"><asp:Label ID="lblPeriod" runat="server" Text='<%# Eval("period_name") %>' /> - <asp:Label ID="lblPeriodStart" runat="server" Text='<%# Eval("period_time_start") %>' /> to <asp:Label ID="lblPeriodEnd" runat="server" Text='<%# Eval("period_time_end") %>' /></th>

         </ItemTemplate>

         <FooterTemplate>
             </tr> 

             <asp:Repeater ID="drResources" runat="server" OnItemDataBound="drResources_OnItemDataBound">

                    <ItemTemplate>

                    <tr>
                        <td height="50">
                            <asp:Label ID="lblResource" runat="server" Text='<%# Eval("room_name") %>' />
                            <br /><asp:Label ID="lblResourceDetails" runat="server" />
                        </td>
                        <asp:Label ID="lblFreeBooking" runat="server" Visible="false" />

                        <asp:Repeater ID="drBookings" runat="server" OnItemDataBound="drBookings_OnItemDataBound">

                            <ItemTemplate>

                                <td height="50">
                                    <asp:Label ID="lblCellContent" runat="server" />
                                </td>

                            </ItemTemplate>

                        </asp:Repeater>

                    </tr>

                    </ItemTemplate>

                </asp:Repeater>

            </table>

         </FooterTemplate>

     </asp:Repeater>

Code Behind

Namespace Staff

Public Class Rb
    Inherits System.Web.UI.Page
    Private _periodId As Integer


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

        If Not IsPostBack Then

            txtDate.Text = Request.QueryString("d")

            'check for weekend dates, show message if it is.
            Dim iWeekday As Integer = Weekday(Request.QueryString("d"))

            If iWeekday = 1 Or iWeekday = 7 Then

                lblMsg.Text = "<div class='alert alert-info alert-block'><h4 class='alert-heading'><i class='icon-warning-sign'></i>&nbsp;It's the weekend.</h4><p>The date you have choosen is a weekend, resources cannot be booked on weekends.</p></div>"
                lblMsg.Visible = True
                drPeriods.Visible = False

            Else

                Dim objConnection As SqlConnection
                Dim objCommand As SqlCommand
                Dim objDataReader As SqlDataReader

                objConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("OPSDConnectionString").ConnectionString)

                'Get Periods
                objCommand = New SqlCommand("SELECT period_id, CONVERT(char(5), period_time_start, 108) AS period_time_start, CONVERT(char(5), period_time_end, 108) AS period_time_end, period_name FROM tblrb_periods", objConnection)

                Try
                    objConnection.Open()
                    objDataReader = objCommand.ExecuteReader()

                    If objDataReader.HasRows Then

                        drPeriods.DataSource = objDataReader
                        drPeriods.DataBind()
                        objDataReader.Close()

                    Else

                        drPeriods.Visible = False
                        lblMsg.Text = "<div class='alert alert-error alert-block'><h4 class='alert-heading'><i class='icon-warning-sign'></i>&nbsp;Error!</h4><p>Periods have not yet been setup, please set these up by selecting the periods tab above, if you cannot see this tab please ask the helpdesk administrator to set these up for you.</p></div>"
                        lblMsg.Visible = True
                        objDataReader.Close()

                    End If

                Catch ex As Exception

                    'Inform of the error
                    Elmah.ErrorSignal.FromCurrentContext().Raise(ex)

                Finally
                    objCommand.Dispose()
                    objConnection.Close()
                    objConnection.Dispose()

                End Try

            End If

        End If

    End Sub

    Protected Sub drPeriods_OnItemDataBound(sender As Object, e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles drPeriods.ItemDataBound

        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then

            _periodId = e.Item.DataItem("period_id")

        End If

        If e.Item.ItemType = ListItemType.Header Then

            Dim lblResourceHeader As Label = drPeriods.Controls(0).Controls(0).FindControl("lblResourceHeader")
            Dim layoutView As Integer = Request.QueryString("v")
            Select Case layoutView
                Case 1 ' Rooms
                    lblResourceHeader.Text = "Rooms"
                Case 2 ' Resources
                    lblResourceHeader.Text = "Resources"
                Case 3 ' Both
                    lblResourceHeader.Text = "Rooms &amp; Resources"
                Case Else
                    lblResourceHeader.Text = "Rooms &amp; Resources"
            End Select

        End If

        If e.Item.ItemType = ListItemType.Footer Then

            Dim objConnection As SqlConnection
            Dim objCommand As SqlCommand
            Dim objDataReader As SqlDataReader

            'find repeater in the footer of drPeriods (repeater)
            Dim drResources As Repeater = drPeriods.Controls(drPeriods.Controls.Count - 1).Controls(0).FindControl("drResources")
            Dim layoutView As Integer = Request.QueryString("v")

            'Get Rooms
            objConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("OPSDConnectionString").ConnectionString)

            Select Case layoutView
                Case 1 ' Rooms
                    objCommand = New SqlCommand("SELECT * FROM tblrb_rooms WHERE room_resource = 1 ORDER BY room_name", objConnection)
                Case 2 ' Resources
                    objCommand = New SqlCommand("SELECT * FROM tblrb_rooms WHERE room_resource = 2 ORDER BY room_name", objConnection)
                Case 3 ' Both
                    objCommand = New SqlCommand("SELECT * FROM tblrb_rooms ORDER BY room_name", objConnection)
                Case Else
                    objCommand = New SqlCommand("SELECT * FROM tblrb_rooms ORDER BY room_name", objConnection)
            End Select

            Try
                objConnection.Open()
                objDataReader = objCommand.ExecuteReader()

                drResources.DataSource = objDataReader
                drResources.DataBind()
                objDataReader.Close()

            Catch ex As Exception

                'Inform of the error
                Elmah.ErrorSignal.FromCurrentContext().Raise(ex)

            Finally
                objCommand.Dispose()
                objConnection.Close()
                objConnection.Dispose()

            End Try

        End If

    End Sub

    Protected Sub drResources_OnItemDataBound(sender As Object, e As System.Web.UI.WebControls.RepeaterItemEventArgs)

        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then

            Dim lblResourceDetails As Label = e.Item.FindControl("lblResourceDetails")
            If e.Item.DataItem("room_resource") <> 2 Then
                lblResourceDetails.Text = "[ <a href='#' class='withajaxpopover' title='Room Details' data-load='resourceManagerViewDetails.aspx?id=" & e.Item.DataItem("room_id") & "'>View Room Details</a> ]"
            End If

            Dim objConnection As SqlConnection
            Dim objCommand As SqlCommand
            Dim objDataReader As SqlDataReader
            Dim drBookings As Repeater = e.Item.FindControl("drBookings")
            Dim lblFreeBooking As Label = e.Item.FindControl("lblFreeBooking")

            'Get Bookings
            objConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("OPSDConnectionString").ConnectionString)
            objCommand = New SqlCommand("SELECT tblrb_bookings.booking_status, tblrb_bookings.booking_subject, tblrb_bookings.booking_notes FROM tblrb_bookings WHERE (tblrb_bookings.room_id = @room_id) AND (tblrb_bookings.booking_date = @booking_date) AND (tblrb_bookings.period_id = @period_id)", objConnection)

            objCommand.Parameters.Add("@room_id", SqlDbType.Int, 10).Value = e.Item.DataItem("room_id")
            objCommand.Parameters.Add("@period_id", SqlDbType.Int, 10).Value = _periodId
            objCommand.Parameters.Add("@booking_date", SqlDbType.DateTime).Value = Request.QueryString("d")

            Try
                objConnection.Open()
                objDataReader = objCommand.ExecuteReader()

                If objDataReader.HasRows Then

                    drBookings.DataSource = objDataReader
                    drBookings.DataBind()
                    objDataReader.Close()

                Else

                    'free period
                    lblFreeBooking.Text = "<td height='50'><div class='alert alert-block alert-success'><h4 class='alert-heading'><i class='icon-ok'></i>&nbsp;Free.</h4><p><a href='#'>Click here to book this resource.</a></p></div></td>"
                    lblFreeBooking.Visible = True

                End If

            Catch ex As Exception

                'Inform of the error
                Elmah.ErrorSignal.FromCurrentContext().Raise(ex)

            Finally
                objCommand.Dispose()
                objConnection.Close()
                objConnection.Dispose()

            End Try

        End If

    End Sub

    Protected Sub drBookings_OnItemDataBound(sender As Object, e As System.Web.UI.WebControls.RepeaterItemEventArgs)

        Dim lblCellContent As Label = e.Item.FindControl("lblCellContent")

        Select Case e.Item.DataItem("booking_status")
            Case 1
                'timetabled
                lblCellContent.Text = "<div class='alert alert-block alert-error'><h4 class='alert-heading'><i class='icon-warning-sign'></i>&nbsp;Timetabled.</h4><p>" & e.Item.DataItem("booking_subject") & "</p></div>"

            Case 2
                'user booked
                lblCellContent.Text = "<div class='alert alert-block'><h4 class='alert-heading'><i class='icon-warning-sign'></i>&nbsp;Booked.</h4><p>" & e.Item.DataItem("booking_subject") & ".</p></div>"

        End Select

    End Sub


    Protected Sub btnDateSelect_Click(sender As Object, e As System.EventArgs) Handles btnDateSelect.Click

        'quick reload of page
        Response.Redirect("resourceManager.aspx?v=" & Request.QueryString("v") & "&d=" & txtDate.Text)

    End Sub

End Class End Namespace

Can anyone help with how I can get the required layout so that when the timetable is built the rooms and periods contain the correct booking data for each cell in thier corresponding columns.

Thanks in advance for any pointers you can provide, having spent 3 days on this I could do with a fresh pair of eyes :-)

Regards,

Ozzie

Repeater Data Issue

score 3 · Accepted Answer · edited Aug 29 '13 at 12:36

First of all, I think you are over-complicating the way you're doing the control. If you were to use LINQ or EF for your database access, you could do this with 2 nested repeaters and no complicated code behind. However, you can start by simplifying the query down to something like this:

SELECT *
FROM tblperiods p
    CROSS JOIN tblrooms r
    LEFT JOIN tblbookings b ON p.period_id = b.period_id AND r.room_id = b.room_id AND b.booking_date = '2012-08-01'

This will get you enough information to produce the whole output in a single query for a given day. If you don't want to use EF or LINQ-to-SQL, you can still do a bit of LINQ magic to separate this out further by room, which you can then use for your databinding (forgive the C#, VB.NET is a bit rusty!):

DataTable dt = new DataTable();
dt.Fill(objDataReader);          // Loads all data into the DataTable
var groupedRows = dt.Rows.Cast<DataRow>().GroupBy(row => new { RoomId = (int) row["room_id"], RoomName = (string) row["room_name"] });
rpRows.DataSource = groupedRows;
rpRows.DataBind();

Then you would have Repeaters like so:

<table>
<thead><tr><th>Rooms &amp; Resources</th>
<asp:Repeater runat="server" id="rpHeader">
    <ItemTemplate>
        <td><%# Eval("period_name") %></td>
    </ItemTemplate>
</asp:Repeater>
</tr></thead>
<asp:Repeater runat="server" id="rpRows">
    <ItemTemplate>
        <tr>
            <th><!-- Put room header stuff here --><%# Eval("Key.RoomName") %></th>
            <asp:Repeater runat="server" DataSource="<%# Container.DataItem %>">
                <ItemTemplate>
                    <td>
                         <!-- Per-booking info -->
                         <asp:Label runat="server" Visible='<%# Eval("booking_id") == DBNull.Value %>'>Not Booked</asp:label>
                         <asp:Label runat="server" Visible='<%# Eval("booking_id") != DBNull.Value %>'>Booked!</asp:label>
                    </td>
                </ItemTemplate>
            </asp:Repeater>
        </tr>
    </ItemTemplate>
</asp:Repeater>
</table>

I've not given the code for databinding the header row, but this is simply a select * from tbl_periods

Note that none of this code was tested, and probably needs a little adjusting on the data binding.

thanks for the SQL never thought of pulling it out in one quick hit. Dumping into a datatable makes alot of sense too. So in order to get the period headers I would still need 3 nested repeaters correct? have added your suggestions above but upon running I get an error that room_name does not exist looking at the data I see column headers do not exist how would I reference the room_name by its position in the datatable, in this case 5? — OPSL, Aug 05 '12 at 19:48
got the room names in now, my mistake not paying attention :-) Having more of a play with your suggestions and will letyou know how I get on with it. — OPSL, Aug 05 '12 at 20:42
You'd need a third repeater, but it wouldn't be nested. I've amended by answer to show where it would go. As for the RoomName, the intention was to access it from the Key property of the grouping - that's where the .Select(new { ... }) bit after GroupBy came in — Richard, Aug 05 '12 at 20:42
I figured out where the period repeater would go however when binding the first of the nested repeaters i get the following error: The DataSourceID of 'drBookings' must be the ID of a control of type IDataSource. A control with ID 'System.Linq.Lookup`2+Grouping[VB$AnonymousType_2`2[System.Int32,System.String],System.Data.DataRow]' could not be found. drBookings is the per booking info repeater and do not understand the error. — OPSL, Aug 05 '12 at 20:48
Sounds like you've set the `DataSourceId` property instead of `DataSource` — Richard, Aug 05 '12 at 20:51
wow how did I miss that, OK 10pm time for a well earned rest. Many thanks Richard I'll give you a shout tomorrow once I get the booking data in and displayed, will mark as answered then, cheers mate ! — OPSL, Aug 05 '12 at 21:00
thanks for the pointers I can confirm all is now working as expected with a little tweaking on the databinding as you assumed. Many thanks again :-) — OPSL, Aug 06 '12 at 15:49

score 0 · Answer 2 · edited Aug 05 '12 at 18:20

0

One thing that leaps out at me is this line...

Dim drResources As Repeater = drPeriods.Controls(drPeriods.Controls.Count - 1).Controls(0).FindControl("drResources")

Try replacing it with something like

Dim drResources As Repeater = e.Item.FindControl("drResources")

(the syntax maybe a bit off - my VB is flaky :)

I'd also remove the try catch until the basics are working - it maybe that an error is being generated, but you're never seeing it.

edited Aug 05 '12 at 18:20

OPSL

131
1
11

answered Aug 05 '12 at 18:03

podiluska

50,950
7
98
104

both versions work fine I tested these extensivley, this issue surrounds the first column in the screen shot (P1 - 09:00 to 10:00) the bookings beneth it should be in the last column, its like all the other data is being ignored. I wonder if having three nested repeaters is the best way to go with this? – OPSL Aug 05 '12 at 18:10

ASP.Net Nested Repeater Logic Hell

2 Answers2