showregionalStats.aspxReturn to the Documentation page
  

functionsPage_Load
doLosses

<%@ Page Language="vb" %>

‘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 Page_Load function runs when the page first loads on the user’s machine and it works out the gains and the losses for each party in the selected region. This file [showregionalStats.aspx] can only be called from an election map – Election maps, select a region and then click the ‘Stats’ button.’

‘If the form hasn’t already been submitted then run the code below.’

if Not Page.IsPostBack then

‘Declare a string variable [OtherSql] to hold the SQL statement SELECT… FROM… WHERE…. Only one field is selected – the result field – and only those records are selected  where the region field is equal to the contents of the Session variable [subsetLabel]. This Session variable is set in the calling file i.e. by regionalmap.aspx.’

dim OtherSql as string = "Select result from fullresults where region='" & Session("subsetLabel") & "'"

‘Declare a variable [OtherConn] 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={Microsoft Access Driver (*.mdb)};DBQ=c:Uk elections/election2001/database/election2001.mdb;Uid=;Pwd=;". ’

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

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

dim Otherds as DataSet=New DataSet()

‘The object variable of type ODBCDataAdapter [OtherCmd] takes the string variable OtherSQL and the connection variable OtherConn as parameters.’

dim OtherCmd as New ODBCDataAdapter(OtherSQL,OtherConn)

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

OtherCmd.Fill(Otherds,"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 the result field ends with the text ‘from Lab’ i.e. this shows the seats that the Labour party lost at the election.’

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

‘Accordingly count the number of items in this DataView and assign it to the Session variable ‘lablosses’. This will appear in the DataGrid web control [gridRegion] via the onItemDataBound event using the doLosses function, which is below.’

Session("lablosses") = dv.count

‘Similarly for all the other parties – assign the count to a suitable Session variable which will be used later on in doLosses.’

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 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

‘Close the OtherConn connection object.’

OtherConn.Close

‘Declare a string variable [addToAccess2000] which is added to the string variable [Access2000] below using the concatenation operator – ‘&’. All this variable does is add a condition based on the region field plus a GROUP BY…. ORDER BY clause. Splitting the statement into two string variables makes it easier to write the required SQL statement, which is quite complicated. The ORDER BY… syntax first sorts the records on the count(winningparty) field in descending order, and then in alphabetical order on the winningparty field.’

dim addToAccess2000 as string = "region='" & Session("subsetLabel") & "' group by winningparty order by 2 desc, winningparty"

‘Declare a string variable [Access2000] which takes the above string variable [addToAccess2000] at the end. This variable contains an SQL statement – SELECT… FROM…WHERE….. which computes the number of seats won by each party within each region and the gains, if any, for each party. The region is contained within the Session variable named ‘subsetLabel’. ‘count(winningparty) as partytotals’ adds up the number of seats for each party in the region and the second count statement, which uses an IIf block, will calculate the number of gains for that party in the region selected. This second ‘count(iif…)’ is referred to ‘as gains’ for the purposes of the datagrid web control [gridRegion], which can be seen below.’

dim Access2000 as string = "select winningparty, count(winningparty) as partytotals, count(iif(result<>'No change', '1')) as gains from fullresults where " & addToAccess2000

‘Declare a variable [MyConn] of type ODBCConnection which references the connection string from the web.config file using the ‘ConfigurationSettings.AppSettings("strConn")’.’

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

‘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 object variable of type DataSet [ds] is declared ready to hold the results before being bound to the datagrid control [gridRegion].’

dim ds as DataSet=New DataSet()

‘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 gridRegion has its Datasource property set to the mps table.’

gridRegion.Datasource=ds

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

gridRegion.DataBind()

‘Tidy up and close the connection variable MyConn.’

MyConn.Close

‘Set the text property of the label [regionalHeader] to the contents of the Session variable named ‘subsetLabel’, add the contents of the Session variable named ‘numberofseats’ to it within brackets so that the label at the top of the web form will display ‘Greater London (74 seats)’ or similar. Both Session variables are set within the relevant election map and come across when the user clicks on the ‘Stats’ button.’

regionalHeader.Text = Session("subsetLabel") & " (" & Session("numberofseats") & " seats)"

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

end if

‘The doLosses function is bound to the OnItemDataBound event of the Datagrid control [gridRegion]. It adds the losses for each party within that particular region to the Datagrid using a SELECT…CASE…END SELECT block. The losses go into the fourth column of the web control.’

‘Declare a string variable [winningparty] which is assigned the contents of the winningparty field – e.Item.Cells(0).Text – which is the first column in the datagrid.’

dim winningparty as string = e.Item.Cells(0).Text

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

select case winningparty

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

Case "Labour"

‘Set the losses field in the datagrid [gridRegion] to display the contents of the Session variable named ‘lablosses’. The losses column is the fourth column in the datagrid and it has its datafield attribute set to an empty string – DataField=””.’

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

‘Similarly for all the other party losses.’

Case "Conservative"

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

Case "Liberal Democrats"

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

Case "SNP"

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

Case "Plaid Cymru"

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

Case "DUP"

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

Case "UUP"

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

Case "SDLP"

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

Case "Sinn Fein"

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

Case "Independent"

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

‘Traditionally the speaker always gets re-elected unopposed to Parliament and no losses or gains are appropriate here. Only hyphens are displayed for the Speaker row for these two columns.’

Case "Speaker"

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

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

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

end select

</script>

<html>

<head>

<title>Statistics for this region...</title>

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

</head>

‘The onblur event for the body tag is set to ‘self.focus()’ so that the user MUST click the ‘Close window’ text before being able to return to the election map.’

<body bgcolor="silver" onblur="self.focus()">

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

<table width="100%"><tr>

<td><asp:Label runat="server" id="regionalHeader"></asp:Label><br><br></td></tr>

</table>

‘The regional stats are displayed in the following datagrid [gridRegion]. An ASP label [regionalHeader] above the control displays the region and the total number of seats.’

<asp:DataGrid

runat="server"

id="gridRegion"

onItemDataBound="doLosses"

AutoGenerateColumns="false"

BorderColor="Black"

ItemStyle-BackColor="white"

AlternatingItemStyle-Font-Name="Times New Roman"

AlternatingItemStyle-BackColor="white"

Font-Name="Times New Roman"

BackColor="#E7EFFF"

Headerstyle-Font-Name="Arial"

Headerstyle-ForeColor="blue"

Headerstyle-BackColor="#BDCFE7"

cellspacing="0"

width="330"

cellpadding="4"

GridLines="Horizontal">

‘There are four bound columns, winningparty, partytotals, gains and an empty column which is filled from the doLosses function via the onItemDataBound event.’

<columns>

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

<asp:BoundColumn HeaderText="No. of seats" HeaderStyle-HorizontalAlign="right" ItemStyle-HorizontalAlign="right" DataField="partytotals" />

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

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

</columns>

</asp:DataGrid>

<P align="right">

‘A span puts the text ‘Close window’ underneath the datagrid so that the user can return to the election map.’

<span style="cursor:pointer;color:blue" title=”Click to close this window” onClick="window.close()" >Close window</span>

</form>

</body>

</html>

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