partygains.aspxReturn to the Documentation page
  

functionsshowGains
doFormatting
linkToOtherStatsMenu
addGains

‘Disable the viewstate at page level to improve the application's performance.’

<%@ Page Language="vb" EnableViewState="false" %>

‘Import the namespaces below to enable a connection to an Access 2000 database’

<%@ import Namespace="System.Data" %>

<%@ import Namespace="System.Data.ODBC" %>

<script runat="server">

‘The showGains function displays the gains that each party made in the election using a datagrid web control [gridMP].’

‘If the user doesn’t select an item from the listbox [listBox1] but just clicks the button with the caption ‘Show gains’, then nothing happens and the function ends.’

if listBox1.SelectedIndex >= 0 then

‘Declare a string variable [Access2000] to hold the SQL statement SELECT… FROM… WHERE… LIKE…. This statement selects all the fields from the fullresults table of the Access 2000 database [election2001.mdb] which satisfy the condition ‘result like Con gain’ and similar conditions for the other parties. The “%” symbol at the end of the condition stands for any number of characters that might appear after ‘Con gain’ e.g. ‘Con gain from Lab’ or ‘Con gain from Lib Dem’.

Dim Access2000 as string = "Select * from fullresults where result like '" & listBox1.selectedItem.Text & "%'"

‘The object variable of type ODBCConnection [MyConn] takes the strConn variable as a parameter and this is fetched from the web.config file.’

Dim MyConn as New ODBCConnection(ConfigurationSettings.AppSettings("strConn"))

‘The object variable of type DataSet [ds] is declared ready to hold the results before being bound to the datagrid control [gridMP].’

Dim ds as DataSet=New DataSet()

‘The object variable of type ODBCDataAdapter [Cmd] takes the string variable Access2000 and the connection variable MyConn as parameters.’

Dim Cmd as New ODBCDataAdapter(Access2000,MyConn)

‘The Fill method of the ODBCDataAdapter object puts the results of the query into the dataset [ds] as a table named ‘mps’.’

Cmd.Fill(ds,"mps")

‘The datagrid web control named gridMP has its Datasource property set to the mps table.’

gridMP.DataSource=ds

‘The data is bound to the control on the web page using the DataBind method.’

gridMP.DataBind()

‘Declare a string variable [Access2001] to hold the SQL statement SELECT...FROM...WHERE...LIKE...GROUP BY...ORDER BY. This statement selects the region and the result fields from the fullresults table of the Access database [election2001.mdb] which satisfy the condition ‘result like Con gain’. The “%” symbol at the end of the LIKE condition stands for any number of characters that might appear after ‘Con gain’ e.g. ‘Con gain from Lab’ or ‘Con gain from Lib Dem’. The count function is used to keep track of the total gains for each party within each region and the data is sorted on the first field i.e. region before it is bound to the datalist web control [datalist2] on the page.

Dim Access2001 as string = "Select region, count(result) as gains from fullresults where result like '" & listBox1.SelectedItem.Text & "%' group by region order by 1"

‘The object variable of type ODBCConnection [MyConn1] takes the strConn variable as a parameter and this is fetched from the web.config file.’

Dim MyConn1 as New ODBCConnection(ConfigurationSettings.AppSettings("strConn"))

‘Open the connection to the database.’

MyConn1.Open()

‘The object variable of type ODBCCommand [Cmd1] takes the string variable Access2001 and the connection variable MyConn1 as parameters.’

Dim Cmd1 as New ODBCCommand(Access2001,MyConn1)

‘Declare a variable [reader] of type ODBCDataReader.’

Dim reader as ODBCDataReader

‘Put all the requested data from the database into the reader using the ExecuteReader method.’

reader = Cmd1.ExecuteReader

‘The datalist web control [datalist2] has its Datasource property set to the reader object.’

dataList2.DataSource = reader

‘The data is bound to the control on the web page using the DataBind method.’

dataList2.DataBind()

‘If the number of items in the datagrid web control [gridMP] is equal to 0 then….’

if gridMP.items.count = 0 then

