votingstatsbyregion.aspxReturn to the Documentation page
  

functionsPage_Load
deleteVotesCastByRegion
produceVotesCastByRegion
showPartyVotesCastByRegion
doChangePartyName
linkToOtherStatsMenu

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

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

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

<script runat="server">

‘When the page first loads on the client machine the showPartyVotesCastByRegion function is called – see below.’

showPartyVotesCastByRegion

‘The deleteVotesCastByRegion function is attached to a hidden button [btnDeleteVotes]. This button is used to delete all the records in the table votescastbyregion – it only needs to be used for administration/testing purposes. Once all the regional voting statistics have been compiled into the votescastbyregion table it is no longer needed.’

‘Declare a string variable [MySQL] to hold the SQL statement DELETE FROM….’

Dim MySQL as string = "Delete from votescastbyregion"

‘Declare a variable [MyConn] of type ODBCConnection which references the strConn key/value in the web.config file – this key/value pair holds the connection string to the MySQL database.’

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

‘Declare an object [Cmd] of type ODBCCommand which takes the parameters MySQL and MyConn defined above.’

Dim Cmd as New ODBCCommand(MySQL,MyConn)

‘Open the connection to the database.’

MyConn.Open()

‘Use the ExecuteNonQuery method of the Cmd object to delete all the records in votescastbyregion. ExecuteNonQuery is used when you don’t need to return any records to the users’s screen.’

Cmd.ExecuteNonQuery()

‘Tidy up and close the connection object.’

MyConn.Close()

‘The produceVotesCastByRegion function is run from a hidden button [btnPartyVotes]. After it compiles all the regional voting stats for all the parties it is no longer needed. All the votes are put into the table votescastbyregion.’

‘Declare an array variable [MySQL] of sixteen SQL statements of the form INSERT INTO… SELECT…FROM GROUP BY….

Dim MySql(16) as string

‘The first element MySql(0) will sum all the votes for the parties that came first within each region/district and insert the data into the table named votescastbyregion.’

MySQL(0) = "Insert into votescastbyregion Select district, firstparty, sum(firstvotes) from fullresults group by district, firstparty"

‘Similarly for all the other candidates - all the data gets appended to that already contained within the votescastbyregion table.’

MySql(1) = "Insert into votescastbyregion Select district, secondparty, sum(secondvotes) from fullresults group by district, secondparty"
MySql(2) = "Insert into votescastbyregion Select district, thirdparty, sum(thirdvotes) from fullresults group by district, thirdparty"
MySql(3) = "Insert into votescastbyregion Select district, fourthparty, sum(fourthvotes) from fullresults group by district, fourthparty"
MySql(4) = "Insert into votescastbyregion Select district, fifthparty, sum(fifthvotes) from fullresults group by district, fifthparty"
MySql(5) = "Insert into votescastbyregion Select district, sixthparty, sum(sixthvotes) from fullresults group by district, sixthparty"
MySql(6) = "Insert into votescastbyregion Select district, seventhparty, sum(seventhvotes) from fullresults group by district, seventhparty"
MySql(7) = "Insert into votescastbyregion Select district, eighthparty, sum(eighthvotes) from fullresults group by district, eighthparty"
MySql(8) = "Insert into votescastbyregion Select district, ninthparty, sum(ninthvotes) from fullresults group by district, ninthparty"
MySql(9) = "Insert into votescastbyregion Select district, tenthparty, sum(tenthvotes) from fullresults group by district, tenthparty"
MySql(10) = "Insert into votescastbyregion Select district, eleventhparty, sum(eleventhvotes) from fullresults group by district, eleventhparty"
MySql(11) = "Insert into votescastbyregion Select district, twelfthparty, sum(twelfthvotes) from fullresults group by district, twelfthparty"
MySql(12) = "Insert into votescastbyregion Select district, thirteenthparty, sum(thirteenthvotes) from fullresults group by district, thirteenthparty"
MySql(13) = "Insert into votescastbyregion Select district, fourteenthparty, sum(fourteenthvotes) from fullresults group by district, fourteenthparty"
MySql(14) = "Insert into votescastbyregion Select district, fifteenthparty, sum(fifteenthvotes) from fullresults group by district, fifteenthparty"

‘The final statement in the array [MySql] deletes those records where the party field is blank. The votescastbyregion table should be devoid of blank columns otherwise an error maybe generated when the user first loads the votingstatsbyregion.aspx file on their computer.’

MySql(15) = "Delete from votescastbyregion where party = ''"

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

‘Declare a new ODBCCommand object [Cmd].’

Dim Cmd as New ODBCCommand()

‘Set the Connection property of the Cmd object to the ODBCConnection variable [MyConn].’

Cmd.Connection = MyConn

‘Open the connection....’

MyConn.Open()

‘Declare a variable i of type integer, which will be used in the following FOR...NEXT loop.’

dim i as integer

‘Start the loop with i set to 0....’

for i = 0 to 15

‘Set the CommandText property of the Cmd object to the appropriate SQL statement within the array [MySql], i.e. the first time the loop runs the property will be set to MySql(0).’

Cmd.CommandText = MySql(i)

‘Use the ExecuteNonQuery method of the Cmd object to insert the relevant data into votescast.’

Cmd.ExecuteNonQuery()

‘Continue looping through the array of SQL statements until i is greater than fifteen i.e. sixteen since it will be incremented automatically by 1.’

Next

‘Close the connection object.’

MyConn.Close()

