nationalstats.aspxReturn to the Documentation page


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

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

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

<script runat="server">

‘Declare string variables as public variables – these will hold the totals for each party.’

public labourMPs as string

public conservativeMPs as string

public libdemMPs as string

public snpMPs as string

public pcMPs as string

public dupMPs as string

public uupMPs as string

public sdlpMPs as string

public sinnfeinMPs as string

public indMPs as string

public respectMPs as string

‘When the page first loads on the client’s machine the Page_Load function runs to compute the losses for each party using the result field from the election2005 database.’

‘Declare a string variable [OtherSql] to hold the SQL statement SELECT… FROM. It is only concerned with the data in one field - result.’

dim OtherSql as string = "Select result from fullresults"

‘Declare a variable [OtherConn] of type ODBCConnection that uses the connection string which is stored in the web.config file.’

dim OtherConn as New ODBCConnection(ConfigurationSettings.AppSettings("strConn"))

‘Declare a variable [Otherds] to hold the results of the above query.’

dim Otherds as DataSet=New DataSet()

‘Declare a variable of type ODBCDataAdapter which takes the parameters OtherSql and OtherConn. This object pulls the requested data from the election2005 database and puts it into the dataset [Otherds].’

Dim OtherCmd as New ODBCDataAdapter(OtherSQL,OtherConn)

‘Use the Fill method of the ODBCDataAdapter object to populate the dataset witrh a table named ‘losses’.’


‘Declare a variable [dTable] of type DataTable which is filled with data from the dataset, namely the table ‘losses’.’

dim dTable as DataTable = Otherds.Tables("losses")

‘Declare a variable [dv] of type DataView using the dTable as a parameter.’

dim dv as new DataView(dTable)

‘The RowFilter property of the DataView is set to show only those records where Labour lost a seat to another party – the “%” character can stand for any sequence of letters and/or numbers. This filter will pick up ‘Con gain from Lab’ as well as ‘Lib Dem gain from Lab’, etc, etc.’

dv.RowFilter = "result like '%from Lab'"

‘Obtain the number of Labour losses by using the count property of the DataView and store the resulting number [46] in the session variable ‘lablosses’ – this will be retrieved by the datagrid [gridMP] later on using the OnItemDataBound event.’

Session("lablosses") = dv.count

‘Similarly for the losses of the Conservatives...and other parties.’

dv.RowFilter = "result like '%from Con'"

Session("conlosses") = dv.count

dv.RowFilter = "result like '%from Lib Dem'"

Session("libdemlosses") = dv.count

dv.RowFilter = "result like '%from SNP'"

Session("snplosses") = dv.count

dv.RowFilter = "result like '%from Plaid Cymru'"

Session("pclosses") = dv.count

dv.RowFilter = "result like '%from Independent'"

Session("indlosses") = dv.count

dv.RowFilter = "result like '%from Respect'"

Session("respectlosses") = dv.count

dv.RowFilter = "result like '%from UUP'"

Session("uuplosses") = dv.count

dv.RowFilter = "result like '%from DUP'"

Session("duplosses") = dv.count

dv.RowFilter = "result like '%from Sinn Fein'"

Session("sinnfeinlosses") = dv.count

dv.RowFilter = "result like '%from SDLP'"

Session("sdlplosses") = dv.count

‘Tidy up and close the connection object OtherConn.’



‘Run the showMPs function which will display the national situation on the client’s screen.’


‘The showMPs function is called automatically at the end of the Page_Load function. It displays the parties, the number of seats won at the election, their gains and, finally, their losses over the previous election [2001]. It also shows Labour’s overall majority – 65.’

‘Declare a string variable [MySQL] to hold the SQL statement SELECT… FROM… GROUP BY… ORDER BY…. It includes a COUNT(CASE… END) expression which will count the gains – ‘case when result<>”No change” then 1’. This expression is referred to as ‘gains’ for the purposes of the bound column in the datagrid [gridMP] - see below.’

