Solved

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

Posted on 2013-01-02
5
395 Views
Last Modified: 2013-02-15
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

0
Comment
Question by:pbissegger
  • 3
  • 2
5 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38738280
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.
0
 

Author Comment

by:pbissegger
ID: 38738679
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
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38740217
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.
0
 

Author Comment

by:pbissegger
ID: 38746932
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
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 38746941
1) It would need to be in ItemDataBound event of the datalist
2) Nothing
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now