‘The showPartyVotesCastByRegion function runs when the page first loads on the client’s computer – it’s called by the Page_Load event. It displays the total number of votes cast for each party in each region/district provided they have polled 5,000 votes or more.’

‘Declare a string variable [MySQL] to hold the SQL statement SELECT… FROM… GROUP BY… HAVING… ORDER BY…. This STATEMENT adds up all the votes for each party in each region and displays them to the user, first by region and then by number of votes – ORDER BY 1, 3 DESC. The data for Eastern England is displayed first with the regional stats for West Midlands at the bottom. Only parties that polled 5,000 or more votes are shown.’

Dim MySQL as string = "Select district, party, sum(votes) from votescastbyregion group by district, party having sum(votes) > 4999 order by 1, 3 desc"

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

‘ Declare a dataset [ds] to hold the returned data.’

Dim ds as DataSet=New DataSet()

‘Declare a new ODBCDataAdapter object [Cmd] with the parameters MySQL and MyConn. This object pulls the requested data from the election2005 database and puts it into the dataset [ds].’

Dim Cmd as New ODBCDataAdapter(MySQL,MyConn)

‘Use the Fill method to create a table ‘votesbyregion’ in the dataset.’

Cmd.Fill(ds,"votesbyregion")

‘Set the datasource property of the Datagrid [gridVotesByRegion] to the table ‘votesbyregion’ in the dataset.’

gridVotesByRegion.Datasource=ds.Tables("votesbyregion").DefaultView

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

gridVotesByRegion.DataBind()

‘Close the connection object.’

MyConn.Close()

‘The doChangePartyName function is attached to the OnItemDataBound event of the Datagrid web control [gridVotesByRegion]. Instead of an abbreviated party name like ‘Con’ it displays a more suitable label – ‘Conservatives’ – and similarly for all the other parties.’

‘Declare a string variable [party] which is assigned to the contents of the second column in the datagrid.’

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

‘Use a SELECT…CASE…END SELECT coding block to change the party abbreviation to something more suitable.’

select case party

‘When the second column contains ‘Lab’ change the text to ‘Labour’ and likewise for Con, Lib Dem etc….’

Case "Lab"

e.Item.Cells(1).Text = "Labour"

Case "Con"

e.Item.Cells(1).Text = "Conservatives"

Case "Lib Dem"

e.Item.Cells(1).Text = "Liberal Democrats"

Case "SNP"

e.Item.Cells(1).Text = "Scottish Nationalists"

Case "PC"

e.Item.Cells(1).Text = "Plaid Cymru"

Case "UUP"

e.Item.Cells(1).Text = "Ulster Unionists"

Case "DUP"

e.Item.Cells(1).Text = "Democratic Unionists"

Case "Ind"

e.Item.Cells(1).Text = "Independents"

Case "UKIP"

e.Item.Cells(1).Text = "UK Independence Party"

Case "Soc All"

e.Item.Cells(1).Text = "Socialist Alliance"

Case "SSP"

e.Item.Cells(1).Text = "Scottish Socialists"

Case "Soc Lab"

e.Item.Cells(1).Text = "Socialist Labour"

Case "BNP"

e.Item.Cells(1).Text = "British Nationalists"

Case "SDLP"

e.Item.Cells(1).Text = "Social & Democratic Labour"

Case "Lib"

e.Item.Cells(1).Text = "Liberals"

Case "Green"

e.Item.Cells(1).Text = "Green Party"

end select

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

Response.Redirect("otherstats.aspx")

</script>

<html>

<head>

<title>Voting stats by region from the Election 2005 database</title>

‘Two buttons which are no longer needed are hidden from the user and the font-size of the Label [Label1] next to the graphic [cross.gif] is set to 24px’

<style>

#btnPartyVotes, #btnDeleteVotes {visibility: hidden}

#Label1 {font-size: 24px}

</style>

</head>

<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 [Votes cast by 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/cross.gif" title="Make voting compulsory!"></td>

<td><asp:Label id="Label1" font-names="Times New Roman" runat="server"><u>Votes cast by 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>

‘The second row of the table contains two hidden buttons that were used in the initial generation of the votes cast.’

<tr>

<td><asp:Button runat="server" onclick="produceVotesCastByRegion" id="btnPartyVotes" Text="Party votes by region"></asp:Button>&nbsp;<asp:Button runat="server" onclick="deleteVotesCastByRegion" id="btnDeleteVotes" Text="Delete Party votes by region"></asp:Button></td>

<td>&nbsp;</td>

</tr>

</table>

<p>

<center><H2><asp:Label runat="server" id="partyLabel">The votes cast in each region<br></h2><H5>(Parties with 5000 or more votes only)</asp:Label></H5><P>

‘The Datagrid web control [gridVotesByRegion] displays the votes cast for every party in each region and ranks them in descending order – highest number of votes down to the lowest, subject to an initial total of at least 5000 votes.’

<asp:Datagrid id="gridVotesByRegion" Runat="server" OnItemDataBound = "doChangePartyName" 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="570" cellpadding="4" GridLines="Both">

‘There are three bound columns, district, party and sum(votes). The latter has the DataFormatString set to {0:#,###} to display the thousands separator – 1,281,333 is much easier to read than 1281333.’

<columns>

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

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

<asp:BoundColumn HeaderText="Votes" ItemStyle-HorizontalAlign="right" DataFormatString="{0:#,###}" DataField="sum(votes)" />

</columns>

</asp:Datagrid>

</center>

</form>

</body>

</html>

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