asked on

Need help binding relation of Gridview nesting within Datalist in VB.Net

I have originally coded a nested Datalist within Datalist, and for several reasons (code simplification, requirements for translation, flexibility etc..) I have decided to change the nested item to a Gridview. So I have a GridView within a Datalist.

The goal of my program is to produce a events calendar, where each month is a table. The goal of the OUTER relation (Datalist) is simply to produce a set of months (ex: November 2012, December 2012, January 2013... etc) and the INNER relation (Gridview) represents a table / list of dates and event information within that month.

I have the queries working that generate the OUTER and INNER data - but I need help binding /nesting them together. Originally, for my Datalist within Datalist code, I used the following to bind the two:

        ' Fill dataset with month names
        MySQL1 = "SELECT DISTINCT MONTH(g.gamedatetime) AS Months, YEAR(g.gamedatetime) AS Years from games g, leaguesetup ls where g.leaguenum=" & Session("leaguenum") & " and g.leaguenum = ls.leaguenum and g.gamedatetime > ls.startdate and g.gametype = 'Regular' Order By Years asc, Months asc"
        Dim objCmd1 As New SqlDataAdapter(MySQL1, MyConn)
        objCmd1.Fill(ds, "monthnames")
     
        ' Fill datagrid with calendar events
        MySQL2 = "SELECT g.gamenum as Gamenum, month(g.gamedatetime) as Eventmonth, 'Backcolor' = CASE WHEN g.Gametype = 'Practice' THEN 'gray' ELSE 'white' END, Day(G.Gamedatetime) as EventDay, 'HyperEnabled' = CASE WHEN g.Gamestatus = 'Played' THEN 'true' ELSE 'false' END, g.gamedatetime as Eventtime, g.Gametype, 'Score' = CASE WHEN g.gametype = 'Practice' THEN 'At ' + r.shortname WHEN (g.gametype <> 'Practice' AND g.Gamestatus = 'Played') THEN L1.Groupname + ' : ' + convert(varchar,g.Homegoals) + '<br/>' + L2.Groupname + ' : ' + convert(varchar,g.Awaygoals) WHEN (g.gametype <> 'Practice' AND g.Gamestatus <> 'Played') THEN L1.Groupname + ' vs. ' + L2.Groupname + '<br/>@ ' +  r.shortname END, 'Gamedetails' = CASE WHEN (g.Gamestatus <> 'Played' AND g.needsnack = 'yes') THEN left(c.firstname,1) + '. ' + c.lastname ELSE '-' END from games g, leagues l1, leagues l2, locations r, leaguesetup ls, contacts c where l1.groupnum = g.hometeamnum and c.usernum = g.snacknum and l2.groupnum = g.awayteamnum and g.gamelocnum = r.locnum and g.leaguenum = " & Session("leaguenum") & " and g.leaguenum = ls.leaguenum and g.gamedatetime > ls.startdate " & Showtype & " and g.Gamestatus <> 'disabled' " & Showtime & " order by g.gamedatetime asc"
        Dim objCmd2 As New SqlDataAdapter(MySQL2, MyConn)
        objCmd2.Fill(ds, "events")
        
        ds.Relations.Add("myrelation1", ds.Tables("monthnames").Columns("Months"), ds.Tables("events").Columns("Eventmonth"), False)

        Gamemonths.DataSource = ds.Tables("monthnames").DefaultView
        Gamemonths.DataBind()

and I also had the following in the INNER datalist

     DataSource='<%# Container.DataItem.Row.GetChildRows("myrelation1")%>'

Open in new window

But I have no clue how to change this so it will work with a Gridview, and so I need help in setting up the new relationship.

If it is easier to do this with a Gridview within a Gridview, then I am open to this as well.

Thanks, Peter

P.S. Please find attached all my code FYI

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" %>

<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Globalization" %>

