Solved

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

Posted on 2013-01-02
5
398 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

808 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