displayMPs.aspxReturn to the Documentation page
  

functionsPage_Load
bindData
Page_Change
doBackColour
linkToMainMenu

<%@ Page Language="vb" %>

‘Import the namespaces below to enable a connection to an Access 2000 database’

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

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

<script runat="server">

‘When the page loads it first runs the bindData function…’

bindData

‘The bindData function connects to the Access 2000 database called election2001.mdb and it puts the data into a datagrid control on the web page named gridMP’

‘The string variable Access2000 holds the query string – i.e. select the fields country, constituency, etc… from the election2001 database and sort them by constituency in ascending order [order by constituency]. A calculated field, named majority, is also produced from [firstvotes-secondvotes] which produces the MP's majority.

Note: the calculated field 'majority' has to be defined within the query string using the Jet SQL syntax whereas it doesn't have to be under the MySQL version - see the documentation for Election 2005.’

Dim Access2000 as string = "Select country, constituency, first, firstvotes-secondvotes as majority, result, winningparty from fullresults order by constituency"

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

Dim ds as DataSet=New DataSet()

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

Dim Cmd as New ODBCDataAdapter(Access2000,MyConn)

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

Cmd.Fill(ds,"fullresults")

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

gridMP.Datasource=ds.Tables("fullresults").DefaultView

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

gridMP.DataBind()

‘The connection variable MyConn is now closed.’

MyConn.Close()

‘The Page_Change function runs when a user clicks on a number at the bottom of the datagrid [gridMP]. It’s called by the OnPageIndexChanged event of the datagrid.’

gridMP.CurrentPageIndex = e.NewPageIndex

bindData

‘The doBackColour function runs when each row is written to the screen. It colours the background of the row depending on who won the constituency – a Labour seat will be coloured red, a Tory seat blue etc… The function is called by the OnItemDataBound event of the datagrid [gridMP].’

‘The string variable winningparty is set to what is in the fifth column of that particular row i.e. the party that won the seat. A select Case … end select series of statements then colours the row depending on which party won the seat – the .BackColor property colours the background of the row whilst the .ForeColor property sets the colour of the font.’

Dim winningparty as string = e.Item.Cells(4).Text

select case winningparty

Case "Labour"

e.Item.BackColor = Drawing.Color.FromName("red")

e.Item.ForeColor = Drawing.Color.FromName("white")

Case "Conservative"

e.Item.BackColor = Drawing.Color.FromName("blue")

e.Item.ForeColor = Drawing.Color.FromName("white")

Case "Liberal Democrats"

e.Item.BackColor = Drawing.Color.FromName("orange")

e.Item.ForeColor = Drawing.Color.FromName("white")

Case "SNP", "Plaid Cymru"

e.Item.BackColor = Drawing.Color.FromName("green")

e.Item.ForeColor = Drawing.Color.FromName("white")

Case "UUP"

e.Item.BackColor = Drawing.Color.FromName("#800080")

e.Item.ForeColor = Drawing.Color.FromName("white")

Case "DUP"

e.Item.BackColor = Drawing.Color.FromName("#800000")

e.Item.ForeColor = Drawing.Color.FromName("white")

Case "Sinn Fein"

e.Item.BackColor = Drawing.Color.FromName("#00FF00")

e.Item.ForeColor = Drawing.Color.FromName("black")

Case "SDLP"

e.Item.BackColor = Drawing.Color.FromName("#008000")

e.Item.ForeColor = Drawing.Color.FromName("white")

Case "Winning party"

e.Item.BackColor = Drawing.Color.FromName("#BDCFE7")

e.Item.ForeColor = Drawing.Color.FromName("blue")

Case else

e.Item.BackColor = Drawing.Color.FromName("white")

e.Item.ForeColor = Drawing.Color.FromName("black")

end select

‘Two if..then..else statements then measure the length of the text in the Constituency and MP columns. If the lengths are over 30 and 20 characters respectively then the cssClass property of those cells is set to the ‘lowerFont’ style – see the function init() where this style is defined according to the screen resolution on the client machine.’

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

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

end if

if e.Item.Cells(2).Text.Length > 20 then

e.Item.Cells(2).cssClass = "lowerFont"

end if

‘The function linkToMainMenu redirects the user back to the mainmenu page. This is attached to a LinkButton web control at the top of the page.’

Response.Redirect("mainmenu.aspx")

</script>

<html>

<head>

<title>Members of Parliament</title>

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

‘An empty style container ready to take the styles defined within the above javascript file, which runs when the page loads see the BODY tag below.’

<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" Runat="server">

<table width="100%">

<tr><td rowspan="2" width="85px"><img src="./images/ballotbox.jpg" title="Cast your vote wisely!"></td><td><H3>Members of Parliament</H3></td><td align="right"><asp:LinkButton id="LinkToEdit" onclick="linkToMainMenu" runat="server" Text="Return to the Main menu"></asp:LinkButton></td></tr>

<tr><td>&nbsp;</td><td>&nbsp;</td></tr>

</table><P>

<center>

‘The datagrid [gridMP] which displays the Members of Parliament in pages of 20. The cssClass property is set to gridMP so the font-size and width of the grid change depending on the screen resolution. Notice the OnItemDataBound event which runs the doBackColour function mentioned above.’

<asp:Datagrid Runat="server"

Id="gridMP"

GridLines="Both"

cellpadding="4"

cellspacing="0"

OnItemDataBound = "doBackColour"

Headerstyle-BackColor="#BDCFE7"

Headerstyle-ForeColor="blue"

Headerstyle-Font-Name="Arial"

Headerstyle-Font-Size="12"

BackColor="#E7EFFF"

Font-Name="Arial"

AlternatingItemStyle-BackColor="#E7EFFF"

AlternatingItemStyle-Font-Name="Arial"

BorderColor="Black"

AllowPaging = "True"

PageSize = "20"

PagerStyle-Mode = "NumericPages"

PagerStyle-HorizontalAlign="Center"

PagerStyle-PageButtonCount = "33"

OnPageIndexChanged = "Page_Change"

AutoGenerateColumns="False"

cssClass="gridMP">

‘Since the AutoGenerateColumns is set to false you can define which fields are to be displayed in the datagrid using the <columns> tag. Note all six columns from the dataset [ds] are to be displayed on the web page. The Majority column has a customized format applied to it – DataFormatString=”{0:#,###}” – this will add a thousands separator to the figure to make it readable. Each BoundColumn has a HeaderText property which displays at the top of the column and a DataField property which references the appropriate field in the dataset [ds].’

<columns>

<asp:BoundColumn HeaderText="Country" DataField="country" />

<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="" DataField="result" />

</columns>

</asp:DataGrid>

</form>

</center>

</body>

</html>

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