<script runat="server">
    Private Shared Ttext(26) As String
    Private Shared Header1, Header2, Header3, Header4, Header5, LabelA, LabelB, LabelC, LabelD, LabelE as String 
    Dim cmd As New SqlCommand()
    Dim TransReader As SqlDataReader
    Dim MyConn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
    Dim MyTools As New Krylium.KryliumTools
    Dim MySecurity As New Krylium.KryliumSecurity
    
    Dim Whichstep, Gamedates, Showtype, Showtime As String
    Dim ds As New DataSet()
    Dim MySQL, MySQL1, MySQL2, MySQL3 As String
    Dim MyViewloc As DataView 

    Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs)
 
        ' Check if logged in
        If Session("Leaguenum") < 1 Then
            Response.Redirect("~/index.aspx")
        End If         
        
        If Not Page.IsPostBack Then

            ' Load in appropriate language files
            Ttext = MyTools.LoadNewTranslation("pickschedule", MyTools.GetLanguage)
            
            Pagetitle.Text = Ttext(1)
            Helporerror.Text = Ttext(2) 
            dropdownLabel1.Text = Ttext(10)
            dropdownLabel2.Text = Ttext(11)

            ' Load the time dropdown
            Timetype.Items.Add(Ttext(13))
            Timetype.Items.Add(Ttext(14))
            Timetype.Items.Add(Ttext(15))
            Timetype.SelectedIndex = 0
            
            ' Load the type dropdown
            Gametype.Items.Add(Ttext(3))
            Gametype.Items.Add(Ttext(4))
            Gametype.Items.Add(Ttext(5))
            Gametype.Items.Add(Ttext(6))
            Gametype.Items.Add(Ttext(7))
            Gametype.SelectedIndex = 0

            ' Create the datalist
            BindGrid(0,0)
        End If

    End Sub

    Sub ChangeDisplay(sender As Object, e As EventArgs)
        BindGrid(Timetype.SelectedIndex,Gametype.SelectedIndex)
    End Sub
        
    Private Sub BindGrid(Timenum as Integer, Typenum as Integer)

        ' Convert inputs into SQL string components
        Select Case Timenum
            Case 0
                Showtime = ""
            Case 1
                Showtime = " and g.gamedatetime > Getdate()"
            Case 2
                Showtime = " and g.ganestatus = 'Played'"
        End Select

        Select Case Typenum
            Case 0
                Showtype = ""
            Case 1
                Showtype = " and g.gametype = 'Regular'"
            Case 2
                Showtype = " and g.gametype = 'Practice'"
            Case 3
                Showtype = " and g.gametype = 'Exhibition'"
            Case 4
                Showtype = " and g.gametype = 'Playoff'"
        End Select
        
        ' Prepare the datagrid
        ScheduleGrid.Columns(0).HeaderText = Ttext(16)
        ScheduleGrid.Columns(1).HeaderText = Ttext(17)
        ScheduleGrid.Columns(2).HeaderText = Ttext(18)
        ScheduleGrid.Columns(3).HeaderText = Ttext(19)
        ScheduleGrid.Columns(4).HeaderText = Ttext(20)
        
        ' Fill dataset with month names
        ' MySQL1 = "SELECT g.gamedatetime as MonthName, Case When Month(g.gamedatetime)<9 Then Month(g.gamedatetime)+12 Else Month(g.gamedatetime) End As OrderKey FROM games g, leaguesetup ls where g.leaguenum=" & Session("Leaguenum") & " and g.leaguenum = ls.leaguenum and g.gamedatetime > ls.startdate and g.gametype = 'Regular' GROUP BY datename(month,g.gamedatetime),Case When Month(g.gamedatetime)<9 Then Month(g.gamedatetime)+12 Else Month(g.gamedatetime) END ORDER BY Case When Month(g.gamedatetime)<9 Then Month(g.gamedatetime)+12 Else Month(g.gamedatetime) END"
        MySQL1 = "SELECT DISTINCT MONTH(g.gamedatetime) AS Months, YEAR(g.gamedatetime) AS Years from games g, leaguesetup ls where g.leaguenum=" & Session("leaguenum") & " and g.leaguenum = ls.leaguenum and g.gamedatetime > ls.startdate and g.gametype = 'Regular' Order By Years asc, Months asc"
        Dim objCmd1 As New SqlDataAdapter(MySQL1, MyConn)
        objCmd1.Fill(ds, "monthnames")
     
        ' Fill datagrid with month events - for calendar or boxscores
        MySQL2 = "SELECT g.gamenum as Gamenum, month(g.gamedatetime) as Eventmonth, 'Backcolor' = CASE WHEN g.Gametype = 'Practice' THEN 'gray' ELSE 'white' END, Day(G.Gamedatetime) as EventDay, 'HyperEnabled' = CASE WHEN g.Gamestatus = 'Played' THEN 'true' ELSE 'false' END, g.gamedatetime as Eventtime, g.Gametype, 'Score' = CASE WHEN g.gametype = 'Practice' THEN 'At ' + r.shortname WHEN (g.gametype <> 'Practice' AND g.Gamestatus = 'Played') THEN L1.Groupname + ' : ' + convert(varchar,g.Homegoals) + '<br/>' + L2.Groupname + ' : ' + convert(varchar,g.Awaygoals) WHEN (g.gametype <> 'Practice' AND g.Gamestatus <> 'Played') THEN L1.Groupname + ' vs. ' + L2.Groupname + '<br/>@ ' +  r.shortname END, 'Gamedetails' = CASE WHEN (g.Gamestatus <> 'Played' AND g.needsnack = 'yes') THEN left(c.firstname,1) + '. ' + c.lastname ELSE '-' END from games g, leagues l1, leagues l2, locations r, leaguesetup ls, contacts c where l1.groupnum = g.hometeamnum and c.usernum = g.snacknum and l2.groupnum = g.awayteamnum and g.gamelocnum = r.locnum and g.leaguenum = " & Session("leaguenum") & " and g.leaguenum = ls.leaguenum and g.gamedatetime > ls.startdate " & Showtype & " and g.Gamestatus <> 'disabled' " & Showtime & " order by g.gamedatetime asc"
        Dim objCmd2 As New SqlDataAdapter(MySQL2, MyConn)
        objCmd2.Fill(ds, "events")
        
        ds.Relations.Add("myrelation1", ds.Tables("monthnames").Columns("Months"), ds.Tables("events").Columns("Eventmonth"), False)

        Gamemonths.DataSource = ds.Tables("monthnames").DefaultView
        Gamemonths.DataBind()
    End Sub 

    Function Monthonly(monthnumber as Integer) as String
        
        Dim French As New System.Globalization.CultureInfo("fr-FR")
        Dim English As New System.Globalization.CultureInfo("en-US")
        
        Dim dt As New DateTime(DateTime.Today.Year, monthnumber, 1)

        If Session("lang") = "english" Then
            Monthonly = dt.ToString("MMMM", English)
        else
            Monthonly = dt.ToString("MMMM", French)
        End If

    End Function

    Function Translatetype(Gametype as String) as String
        
        Select Case Gametype

            Case "Regular"
                If Session("lang") = "english" then
                    Gametype = "Game"
                Else
                    Gametype = "Match"
                End If
            Case "Practice"
                If Session("lang") = "english" then
                    Gametype = "Practice"
                Else
                    Gametype = "Pratique"
                End If
            Case "Exhibition"
                If Session("lang") = "english" then
                    Gametype = "Exhibition"
                Else
                    Gametype = "Exhibition"
                End If
            Case "Playoff"
                If Session("lang") = "english" then
                    Gametype = "Playoff Game"
                Else
                    Gametype = "Match éliminatoire"
                End If
        
        End Select

    End Function

    Sub ColorRow(object sender, GridViewRowEventArgs e)
    
        If (DataBinder.Eval(e.Row.DataItem, "Gametype") = "Practice" or DataBinder.Eval(e.Row.DataItem, "Gametype") = "Pratique") then
            e.Row.BackColor = Color.FromName("#C0C0C0")
        End If

    End Sub
    
