regionalstats.aspxReturn to the Documentation page


<%@ 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">

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


‘The showMPs function connects to the MySQL database called election2005 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 [MySQL] to hold the SQL statement SELECT…FROM…GROUP BY…WITH ROLLUP. The statement totals up the MPs for each party in each region [district]. Note: three of the regions are also equivalent to countries – Northern Ireland, Scotland & Wales. The WITH ROLLUP statement adds a subtotal after the data for each region e.g. Eastern England 62, and the formatting for this row is amended by code in the doTotalFormatting function, which can be seen below.’

Dim MySQL as string = "Select district, winningparty, count(winningparty) from fullresults group by district, winningparty with rollup"

‘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.’


‘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.’


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



‘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.’


‘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 ‘WITH ROLLUP’ statement 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 ROLLUP row then...’

if e.Item.Cells(1).Text.Equals("&nbsp;") 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…’


‘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 both the first cell is empty and the second cell contains ‘646’ then…’

if e.Item.Cells(0).Text.Equals("&nbsp;") and e.Item.Cells(1).Text.Equals("646") then

‘Put the text ‘Grand total:’ into the first cell of that row AND…’

e.Item.Cells(0).Text = "Grand total:"

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

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

‘Switch off the bold attribute.’

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

end if




<title>The stats for each region</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 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%">


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







<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, district, winningparty and count(winningparty). The regions are sorted into alphabetical order on the screen, starting with Eastern England.’


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

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

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