Dim MySQL as string = "Select winningparty, count(winningparty), count(case when result<>'No change' then '1' end) as gains from fullresults group by winningparty order by 2 desc, winningparty asc"

‘Declare a variable [MyConn] of type ODBCConnection which references the connection string from the web.config file using the ‘ConfigurationSettings.AppSettings("strConn")’. strConn is the key and its value is the connection string to the database – it’s in the form: "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=keith001_election2005;USER=;PASSWORD=; OPTION=3;". ’

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

‘Open the connection to the database.’


‘Declare a new ODBCCommand object [Cmd] with the parameters MySQL and MyConn. This object pulls the requested data from the election2005 database and puts it into the ODBCDataReader object [reader], which is declared below.’

Dim Cmd as New ODBCCommand(MySQL,MyConn)

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

Dim reader as ODBCDataReader

‘Use the ExecuteReader method to pull the requested data out from the election database and into the reader object.’

reader = Cmd.ExecuteReader

‘Set the datasource property of the Datagrid [gridMP] to the data in the ODBCDataReader.’


‘Bind the data to the web control on the page.’


‘Display the text expression ‘Labour’s overall majoity etc.,etc.’ in the ASP label  [majorityLabel]. This will compute the overall majority for Labour using the variables filled from the doOverallMajority function below.’

majorityLabel.Text = "Labour's overall<br>majority is " & labourMPs - conservativeMPs - libdemMPs - snpMPs - pcMPs - dupMPs - uupMPs - sdlpMPs - sinnfeinMPs - indMPs - respectMPs

‘Tidy up and close the MyConn connection and the reader object.’



‘The linkToOtherStatsMenu function runs when the user clicks the ASP linkbutton in the top right-hand corner of the screen. It redirects the user back to the Other stats menu.’


‘The doOverallMajority function is attached to the OnItemDataBound event of the Datagrid [gridMP] and it fills variables with the total number of MPs for each party. These are then used above in the showMPs function. In addition it puts each party’s losses into the fifth column of the Datagrid [gridMP] – these losses were calculated when the Page_Load function ran – see above.’

‘Declare a variable [img] of type Image which is assigned to the image control on the page with the id="partyLogo". This control is retrieved using the FindControl method. The image [img] will have its ImageUrl or Visible property set depending on the party displayed within that row of the datagrid.’

dim img as Image = e.Item.FindControl("partyLogo")

‘Declare a string variable [winningparty] which refers to the contents of the second cell in the datagrid.’

Dim winningparty as string = e.Item.Cells(1).Text

‘ Start a SELECT…CASE…END SELECT block.’

select case winningparty

‘If the contents of the second cell [e.Item.Cells(1).Text] are ‘Labour’ then….’

Case "Labour"

‘Put the number of labour losses [46] into the fifth column of this row – take this number out of the session variable named “lablosses”.’

e.Item.Cells(4).Text = Session("lablosses")

‘Assign the contents of the third cell [355] to the variable labourMPs.’

labourMPs = e.Item.Cells(2).Text

‘Set the ImageUrl property of the img object to 'lab.gif' - Labour's red rose logo.’

img.ImageUrl = "./images/lab.gif"

‘Similarly for all the other parties that won seats in the election….’

Case "Conservative"

e.Item.Cells(4).Text = Session("conlosses")

conservativeMPs = e.Item.Cells(2).Text

img.ImageUrl = "./images/cons.gif"

Case "Liberal Democrats"

e.Item.Cells(4).Text = Session("libdemlosses")

libdemMPs = e.Item.Cells(2).Text

img.ImageUrl = "./images/libdem.gif"

Case "SNP"

e.Item.Cells(4).Text = Session("snplosses")

snpMPs = e.Item.Cells(2).Text

img.ImageUrl = "./images/snp.gif"

Case "Plaid Cymru"

e.Item.Cells(4).Text = Session("pclosses")

pcMPs = e.Item.Cells(2).Text

img.ImageUrl = "./images/pc.gif"