</script>


<asp:Content ID="Content1" ContentPlaceHolderID="Mycontent" runat="Server">
    <table id="AutoNumber1" style="border-collapse: collapse; margin-left: auto; margin-right: auto;
        border-color: #111111" cellspacing="0" cellpadding="0" width="700" border="1">
        <tr>
            <td class="WizardTitle">
                <asp:Label ID="Pagetitle" runat="server"></asp:Label>
            </td>
        </tr>
        <tr>
            <td class="WizardInstructions">
                <p style="margin: 6px"><asp:Label ID="Helporerror" runat="server" /></p>    
                <p style="margin-bottom: 6px"><asp:Label ID="dropdownLabel2" style="padding-right: 10px; font-weight:bold" runat="server" /><asp:DropDownList ID="Timetype" OnSelectedIndexChanged="ChangeDisplay" AutoPostBack="true" Width="160" runat="server" /><asp:Label ID="dropdownLabel1" style="padding-left: 50px; padding-right: 10px; font-weight:bold" runat="server" /><asp:DropDownList ID="Gametype" OnSelectedIndexChanged="ChangeDisplay" AutoPostBack="true" Width="160" runat="server" /></p>
            </td>
        </tr>
        <tr>
            <td class="WizardContent" style="width:700px">
                <div align="center" style="margin-bottom: 12px">
                <asp:DataList ID="Gamemonths" ItemStyle-VerticalAlign="Top" ItemStyle-HorizontalAlign="Center"
                    runat="server" CellPadding="3" Font-Names="Verdana" Font-Size="8pt">                   
                    <ItemTemplate>
                            <asp:GridView ID="ScheduleGrid" runat="server" Width="700" CellPadding="3" DataSource='<%# Container.DataItem.Row.GetChildRows("myrelation1")%>' 
                            DataKeyNames="gamenum" RowStyle-Font-Size="10pt" HeaderStyle-Font-Size="10pt"
                            Caption='<table width="100%" class="CaptionStyle"><tr><td><%# Monthonly(Container.DataItem("Months")) & " " & Container.DataItem("Years") %></td></tr></table>'
                            HeaderStyle-ForeColor="black" HeaderStyle-BackColor="Gray" AutoGenerateColumns="false">
                            <Columns>
                                <asp:TemplateField ItemStyle-Width="60"  ItemStyle-HorizontalAlign="Center">
                                    <ItemTemplate>
                                        <asp:HyperLink ID="HyperLink1" runat="server" Enabled='<%# Container.DataItem("HyperEnabled") %>' NavigateUrl='<%#"../Games/gamereview1.aspx?pstep=11&game=" & Container.DataItem("Gamenum") %>'><%# Container.DataItem("EventDay") %></asp:HyperLink>
                                    </ItemTemplate>
                                </asp:TemplateField>
                                <asp:TemplateField ItemStyle-Width="60"  ItemStyle-HorizontalAlign="Center">
                                    <ItemTemplate>
                                        <asp:Label ID="Label11" runat="server" Font-Size="9pt" Text='<%# MyTools.Makedate(Container.DataItem("Eventtime"),"timeonly") %>' />
                                    </ItemTemplate>
                                </asp:TemplateField>
                                <asp:TemplateField ItemStyle-Width="60"  ItemStyle-HorizontalAlign="Center">
                                    <ItemTemplate>
                                        <asp:Label ID="Label12" runat="server" Font-Size="9pt" Text='<%# Translatetype(Container.DataItem("Gametype")) %>' />
                                    </ItemTemplate>
                                </asp:TemplateField>
                                <asp:BoundField DataField="Score" ItemStyle-Width="100" ItemStyle-HorizontalAlign="Center" />
                                <asp:BoundField DataField="Gamedetails" ItemStyle-Width="150" ItemStyle-HorizontalAlign="Center" />
                            </Columns>
                        </asp:GridView
                    </ItemTemplate>
                </asp:DataList>
                </div>
            </td>
        </tr>
    </table>
