mostvulnerable.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">

‘The showSeats function connects to the election2005 database and extracts the top 20 target seats for each party. It displays the results in the datagrid web control [gridMP].’

‘If the user doesn’t select a party from the listbox [listBox1] but just clicks the button with the caption ‘Show the most vulnerable seats’, then nothing happens and the function ends.’

if listBox1.SelectedIndex >= 0 then

‘Declare a string variable [MySQL] to hold the SQL statement SELECT… FROM… WHERE… LIKE… ORDER BY… LIMIT. This statement extracts all the fields plus computes the majority from the table fullresults but only for those records where the secondparty field is equal to the first three letters of the selection – i.e. if Labour is selected in the listbox [listBox1] then the ‘where’ part of the statement would be “where secondparty like “Lab%”. The % character allows for any other character to appear after the first three letters so this allows “Lib%” to pick up those records where the second party is ‘Lib Dem’. Finally the records are sorted in ascending order on the majority field so that the most vulnerable seats appear at the top of the web page. The LIMIT 0, 20 syntax dictates that only the first 20 records are to be displayed in the web control [gridMP]. Note: firstvotes-secondvotes is referred to as ‘majority’ in the statement and can be accessed as such by one of the bound columns in the datagrid web control [gridMP].’

Dim MySQL as string = "Select *, firstvotes-secondvotes as majority from fullresults where secondparty like '" & left(listBox1.selectedItem.Text,3) & "%' order by majority asc limit 0, 20"

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

‘The object variable of type DataSet [ds] is declared ready to hold the results before being bound to the datagrid control [gridMP].’

Dim ds as DataSet=New DataSet()

‘The object variable of type ODBCDataAdapter [Cmd] takes the string variable MySQL and the connection variable MyConn as parameters.’

Dim Cmd as New ODBCDataAdapter(MySQL,MyConn)

‘The Fill method of the ODBCDataAdapter object puts the results of the query into the dataset named ds as a table named ‘mps’.’


‘The datagrid web control named gridMP has its Datasource property set to the mps table.’


‘The data is bound to the control on the web page using the DataBind method.’


‘Declare a string variable [vulnerable] to hold the user’s selection from listBox1.’

Dim vulnerable as string = listBox1.SelectedItem.Text

‘Start a SELECT… CASE… END SELECT BLOCK which looks at what’s in the variable [vulnerable].’

select case vulnerable

‘If the user selected ‘Conservative’ from the listbox [listBox1] then….’

Case "Conservative"

‘Set the text property of the Label control [vulnerableLabel] to ‘Top targets for the Conservatives’ and similarly for the other parties in the listbox control….’

vulnerableLabel.Text = "Top targets for the " & listBox1.SelectedItem.Text & "s"

Case "Liberal Democrats"

vulnerableLabel.Text = "Top targets for the " & listBox1.SelectedItem.Text

Case "SNP"

vulnerableLabel.Text = "Top targets for the " & listBox1.SelectedItem.Text

Case "PC"

vulnerableLabel.Text = "Top targets for Plaid Cymru"

Case "UUP"

vulnerableLabel.Text = "Top targets for the Ulster Unionists"

Case "DUP"

vulnerableLabel.Text = "Top targets for the Democratic Unionists"

Case "SDLP"

vulnerableLabel.Text = "Top targets for the " & listBox1.SelectedItem.Text

Case "Respect"

vulnerableLabel.Text = "Top targets for the Respect party"

Case Else

vulnerableLabel.Text = "Top targets for " & listBox1.SelectedItem.Text

end select

‘If the number of items in the datagrid [gridMP] is 0, then …..’

if gridMP.items.count = 0 then

‘Display the text message ‘No seats are vulnerable to this party’ in the label [vulnerableLabel].’

vulnerableLabel.Text = "No seats are vulnerable to this party"

‘Terminate the IF… THEN… END IF BLOCK.’

end if

‘Store the text property of the label [vulnerableLabel] in the Session variable named ‘subsetLabel’ – this will be used by the showMP function if the user selects the show details column in the datagrid [gridMP].’

Session("subsetLabel") = vulnerableLabel.Text

‘Similarly store the dataset [ds] in the Session variable named ‘subsetDataSet’ – this will also be used by the showMP function (shown below).’

Session("subsetDataSet") = ds

‘Tidy up and close the connection variable MyConn.’


end if

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





<title>The most vulnerable seats at the next election</title>

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

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


‘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 table displays the election cross [cross.gif] in the top left-hand corner together with a label [The most vulnerable seats] 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="3" width="85px" valign="top"><img src="./images/cross.gif" title="Make voting compulsory!"></td>

<td><asp:Label id="Label1" runat="server"><u>The most vulnerable seats</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>



<td colspan="2">&nbsp;</td></tr>


‘Underneath, in the third row of the table, is an ASP listbox [listBox1] displaying the major parties of the British Isles – Labour, Conservative, etc. The user selects a party and then clicks the ASP button [btnLoadSeats] to the right which runs the showSeats function already discussed above.’


<td>Select a party to see its top 20 targets:<p>

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



<asp:ListItem>Liberal Democrats</asp:ListItem>





<asp:ListItem>Sinn Fein</asp:ListItem>





<td><asp:Button id="btnLoadMPs" onclick="showSeats" runat="server" Text="Show the most vulnerable seats"></asp:Button></td>




‘When the user selects a party and clicks the ‘Show the most vulnerable seats’ button, the results are displayed in the following datagrid [gridMP] on the web page. Just above the datagrid is an ASP label [vulnerableLabel] which is formatted as <H2> on the page and this control acts as a heading for the contents of the datagrid.’

<center><H2><asp:Label runat="server" id="vulnerableLabel"></asp:Label></H2><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" cellspacing="0" cellpadding="4" GridLines="Both">

‘There are four bound columns, id, constituency, majority and winningparty. The majority column is formatted using the DataFormatString="{0:#,###}" – this produces a thousands separator and makes the majority easier to read. The final column is a hyperlink column [See details].’


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

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

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

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

‘When the user clicks ‘Show details’ a new window is opened using javascript - the 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 for the coding.’

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