swingstats.aspxReturn to the Documentation page
  

functionsshowMPs
doFormatting
linkToOtherStatsMenu

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

‘Import the namespaces below to enable a connection to a MySQL database’

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

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

<script runat="server">

‘The showMPs function connects to the election2005 database and extracts the top 20 seats, if there are 20, where the swing was highest to each party. It displays the results in the datagrid web control [gridMP].’

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

if listBox1.SelectedIndex >= 0 then

‘Declare a string variable [MySQL] to hold the SQL statement SELECT… FROM… WHERE… ORDER BY… LIMIT. This statement extracts all the records from the table named fullresults where the swingtoparty field contains the party selected in the listbox [listBox]. The mid(listBox1.selectedItem.Text,4) part of the statement only selects the party e.g. if the user selected ‘to Labour’ in the listbox only ‘Labour’ would appear in the above SQL statement. Finally the records are firstly sorted on the swing field from the highest swing down to the lowest and secondly on the id field, since there are many records with the same swing. Only the first 20 records are selected, if they exist, using the LIMIT 0, 20 syntax.’

Dim MySQL as string = "Select * from fullresults where swingtoparty = '" & mid(listBox1.selectedItem.Text,4) & "' order by swing desc, id limit 0, 20"

‘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 MySQL and the connection variable MyConn as parameters.’

Dim Cmd as New ODBCDataAdapter(MySQL,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 [swing] to hold the user’s selection from listBox1.’

Dim swing as string = listBox1.SelectedItem.Text

‘Start a SELECT… CASE… END SELECT BLOCK which looks at what’s in the variable [swing].’

select case swing

‘If the user selected ‘to Conservative’ from the listbox [listBox1] then….’

Case "to Conservative"

‘Set the text property of the Label control [swingLabel] to ‘Highest swings to the Conservatives’ and similarly for the other parties in the listbox control….’

swingLabel.Text = "Highest swings to the Conservatives"

Case "to Liberal Democrats"

swingLabel.Text = "Highest swings to the Liberal Democrats"

Case "to SNP"

swingLabel.Text = "Highest swings to the Scottish Nationalists"

Case "to UUP"

swingLabel.Text = "Highest swings to the Ulster Unionists"

Case "to DUP"

swingLabel.Text = "Highest swings to the Democratic Unionists"

Case "to SDLP"

swingLabel.Text = "Highest swings to the SDLP"

Case Else

swingLabel.Text = "Highest swings " & listBox1.SelectedItem.Text

end select

‘If the number of items in the datagrid [gridMP] is 0, then …..’

if gridMP.items.count = 0 then

‘Display the text message ‘No swings to this party’ in the label [swingLabel].’

swingLabel.Text = "No swings to this party"

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

end if

‘Store the text property of the label [swingLabel] 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") = swingLabel.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 variable MyConn.’

MyConn.Close

end if

‘The doFormatting function is bound to the OnItemDataBound event of the Datagrid control [gridMP]. It changes the font-size of certain columns if their length is greater than a certain number of characters. Note: the first column – the record number - can be accessed by e.Item.Cells(0)’

‘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

‘Start a SELECT….CASE….END SELECT block which looks at the Itemtype.’

select case (e.Item.ItemType)

‘If the ItemType is a typical row item or alternating row item in the datagrid [gridMP]….’

case ListItemType.Item, ListItemType.AlternatingItem

‘Add to the contents of the swing field the string ‘% ’ plus what the user selected in the listbox [listBox1] e.g. “13.9% to Conservative”. Repeat for each row in the datagrid.’

e.Item.Cells(2).Text = e.Item.Cells(2).Text & "% " & ListBox1.SelectedItem.Text

‘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")

</script>

<html>

<head>

<title>Highest swings for each party</title>

<link href=".\otherfiles\election2005.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 the election cross [cross.gif] in the top left-hand corner together with a label [Highest swings] 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/cross.gif" title="Make voting compulsory!"></td>

<td><asp:Label id="Label1" runat="server"><u>Highest swings</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="2">&nbsp;</td></tr>

 

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

 

<tr>

<td>Select a party to see the 20 highest swings:<p>

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

<asp:ListItem>to Labour</asp:ListItem>

<asp:ListItem>to Conservative</asp:ListItem>

<asp:ListItem>to Liberal Democrats</asp:ListItem>

<asp:ListItem>to SNP</asp:ListItem>

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

<asp:ListItem>to UUP</asp:ListItem>

<asp:ListItem>to DUP</asp:ListItem>

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

<asp:ListItem>to SDLP</asp:ListItem>

<asp:ListItem>to Independents</asp:ListItem>

</asp:ListBox>

</td>

<td><asp:Button id="btnLoadMPs" onclick="showMPs" runat="server" Text="Show highest swings"></asp:Button></td>

</tr>

</table>

<p>

‘When the user makes a selection from the listbox [listBox1] and clicks the ‘Show highest swings’ button, the results are displayed in the following datagrid [gridMP] on the web page. Just above the datagrid is an ASP label [swingLabel] which is formatted as <H2> on the page and this control acts as a heading for the contents of the datagrid.’

<center><H2><asp:Label runat="server" id="swingLabel"></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 four bound columns, id, constituency, swing, which is altered slightly by the doFormatting function above, and winningparty. The final column is a template column [See details].’

<columns>

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

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

<asp:BoundColumn HeaderText="Swing(%)" DataField="swing" HeaderStyle-HorizontalAlign="right" ItemStyle-HorizontalAlign="right" />

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

‘The Template Column contains a Hyperlink web control that opens a new window using javascript - the window.open method – if the user clicks on ‘Show details’. This method is different from that used in the other .aspx files in the Other stats section because two fields are needed in the Request.Querystring, which is used by the file showMP.aspx to display the details of the selected constituency. [Note: putting your mouse on top of the ‘Show details’ link will expose the full syntax in the status bar at the bottom of your screen.]’

‘The swing field is not enough to isolate the required constituency since many records have identical swings – so this field has to be used in conjunction with the id field – more on this in the showMP.aspx file. The NavigateUrl attribute takes the javascript syntax as shown below.’

<asp:TemplateColumn HeaderText="See details" HeaderStyle-HorizontalAlign="center" ItemStyle-HorizontalAlign="center">

<ItemTemplate>

<asp:HyperLink runat="server" ToolTip="View the details for this constituency" Text="See details" NavigateUrl='<%# "Javascript:var w=window.open(""showMP.aspx?swing=" & Container.DataItem("swing") & "&id=" & Container.DataItem("id") & """,null,""left=10,top=10,scrollbars"")" %>' />

</ItemTemplate>

</asp:TemplateColumn>

</columns>

</asp:Datagrid>

</center>

</form>

</body>

</html>

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