countrymps.aspxReturn to the Documentation page
  

functionsbindData
showMPs
doFormatting
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">

‘The bindData function is called from the showMPs function – see below. It displays all the MPs for the country selected in the DataGrid web control [gridMP].’

‘If the user has made a selection from the ASP listbox control [listBox1] then….’

if listBox1.SelectedIndex >= 0 then

‘Declare a string variable [Access2000] to hold the SQL statement SELECT… FROM…WHERE…ORDER BY. Firstvotes-secondvotes produces the majority in each constituency.’

Dim Access2000 as string = "Select fullresults.*, [firstvotes]-[secondvotes] as majority from fullresults where country='" & listBox1.SelectedItem.Text & "' order by id"

 

‘Declare a new ODBCConnection object [MyConn] which uses the parameter strConn, which is initially defined within the web.config file.’

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

‘Declare a new DataSet object [ds] which will hold the data retrieved from the database.’

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)

‘The Fill method the ODBCDataAdapter object puts the data into the dataset as a table [mps].’

Cmd.Fill(ds,"mps")

‘The Datagrid web control [gridMP] has its datasource property assigned to the dataset.’

gridMP.Datasource=ds

‘The DataBind method fills the datagrid on the web page with the data for the country selected by the user.’

gridMP.DataBind()

‘Declare a string variable [Access2001] to hold the following SQL statement - SELECT... IIF... FROM... WHERE... GROUP BY... ORDER BY... This statement will compute all the party totals for the selected country. The IIF part changes some of the party names slightly so, for example, 'UUP' is amended to display as 'Ulster Unionists' in the datalist web control [dataList1].’

Dim Access2001 as string = "Select 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 changedpartyname, count(winningparty) as total from fullresults where country='" & listBox1.SelectedItem.Text & "' group by winningparty order by 2 desc, 1"

 

‘Declare a new ODBCConnection object [MyConn1] which uses the parameter strConn, which is initially defined within the web.config file.’

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

‘Open the connection to the election2001 database.’

MyConn1.Open

‘Declare a new ODBCCommand object [Cmd1] with the parameters Access2001 and MyConn1.’

Dim Cmd1 as New ODBCCommand(Access2001,MyConn1)

‘Declare a variable of type ODBCDataReader [reader].’

Dim reader as ODBCDataReader

‘Fill the reader object with the requested data using the ExecuteReader method.’

reader = Cmd1.ExecuteReader

‘Set the datalist's [dataList1] datasource property to the reader object.’

dataList1.DataSource = reader

‘The DataBind method fills the datalist on the web page with the party totals for the country selected by the user.’

dataList1.DataBind()

‘Display the text ‘Members of Parliament for ’ together with the country selected from the listbox [listBox1] and the numbers of MPs [gridMP.items.count] and the text ‘ seats’ in the ASP label control [countryLabel]. This acts as a heading for the data displayed in the datagrid [gridMP]. Underneath the label control is a summary of each party’s total number of MPs as already described above, displayed within a datalist web control [dataList1] courtesy of the reader object.’

countryLabel.Text = "Members of Parliament for " & listBox1.SelectedItem.Text & " (" & gridMP.items.count & " seats)<p>"

‘Set up Session variables to hold the country picked, the title of the data and the dataset. These will be needed by showMP.aspx if the user clicks the ‘Show details’ column.’

Session("picked") = listBox1.selectedItem.Text

Session("subsetLabel") = "Members of Parliament for " & listBox1.selectedItem.Text

Session("subsetDataSet") = ds

‘Tidy up and close both connection objects [MyConn & MyConn1] and the reader object [reader].’

reader.Close()

MyConn.Close()

MyConn1.Close()

‘If the user makes no selection in the listbox [listBox1] then kick them out of the script here.’

end if

‘The showMPs function is called when the user clicks the ‘Show MPs’ button on the page. All it does is to run the bindData function mentioned above.’

bindData

‘The doFormatting function is bound to the OnItemDataBound event of the Datagrid control [gridMP]. It changes the font-size of certain columns if their length is greater than a certain number of characters. Note: the first column – the record number - can be accessed by e.Item.Cells(0)’

‘If the length of the constituency field is greater than 30 then….’

if e.Item.Cells(1).Text.Length > 30 then

‘….assign the cssClass property to 'lowerfont'. This style is defined within the onload.js file - see below and the documentation for this javascript file for the relevant coding.’

e.Item.Cells(1).cssClass = "lowerfont"

end if

‘Similarly for the Winning party column….’