</asp:Content>

Open in new window

Nasir Razzaq

You can use the same code for binding the gridview. You just need to define the gridview with its columns correctly and then set its datasource in same way you are doing now.

pbissegger

ASKER

OK,

I tried running the code above, but it does not seem to be able to see the GridView within the DataList, because I get the following error:

Server Error in '/' Application.

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. 

Compiler Error Message: BC30451: Name 'ScheduleGrid' is not declared.

Source Error:


Line 86:         
Line 87:         ' Prepare the datagrid
Line 88:         ScheduleGrid.Columns(0).HeaderText = Ttext(16)
Line 89:         ScheduleGrid.Columns(1).HeaderText = Ttext(17)
Line 90:         ScheduleGrid.Columns(2).HeaderText = Ttext(18)

Source File: E:\web\hockeymanag\htdocs\Schedule\pickschedule-2.aspx    Line: 88

Open in new window

Is there anything I should be doing to get the GridView recognized within the DataList ?

Thanks, Pete

Nasir Razzaq

You have to declare the grid (setup its column etc) in aspx. The reason that you are not seeing it in code behind is that there is not one grid but one grid per item of the datalist.

pbissegger

ASKER

OK, I have found a good article to create the Gridview in code behind:

http://deepinthecode.com/2012/05/29/programmatically-creating-an-asp-net-gridview/

But I have 2 questions:

1. When do I create / construct this Gridview ? Do I just put it my BindGrid subroutine above, or in the Page_load subroutine (or in the If.not.page.ispostback section) ?

2. In HTML, am taking the entire Gridview out of the Datalist. What do I replace it with in the Itemtemplate in the Datalist ?

Thanks for your help,

Peter

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial