regionalstats.aspxReturn to the Documentation page
  

functionsPage_Load
showMPs
linkToOtherStatsMenu
doTotalFormatting

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

‘When the page loads it first runs the showMPs function…’

showMPs

‘The showMPs function connects to the Access 2000 database called election2001.mdb and puts the data into a datagrid control on the web page named ‘gridMP’. It’ll produce subtotals for each party in each region of the British Isles.’

‘Declare a string variable [Access2000] to hold the SQL statement SELECT…IIF...FROM…GROUP BY…UNION SELECT...FROM...WHERE...UNION...SELECT... etc, etc...ORDER BY.... The statement totals up the MPs for each party in each region [region]. Note: three of the regions are also equivalent to countries – Northern Ireland, Scotland & Wales.

The IIF part of the statement merely tidies up the party abbreviations into a more suitable label - e.g. 'SNP' will be displayed as 'Scottish Nationalists' on the page. The complicated UNION SELECT statements add subtotals after the data for each region e.g. Eastern England 63, and the formatting for this row is amended by code in the doTotalFormatting function, which can be seen below.

The subtotal for Eastern England will illustrate the syntax for that particular UNION statement:

union select 'Eastern England:', ' ', count(winningparty) from fullresults where region='Eastern England'

The first column is filled with the text 'Eastern England:', the second column of the grid is deliberately left blank - ' ' and the final column contains the number of seats in Eastern England - count(winningparty). Similarly for all the other regions/regions...

Note each UNION statement must have the same number of columns [3] as in the main SELECT statement.

Finally the last UNION SELECT statement puts the string 'zzzz' into the first column. This is done so the 'Grand total' row is placed at the bottom of the grid and not after the listing for 'Eastern England' - 'zzzz' is eventually replaced with an empty text string from within the doTotalFormatting function shown below.’

Dim Access2000 as string = "Select region, IIf(winningparty='Conservative', 'Conservatives' , IIF(winningparty='UUP', 'Ulster Unionists' , IIF(winningparty='DUP', 'Democratic Unionists', IIF(winningparty='Independent', 'Independents', IIF(winningparty='SNP', 'Scottish Nationalists', winningparty))))) as changedparty, count(winningparty) as partytotals from fullresults group by region, winningparty union select 'Eastern England:', ' ', count(winningparty) from fullresults where region='Eastern England' union select 'Tyne & Wear:', ' ', count(winningparty) from fullresults where region='Tyne & Wear' union select 'Greater London:', ' ', count(winningparty) from fullresults where region='Greater London' union select 'Merseyside & Gr. Manchester:', ' ', count(winningparty) from fullresults where region='Merseyside & Gr. Manchester' union select 'Northern England:', ' ', count(winningparty) from fullresults where region='Northern England' union select 'Northern Ireland:', ' ', count(winningparty) from fullresults where region='Northern Ireland' union select 'Scotland:', ' ', count(winningparty) from fullresults where region='Scotland' union select 'South Central England:', ' ', count(winningparty) from fullresults where region='South Central England' union select 'South-east England:', ' ', count(winningparty) from fullresults where region='South-east England' union select 'South-west England:', ' ', count(winningparty) from fullresults where region='South-west England' union select 'Wales:', ' ', count(winningparty) from fullresults where region='Wales' union select 'West & South Yorkshire:', ' ', count(winningparty) from fullresults where region='West & South Yorkshire' union select 'West Central England:', ' ', count(winningparty) from fullresults where region='West Central England' union select 'West Midlands:', ' ', count(winningparty) from fullresults where region='West Midlands' union select 'zzzz', 'Grand total: ', count(winningparty) from fullresults order by 1, 3 desc , 2"

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

‘Open the connection to the database.’

MyConn.Open()

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

Dim Cmd as New ODBCCommand(Access2000,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.’

gridMP.Datasource=reader

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

gridMP.DataBind()

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

reader.Close()

MyConn.Close()

‘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 doTotalFormatting function runs when each row of the Datagrid is written to the screen. It looks for the rows which have been added to the datagrid by the ‘UNION SELECT’ statements and formats them as shown below. This function is called by the OnItemDataBound event of the Datagrid web control [gridMP].’

‘If the second cell of each row is empty i.e. it’s part of a subtotal row then...’

if e.Item.Cells(1).Text.Equals(" ") then

‘Embolden the name of the region in the first cell AND...’

e.Item.Cells(0).Font.Bold = True

‘Embolden the subtotal of MPs in the second cell AND…’

e.Item.Cells(2).Font.Bold = True

‘Align the subtotals to the right of the cell AND…’

e.Item.Cells(2).Attributes.Add("align", "right")

‘Make the first cell in each row span TWO columns AND….’

e.Item.Cells(0).ColumnSpan = 2

‘Now remove the second cell in each row AND…’

e.Item.Cells.RemoveAt(1)

‘Change the background color of the subtotal row to blue AND…’

e.Item.BackColor = Drawing.Color.FromName("blue")

‘Change the font colour of the subtotal row to white.’

e.Item.ForeColor = Drawing.Color.FromName("white")

end if

‘If the first cell contains ‘zzzz’ then…’

if e.Item.Cells(0).Text.Equals("zzzz") then

‘Replace it with an empty string ""’

e.Item.Cells(0).Text = ""

‘Align the text to the right in the second cell AND…’

e.Item.Cells(1).Attributes.Add("align", "right")

‘...also align the text to the right in the third cell AND…’

e.Item.Cells(2).Attributes.Add("align", "right")

‘...switch off the bold attribute for the first cell.’

e.Item.Cells(0).Font.Bold = False

end if

</script>

<html>

<head>

<title>The stats for each region</title>

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

</head>

<body bgcolor="white">

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

‘The table displays a ballot box image [ballotbox.jpg] in the top left-hand corner together with a label [The results from each region] 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="2" width="85px" valign="top"><img src="./images/ballotbox.jpg" title="Cast your vote wisely!"></td>

<td><asp:Label id="Label1" runat="server"><u>The results from each region</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>&nbsp;</td><td>&nbsp;</td>

</tr>

</table>

<p>

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

‘The Datagrid web control [gridMP] displays the stats for each region of the British Isles, starting with Eastern England and finishing with the data from the West Midlands. A subtotal is added after each region which is formatted by the function [doTotalFormatting] and this is called by the control’s OnItemDataBound event.’

<asp:Datagrid id="gridMP" Runat="server" OnItemDataBound = "doTotalFormatting" Font-Size="14" Headerstyle-HorizontalAlign="center" AutoGenerateColumns="false" BorderColor="Black" AlternatingItemStyle-Font-Size="14" 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="500" cellpadding="4" GridLines="Both">

‘There are three bound columns, region, changedparty and partytotals. The regions are sorted into alphabetical order on the screen, starting with Eastern England.’

<columns>

<asp:BoundColumn HeaderText="Region" DataField="region" />

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

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

</columns>

</asp:Datagrid>

</center>

</form>

</body>

</html>

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