votingstatsbyregion.aspxReturn to the Documentation page
  

functionsPage_Load
deleteVotesCastByRegion
produceVotesCastByRegion
showPartyVotesCastByRegion
doChangePartyName
linkToOtherStatsMenu

‘Disable the viewstate at page level to improve the application's performance.’

<%@ 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 [Access2000] to hold the SQL statement DELETE FROM….’

Dim Access2000 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 Access2000 database.’

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

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

Dim Cmd as New ODBCCommand(Access2000,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 [Access2000] of sixteen SQL statements of the form INSERT INTO… SELECT…FROM GROUP BY….

Dim Access2000(16) as string

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

Access2000(0) = "Insert into votescastbyregion Select region as region, firstparty as party, sum(firstvotes) as votes from fullresults group by region, firstparty"

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

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

‘The final statement in the array [Access2000] 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.’

Access2000(15) = "Delete from votescastbyregion where party is null"

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

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 [Access2000], i.e. the first time the loop runs the property will be set to Access2000(0).’

Cmd.CommandText = Access2000(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/region provided they have polled 5,000 votes or more.’

‘Declare a string variable [Access2000] 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 Access2000 as string = "Select region, party, sum(votes) as partyvotes from votescastbyregion group by region, 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={Microsoft Access Driver (*.mdb)};DBQ=c:Uk elections/election2001/database/election2001.mdb;Uid=;Pwd=;". ’

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 Access2000 and MyConn. This object pulls the requested data from the election2001 database and puts it into the dataset [ds].’

Dim Cmd as New ODBCDataAdapter(Access2000,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 "UKU"

e.Item.Cells(1).Text = "United Kingdom Unionists"

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 2001 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 [ballotbox.jpg] 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 ballot box graphic [ballotbox.jpg] 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/ballotbox.jpg" title="Cast your vote wisely!"></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, region, 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="region" />

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

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

</columns>

</asp:Datagrid>

</center>

</form>

</body>

</html>

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