‘Set the text property of the label control [gainLabel] to ‘No gains for this party’. This heading will display above an empty datagrid.’

gainLabel.Text = "No gains for this party"

‘Set the text property of the label control [countLabel] to an empty string.’

countLabel.Text = ""

‘Otherwise if there are items i.e. gains to be displayed within the datagrid [gridMP]….’

else

‘Set the text property of the label control [countLabel] to display the number of gains/items within brackets.’

countLabel.Text = " (" & gridMP.items.count & ")"

‘Start a SELECT….CASE….END SELECT block which looks at what the user selected in the listbox control [listBox1].’

select case listBox1.selectedItem.Text

‘If the user selected ‘Lab gain’ then….’

Case "Lab gain"

‘Add certain attributes to the datalist web control [dataList2] that make the background colour red, the font colour white and put a black, grooved border around the content which is 2 pixels thick.’

dataList2.Attributes.Add("style","background-color:red;color:white;border: groove black 2px")

‘Similarly for all the other parties but changing the background colour to the appropriate party colour.’

Case "Con gain"

dataList2.Attributes.Add("style","background-color:blue;color:white;border: groove black 2px")

Case "Lib Dem gain"

dataList2.Attributes.Add("style","background-color:yellow;color:black;border: groove black 2px")

Case "SNP gain"

dataList2.Attributes.Add("style","background-color:green;color:white;border: groove black 2px")

Case "Plaid Cymru gain"

dataList2.Attributes.Add("style","background-color:green;color:white;border: groove black 2px")

Case "UUP gain"

dataList2.Attributes.Add("style","background-color:#800080;color:black;border: groove black 2px")

Case "DUP gain"

dataList2.Attributes.Add("style","background-color:#800000;color:white;border: groove black 2px")

Case "Sinn Fein gain"

dataList2.Attributes.Add("style","background-color:#00FF00;color:black;border: groove black 2px")

Case "SDLP gain"

dataList2.Attributes.Add("style","background-color:green;color:white;border: groove black 2px")

Case "Independent gain"

dataList2.Attributes.Add("style","background-color:white;color:black;border: groove black 2px")

‘End the SELECT…CASE…END SELECT block.’

end select

‘Terminate the IF… THEN… END IF BLOCK.’

end if

‘Store the text property of the label [gainLabel] in the Session variable named ‘subsetLabel’ – this will be used by the showMP function if the user selects the show details column in the datagrid [gridMP].’

Session("subsetLabel") = gainLabel.Text

‘Similarly store the dataset [ds] in the Session variable named ‘subsetDataSet’ – this will also be used by the showMP function (shown below).’

Session("subsetDataSet") = ds

‘Tidy up and close the connection variables MyConn & MyConn1 and the reader object [reader].’

MyConn.Close()

reader.Close()

MyConn1.Close()

end if

‘The doFormatting function is bound to the OnItemDataBound event of the Datagrid control [gridMP]. It changes the font-size of the constituency column if the content is greater than 30 characters. In addition it sets the text property of the label control [gainLabel] using a SELECT…CASE…END SELECT block.’

‘If the length of the constituency field is greater than 30 characters then….’

if e.Item.Cells(1).Text.Length > 30 then

‘….assign the cssClass property to 'lowerfont'. This style is defined within the onload.js file - see below and the documentation for this javascript file for the relevant coding.’

e.Item.Cells(1).cssClass = "lowerfont"

end if

‘Declare a string variable [gains] which is assigned the contents of the winningparty field – e.Item.Cells(3).Text.’

Dim gains as string = e.Item.Cells(3).Text

‘Start a SELECT….CASE….END SELECT block which looks at what’s in the string variable [gains].’

select case gains

‘If the variable contains the string ‘Labour’ then….’

Case "Labour"

‘Set the text property of the label control [gainLabel] to ‘Labour gains’. This web control acts as a heading to the data in the datagrid [gridMP].’

gainLabel.Text = "Labour gains"

‘Similarly for all the other parties.’

Case "Conservative"

gainLabel.Text = "Conservative gains"

Case "Liberal Democrats"

gainLabel.Text = "Liberal Democrat gains"

‘Since the text string ‘Lib Dem gain from Lab’ is quite long, reduce the font size of the result field in the datagrid [gridMP] by setting its cssClass property to 'lowerfont'.’

e.Item.Cells(4).cssClass = "lowerfont"

Case "SNP"

gainLabel.Text = "Gains by the SNP"

Case "Plaid Cymru"

gainLabel.Text = "Gains by Plaid Cymru"

Case "UUP"

gainLabel.Text = "Ulster Unionist gains"

Case "DUP"

gainLabel.Text = "Democratic Unionist gains"

Case "Sinn Fein"

gainLabel.Text = "Gains by Sinn Fein"

Case "SDLP"

gainLabel.Text = "Gains by the SDLP"

Case "Independent"

gainLabel.Text = "Gains by Independents"

‘End the SELECT…CASE…END SELECT block.’

end select

‘The function linkToOtherStatsMenu redirects the user back to the Other stats page. This is attached to a LinkButton web control at the top of the page.’

Response.Redirect("otherstats.aspx")

‘The function addGains is associated with the onItemDataBound event of the datalist [datalist2]. As the data is bound to the control it checks to see if the party has made either one gain or more than one gain in that particular region. If it has made just the 1 gain then the text " gain" is added to the listing, however if it has made more than 1 gain then the text " gains" is added.’

‘Declare a variable [regionalGainsLabel] of type Label which is assigned to the label on the page with the id="regionalGainsLabel". This label is retrieved using the FindControl method and the control then needs to be cast as a 'Label' using the CType function. The label [regionalGainsLabel] is described in further detail below but it basically takes the data from the reader object and displays the gains for the selected party within each region.’

dim regionalGainsLabel as Label = CType(e.Item.FindControl("regionalGainsLabel"), Label)

‘Starts an IF...THEN...ELSE...END IF block. If the rightmost character of the contents of the regionalGainsLabel web control is "1" then...’

if right(regionalGainsLabel.Text,1) = "1" then

‘Add the text " gain " to the listing for that region. Note the two spaces at the end - helps in the formatting of the Label when it is rendered on the page.’

regionalGainsLabel.Text = regionalGainsLabel.Text & " gain" & "&nbsp;&nbsp;"

‘Otherwise...’

else

‘Add the text " gains " to that particular listing.’

regionalGainsLabel.Text = regionalGainsLabel.Text & " gains" & "&nbsp;&nbsp;"

‘Ends the IF...THEN...ELSE...END IF block.’

end if

</script>

<html>

<head>

<title>Gains made by each party in the election</title>

<link href=".\otherfiles\election2001.css" type="text/css" rel="stylesheet">

<script type="text/Javascript" src=".\otherfiles\onload.js"></script><style> </style>

</head>

‘The init function runs when the page first loads on the user's machine. The function is contained within the onload.js file which is loaded automatically by the <script> tag above - see the onload.js file for further details.’

<body bgcolor="white" onload="init()">

<form id="form1" method="post" runat="server">

‘The table displays an image of a ballot box [ballotbox.jpg] in the top left-hand corner together with a label [Gains by each party] and the ASP linkbutton control mentioned above displays in the top right-hand corner of the page. This runs the linkToOtherStatsMenu function when clicked.’

<table width="100%">

<tr>

<td rowspan="3" width="85px" valign="top"><img src="./images/ballotbox.jpg" title="Cast your vote wisely!"></td>

<td colspan="2"><asp:Label id="Label1" runat="server"><u>Gains by each party</u></asp:Label></td>

<td align="right"><asp:LinkButton id="LinkToEdit" onclick="linkToOtherStatsMenu" runat="server" Text="Return to the Other stats menu"></asp:LinkButton></td>

</tr>

<tr><td colspan="3">&nbsp;</td></tr>

‘Underneath, in the third row of the table, is an ASP listbox [listBox1] displaying ‘Lab gain’, ‘Con gain’, etc. The user selects a party and then clicks the ASP button [btnLoadgains] to the right which runs the showGains function already discussed above.

