votingstats.aspxReturn to the Documentation page
  

functionsPage_Load
deleteVotesCast
produceVotesCast
showPartyVotes
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 showPartyVotes function is called – see below.’

showPartyVotes

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

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

Dim MySQL as string = "Delete from votescast"

‘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 votescast. 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 produceVotesCast function is run from a hidden button [btnPartyVotes]. After it compiles all the voting stats for all the parties it is no longer needed. All the votes are put into the table votescast.’

‘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 and insert the data into the table named votescast.’

MySql(0) = "Insert into votescast Select firstparty, sum(firstvotes) from fullresults group by firstparty"

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

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

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

MySql(15) = "Delete from votescast 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 showPartyVotes 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 the election.’

‘Declare a string variable [MySQL] to hold the SQL statement SELECT… FROM… GROUP BY… ORDER BY…. This STATEMENT adds up all the votes for each party and sorts them according to who polled the most [Labour – 9,534,459] down to the party that polled the least number of votes [Telepath – 34] – ORDER BY 2 DESC. 2 refers to the second field in the SQL statement which is the sum(votes) calculated field.’

Dim MySQL as string = "Select party, sum(votes) from votescast group by party order by 2 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 ‘partyvotes’ in the dataset.’

Cmd.Fill(ds,"partyvotes")

‘Set the datasource property of the Datagrid [gridVotes] to the table ‘partyvotes’ in the dataset.’

gridVotes.Datasource=ds.Tables("partyvotes").DefaultView

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

gridVotes.DataBind()

‘Close the connection object.’

MyConn.Close()

‘The doChangePartyName function is attached to the OnItemDataBound event of the Datagrid web control [gridVotes]. 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 first column in the datagrid.’

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

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

select case party

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

Case "Lab"

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

Case "Con"

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

Case "Lib Dem"

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

Case "SNP"

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

Case "PC"

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

Case "UUP"

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

Case "DUP"

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

Case "Ind"

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

Case "UKIP"

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

Case "Soc All"

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

Case "SSP"

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

Case "Soc Lab"

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

Case "BNP"

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

Case "SDLP"

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

Case "Lib"

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

Case "Green"

e.Item.Cells(0).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>Votes cast for the parties</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 overall] 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 overall</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="produceVotesCast" id="btnPartyVotes" Text="Party votes"></asp:Button>&nbsp;<asp:Button runat="server" onclick="deleteVotesCast" id="btnDeleteVotes" Text="Delete Party votes"></asp:Button></td>

<td>&nbsp;</td>

</td>

</table>

<p>

<center><H2><asp:Label runat="server" id="partyLabel">The votes cast in May 2005</asp:Label></H2><P>

‘The Datagrid web control [gridVotes] displays the votes cast for every party at the election and ranks them in descending order – highest down to lowest.’

<asp:Datagrid id="gridVotes" Runat="server" OnItemDataBound = "doChangePartyName" Font-Size="16" Headerstyle-HorizontalAlign="center" AutoGenerateColumns="false" BorderColor="Black" AlternatingItemStyle-Font-Size="16" 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="350" cellpadding="4" GridLines="Both">

‘There are two bound columns, party and sum(votes). The latter has the DataFormatString set to {0:#,###} to display the thousands separator – 9,534,459 is much easier to read than 9534459.’

<columns>

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

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