if e.Item.Cells(4).Text.Length > 15 then

e.Item.Cells(4).cssClass = "lowerfont"

end if

‘...and the result column.’

if e.Item.Cells(5).Text.Length > 15 then

e.Item.Cells(5).cssClass = "lowerfont"

end if

‘An ASP linkbutton control in the top right-hand corner of the page redirects the user back to the Other stats menu when clicked.’

Response.Redirect("otherstats.aspx")

</script>

<html>

<head>

<title>MPs by country</title>

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

<script type="text/Javascript" src=".\otherfiles\onload.js"></script><style> </style>

</head>

‘The init function runs when the page first loads on the user's machine. The function is contained within the onload.js file which is loaded automatically by the <script> tag above - see the onload.js file for further details.’

<body bgcolor="white" onload="init()">

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

‘The first table displays the ballot box [ballotbox.jpg] in the top left-hand corner together with a label [View a country’s MPs] 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="3" width="85px" valign="top"><img src="./images/ballotbox.jpg" title="Cast your vote wisely!"></td>

<td><asp:Label id="Label1" runat="server"><u>View a country's MPs</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 colspan="2">&nbsp;</td></tr>

‘Underneath, in the second row of the table, is an ASP listbox [listBox1] displaying the four countries of the British Isles – England, Scotland, etc. The user selects a country and then clicks the ASP button [btnLoadMPs] to the right which runs the showMPs function already discussed above.’

<tr>

<td>Select a country to see its MPs:<p>

<asp:ListBox id="listBox1" runat="server" size="1">

<asp:listitem>England</asp:listitem>

<asp:listitem>Northern Ireland</asp:listitem>

<asp:listitem>Scotland</asp:listitem>

<asp:listitem>Wales</asp:listitem>

</asp:ListBox>

</td>

<td><asp:Button id="btnLoadMPs" onclick="showMPs" runat="server" Text="Show MPs"></asp:Button></td>

</tr>

</table>

<p>

‘When the users selects a country and clicks the ‘Show MPs’ button’ the results are displayed in the following datagrid [gridMP] on the web page. Just above the datagrid is an ASP label control which displays a country label [countryLabel]. This is formatted as <H2> on the page and acts as a heading for the contents of the datagrid.’

<center><asp:Label runat="server" id="countryLabel"></asp:Label><asp:DataList id="dataList1" runat="server" repeatdirection="horizontal"><ItemTemplate><%# Container.DataItem("changedpartyname") & ": " & Container.DataItem("total") & "  " %></ItemTemplate></asp:DataList><P>

<asp:Datagrid id="gridMP" cssClass="gridMP" Runat="server" AutoGenerateColumns="False" AllowPaging="false" BorderColor="Black" AlternatingItemStyle-Font-Name="Arial" AlternatingItemStyle-BackColor="#E7EFFF" Font-Name="Arial" BackColor="#E7EFFF" Headerstyle-Font-Size="12" Headerstyle-Font-Name="Arial" Headerstyle-ForeColor="blue" Headerstyle-BackColor="#BDCFE7" OnItemDataBound="doFormatting" cellspacing="0" cellpadding="4" GridLines="Both">

‘There are six bound columns, id, constituency, first, majority, winningparty and result. The '[firstvotes]-[secondvotes] as majority' calculated field produces the MP's winning majority which is formatted using the DataFormatString="{0:#,###}" – this produces a thousands separator and makes the numbers easier to read. The final column is a hyperlink column [See details].’

<columns>

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

<asp:BoundColumn HeaderText="Constituency" DataField="Constituency" />

<asp:BoundColumn HeaderText="MP" DataField="first" />

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

<asp:BoundColumn HeaderText="Winning party" DataField="winningparty" />

<asp:BoundColumn HeaderText="Result" DataField="result" />

‘When the user clicks ‘Show details’ a new window is opened using javascript - the window.open method. It opens the file showMP.aspx with all the details of the selected constituency displayed. The window will appear 10 pixels down from the top left-hand corner of the window. Check out the showMP.aspx file under Documentation.’

<asp:hyperlinkColumn HeaderText="See details" DataTextFormatString="Show details" DataTextField="id" HeaderStyle-HorizontalAlign="center" ItemStyle-HorizontalAlign="center" DataNavigateUrlField="id" DataNavigateUrlFormatString="javascript:var w=window.open('showMP.aspx?id={0}',null,'left=10,top=10,scrollbars');" />

</columns>

</asp:Datagrid>

</center>

</form>

</body>

</html>

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