Case "DUP"

e.Item.Cells(4).Text = Session("duplosses")

dupMPs = e.Item.Cells(2).Text

img.ImageUrl = "./images/dup.gif"

Case "UUP"

e.Item.Cells(4).Text = Session("uuplosses")

uupMPs = e.Item.Cells(2).Text

img.ImageUrl = "./images/uup.gif"

Case "SDLP"

e.Item.Cells(4).Text = Session("sdlplosses")

sdlpMPs = e.Item.Cells(2).Text

img.ImageUrl = "./images/sdlp.gif"

Case "Sinn Fein"

e.Item.Cells(4).Text = Session("sinnfeinlosses")

sinnfeinMPs = e.Item.Cells(2).Text

img.ImageUrl = "./images/sinnfein.gif"

Case "Independent"

e.Item.Cells(4).Text = Session("indlosses")

indMPs = e.Item.Cells(2).Text

img.visible = "false"

Case "Respect"

e.Item.Cells(4).Text = Session("respectlosses")

respectMPs = e.Item.Cells(2).Text

img.ImageUrl = "./images/respect.gif"

Case "Speaker"

e.Item.Cells(3).Text = "-"

e.Item.Cells(4).Text = "-"

img.visible = "false"

end select




<title>The result at a glance</title>

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


<body bgcolor="white">

<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 [The result at a glance…] 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%">


<td rowspan="2" width="85px" valign="top"><img src="./images/cross.gif" title="Make voting compulsory!"></td>

<td><asp:Label id="Label1" runat="server"><u>The result at a glance</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><td colspan="2">&nbsp;</td></tr>


‘To the left of the Datagrid [gridMP] is the ASP label [majorityLabel] which displays Labour’s overall majority [65]. This label’s text property is set in the showMPs function above.’

<asp:Label runat="server" id="majorityLabel" style="position:relative;top:200;left:50;text-decoration:underline;font-size:18px"></asp:Label>

<center><asp:Label runat="server" id="partyLabel"><H2>The state of the parties</H2></asp:Label><P>

‘The Datagrid web control [gridMP] displays the parties that won seats in descending order – highest number of seats down to the lowest. Labour top the table with 355 seats and the UUP are at the bottom with just the one MP. If there is more than one party with the same number of seats then alphabetical order is used to sort them. The control also shows gains and losses for each party.’

<asp:Datagrid id="gridMP" Font-Size="20px" OnItemDataBound = "doOverallMajority" Runat="server" Headerstyle-HorizontalAlign="center" AutoGenerateColumns="false" BorderColor="Black" AlternatingItemStyle-Font-Name="Times New Roman" AlternatingItemStyle-BackColor="#E7EFFF" Font-Name="Times New Roman" BackColor="#E7EFFF" Headerstyle-Font-Size="12" Headerstyle-Font-Name="Arial" Headerstyle-ForeColor="blue" Headerstyle-BackColor="#BDCFE7" cellspacing="0" width="400" cellpadding="4" GridLines="Both">

‘The first column in the datagrid is a Template column which will display the party logo via the onItemDataBound event - see the doOverallMajority function above. The rest of the columns are bound columns, winningparty, count(winningparty), gains and, finally, a blank column where the DataField property is set to “”. The blank column is also filled when the OnItemDataBound event is run and it is filled with each party’s losses. These are calculated within the Page_Load function which runs automatically when the page is first requested by the client’s computer.’


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


<asp:Image runat="server" id="partyLogo" />



<asp:BoundColumn HeaderText="Party" DataField="winningparty" />

<asp:BoundColumn HeaderText="&nbsp;&nbsp;MPs&nbsp;&nbsp;" ItemStyle-HorizontalAlign="right" DataField="Count(winningparty)" />

<asp:BoundColumn HeaderText="&nbsp;Gains&nbsp;" HeaderStyle-HorizontalAlign="right" ItemStyle-HorizontalAlign="right" DataField="gains" />

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