A datalist [dataList2] containing a label [regionalGainsLabel] within the <ItemTemplate> section displays the party gains by region/region on the right-hand side of the screen. The text property of the label is set to extract the region and gains fields from the reader object using Container.DataItem as shown below.’

<tr>

<td>Select a party to see its gains:<p>

<asp:ListBox id="listBox1" runat="server" size="1">

<asp:ListItem>Lab gain</asp:ListItem>

<asp:ListItem>Con gain</asp:ListItem>

<asp:ListItem>Lib Dem gain</asp:ListItem>

<asp:ListItem>SNP gain</asp:ListItem>

<asp:ListItem>Plaid Cymru gain</asp:ListItem>

<asp:ListItem>UUP gain</asp:ListItem>

<asp:ListItem>DUP gain</asp:ListItem>

<asp:ListItem>Sinn Fein gain</asp:ListItem>

<asp:ListItem>SDLP gain</asp:ListItem>

<asp:ListItem>Independent gain</asp:ListItem>

</asp:ListBox>

</td>

<td width="30%"><asp:Button id="btnLoadgains" onclick="showGains" runat="server" Text="Show gains"></asp:Button></td>

<td align="right"><asp:DataList id=dataList2 runat="server" OnItemDataBound="addGains" cellpadding="2" repeatdirection="vertical"><ItemTemplate><asp:Label id="regionalGainsLabel" runat="server" Text='<%# " " & Container.DataItem("region") & ": " & Container.DataItem("gains") %>' /></ItemTemplate></asp:DataList></td>

</tr>

</table>

<p>

‘When the user makes a selection from the listbox [listBox1] and clicks the ‘Show gains’ button, the results are displayed in the following datagrid [gridMP] on the web page. Just above the datagrid are two ASP labels [gainLabel], see the doFormatting function above and countLabel, which shows how many gains the selected party made at the election, see the showGains function above. Both controls act as a heading for the contents of the datagrid.’

<center><H2><asp:Label runat="server" id="gainLabel"></asp:Label><asp:Label runat="server" id="countLabel"></asp:Label></H2><P>

<asp:Datagrid id="gridMP" cssClass="gridMP" Runat="server" AutoGenerateColumns="False" AllowPaging="false" BorderColor="Black" AlternatingItemStyle-Font-Name="Arial" AlternatingItemStyle-BackColor="#E7EFFF" Font-Name="Arial" BackColor="#E7EFFF" Headerstyle-Font-Size="12" Headerstyle-Font-Name="Arial" Headerstyle-ForeColor="blue" Headerstyle-BackColor="#BDCFE7" OnItemDataBound="doFormatting" cellspacing="0" cellpadding="4" GridLines="Both">

‘There are five bound columns, id, constituency, which is occasionally altered by the doFormatting function above, first, winningparty and result, which is also altered slightly by the same function. The final column is a hyperlink column [See details].’

<columns>

<asp:BoundColumn HeaderText="" DataField="id" ItemStyle-HorizontalAlign="right" />

<asp:BoundColumn HeaderText="Constituency" DataField="constituency" />

<asp:BoundColumn HeaderText="MP" DataField="first" HeaderStyle-HorizontalAlign="center" ItemStyle-HorizontalAlign="left" />

<asp:BoundColumn HeaderText="Winning party" DataField="winningparty" />

<asp:BoundColumn HeaderText="Result" DataField="result" />

‘When the user clicks ‘Show details’ a new window is opened using javascript - the window.open method. It opens the file showMP.aspx with all the details of the selected constituency displayed. The window will appear 10 pixels down from the top left-hand corner of the window. More on this in the showMP.aspx file.’

<asp:HyperlinkColumn HeaderText="See details" DataTextFormatString="Show details" DataTextField="id" HeaderStyle-HorizontalAlign="center" ItemStyle-HorizontalAlign="center" DataNavigateUrlField="id" DataNavigateUrlFormatString="javascript:var w=window.open('showMP.aspx?id={0}',null,'left=10,top=10,scrollbars');" />

</columns>

</asp:Datagrid>

</center>

</form>

</body>

</html>

***********************************************