addaByelection.aspxReturn to the Documentation page
  

functionsonBtnSendClicked
clearControls
onBtnClearClicked
linkToByelectionMenu

<%@ Page Language="vb" %>

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

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

<script runat="server">

‘The onBtnSendClicked function runs when the user clicks the ‘Add record’ button underneath the web form. It inserts a new record into the fullresults table of the Access 2000 database [election2001.mdb].’

‘If the txtConstituency textbox isn’t empty then run the code underneath otherwise do nothing.’

if txtConstituency.Text <> "" then

‘Declare a variable [Access2000] to hold the following SQL statement – a complicated INSERT INTO… VALUES… Where a parameter is needed in the statement it is represented by ? e.g. "',?,?,'" . See below for more on the parameters needed. Most of the values are derived from the web form using the text property e.g. '" & txtConstituency.Text & "'. ’

Dim Access2000 as string = "INSERT INTO byelections (country, constituency, region, firstparty, first, firstvotes, secondparty, second, secondvotes, thirdparty, third, thirdvotes, fourthparty, fourth, fourthvotes, fifthparty, fifth, fifthvotes, sixthparty, sixth, sixthvotes, seventhparty, seventh, seventhvotes, eighthparty, eighth, eighthvotes, ninthparty, ninth, ninthvotes, tenthparty, tenth, tenthvotes, eleventhparty, eleventh, eleventhvotes, twelfthparty, twelfth, twelfthvotes, thirteenthparty, thirteenth, thirteenthvotes, fourteenthparty, fourteenth, fourteenthvotes, fifteenthparty, fifteenth, fifteenthvotes, sixteenthparty, sixteenth, sixteenthvotes, turnout, swing, swingtoparty, result, winningparty, notes) VALUES ('" & txtCountry.SelectedItem.Text & "','" & txtConstituency.Text & "','" & txtRegion.SelectedItem.Text & "','" & txtFirstparty.Text & "','" & txtFirst.Text & "','" & txtFirstvotes.Text & "','" & txtSecondparty.Text & "','" & txtSecond.Text & "','" & txtSecondvotes.Text & "','" & txtThirdparty.Text & "','" & txtThird.Text & "','" & txtThirdvotes.Text & "',?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'" & txtTurnout.Text & "',?,?,'" & txtResult.SelectedItem.Text & "','" & txtWinningparty.SelectedItem.Text & "',?)"

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

‘Declare a variable [Cmd] of type ODBCCommand which will issue the Access2000 command against the election2001 database i.e. it will insert a record.’

Dim Cmd as New ODBCCommand (Access2000, MyConn)

‘This part of the function deals with fourth place, fifth place, sixth place, etc... in the result. Since the fourth and subsequent places could be vacant an array of ODBCParameters [Params] is declared which feeds into the INSERT INTO statement above.’

Dim Params() As OdbcParameter = { New OdbcParameter("@Fourthparty", txtFourthparty.Text), New OdbcParameter("@Fourth", txtFourth.Text), New OdbcParameter("@Fourthvotes", txtFourthvotes.Text), New OdbcParameter("@Fifthparty", txtFifthparty.Text), New OdbcParameter("@Fifth", txtFifth.Text), New OdbcParameter("@Fifthvotes", txtFifthvotes.Text), New OdbcParameter("@Sixthparty", txtSixthparty.Text), New OdbcParameter("@Sixth", txtSixth.Text), New OdbcParameter("@Sixthvotes", txtSixthvotes.Text), New OdbcParameter("@Seventhparty", txtSeventhparty.Text), New OdbcParameter("@Seventh", txtSeventh.Text), New OdbcParameter("@Seventhvotes", txtSeventhvotes.Text), New OdbcParameter("@Eighthparty", txtEighthparty.Text), New OdbcParameter("@Eighth", txtEighth.Text), New OdbcParameter("@Eighthvotes", txtEighthvotes.Text), New OdbcParameter("@Ninthparty", txtNinthparty.Text), New OdbcParameter("@Ninth", txtNinth.Text), New OdbcParameter("@Ninthvotes", txtNinthvotes.Text), New OdbcParameter("@Tenthparty", txtTenthparty.Text), New OdbcParameter("@Tenth", txtTenth.Text), New OdbcParameter("@Tenthvotes", txtTenthvotes.Text), New OdbcParameter("@Eleventhparty", txtEleventhparty.Text), New OdbcParameter("@Eleventh", txtEleventh.Text), New OdbcParameter("@Eleventhvotes", txtEleventhvotes.Text), New OdbcParameter("@Twelfthparty", txtTwelfthparty.Text), New OdbcParameter("@Twelfth", txtTwelfth.Text), New OdbcParameter("@Twelfthvotes", txtTwelfthvotes.Text), New OdbcParameter("@Thirteenthparty", txtThirteenthparty.Text), New OdbcParameter("@Thirteenth", txtThirteenth.Text), New OdbcParameter("@Thirteenthvotes", txtThirteenthvotes.Text), New OdbcParameter("@Fourteenthparty", txtFourteenthparty.Text), New OdbcParameter("@Fourteenth", txtFourteenth.Text), New OdbcParameter("@Fourteenthvotes", txtFourteenthvotes.Text), New OdbcParameter("@Fifteenthparty", txtFifteenthparty.Text), New OdbcParameter("@Fifteenth", txtFifteenth.Text), New OdbcParameter("@Fifteenthvotes", txtFifteenthvotes.Text), New OdbcParameter("@Sixteenthparty", txtSixteenthparty.Text), New OdbcParameter("@Sixteenth", txtSixteenth.Text), New OdbcParameter("@Sixteenthvotes", txtSixteenthvotes.Text), New OdbcParameter("@Swing", txtSwing.Text), New OdbcParameter("@Swingtoparty", txtSwingtoparty.SelectedItem.Text), New OdbcParameter("@Notes", txtNotes.Text)}

‘Declare a variable [index] to be used in the following FOR...TO...NEXT loop.’

Dim index as Integer

‘Start a FOR...TO...NEXT loop with the index variable set to 0 initially. It will run until the last element of the array [Params] has been accessed which is calculated using the array's length property - Params.Length -1.’

For index = 0 to Params.Length - 1

‘If the value of the parameter is blank i.e. there is no candidate for fourth place, etc then....’

if Params(index).Value = "" then

‘Set that particular parameter to Null e.g. fourthparty=NULL. If the fields are blank it might throw up an error message when you want to display the information in the tables, though this tends to be a problem with MySQL and not Access databases.’

Cmd.Parameters.Add(Params(index)).Value = DBNull.Value

‘Otherwise if there is a candidate for fourth place....’

else

‘Add the parameter's value to the ODBCCommand object [Cmd].’

Cmd.Parameters.Add(Params(index))

‘Ends the IF...THEN...ELSE...END IF block.’

end if

‘Return to the top of the FOR...TO...NEXT block and increment the index variable by 1 i.e. look at the second element within the array [Params] and see if its value is blank.’

next

‘Open the connection to the database.’

MyConn.Open()

‘Execute the INSERT INTO…VALUES command against the byelections table but don’t return any records – use the ODBCCommand object’s ExecuteNonQuery method. This will insert the byelection record into the byelections table of the election2001 database.’

Cmd.ExecuteNonQuery()

‘The connection variable MyConn is now closed.’

MyConn.Close()

end if

 

‘The clearControls function is called when the user clicks the ‘Clear’ button at the bottom of the form - all the textboxes are emptied of text and the dropdownlist boxes are reset. It is actually called by the onBtnClearClicked function which can be seen below.’

‘Starts a FOR EACH...NEXT loop that looks at the type of web server control on the web form.’

for each ctrl As Control in Controls

‘If the control is a textbox then set its text property to "". Note the control has to be first cast as a textbox using the CType function before its text property can be set.’

If TypeOf ctrl Is TextBox then CType(ctrl,TextBox).Text = ""

‘If the control is a dropdown listbox then set its selectedindex property to nothing. As before, the control has to be first cast as a dropdownlist using the CType function before its selectedindex property can be set.’

If TypeOf ctrl Is DropDownList then CType(ctrl,DropDownList).SelectedIndex = 0"

‘Check to see if the control has any child controls within it using the HasControls method. If it has run the clearControls function over them as well and clear the textboxes and labels of any text.’

if ctrl.HasControls then clearControls(ctrl.Controls)

next

‘Return to the calling function which is the onBtnClearClicked() subroutine below.’

return

‘This function resets all the text boxes and other controls on the form when the ‘Clear’ button is clicked.’

‘Calls the clearControls function with all the controls on the page as a parameter. This function is described above.’

clearControls(Page.Controls)

‘Redirects the user back to the byelections menu using an asp Linkbutton web control. This can be found in the top right-hand corner of each page.’

Response.Redirect("byelections.aspx")

</script>

<html>

<head>

<title>Add a By-election to the Election 2001 database</title>

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

</head>

‘When the page loads the code below displays a form which will be filled with data by the user as and when a new byelection result is declared. There are two asp buttons at the bottom of the form – ‘Add record’ [btnSend] which calls the onBtnSendClicked function above and ‘Clear’ [btnClear] which runs the onBtnClearClicked function.’

<body bgcolor="white">

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

<table width="100%">

<tr><td width="85px" rowspan="2"><img src="./images/ballotbox.jpg" title="Cast your vote wisely!"></td>

<td><asp:Label id="Label1" runat="server" ><u>Add a By-election</u></asp:Label></td>

<td align="right"><asp:LinkButton id="LinkToEdit" onclick="linkToByelectionMenu" runat="server" Text="Return to the By-elections menu"></asp:LinkButton></td></tr>

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

</table><P>

<center>

<table bordercolor="silver" cellspacing="0" id="table1" bgcolor="#ffffcd" border="1">

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

<tr><td>&nbsp;Country: <asp:DropDownList id="txtCountry" runat="server" font-names="Times New Roman" size="1">

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

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

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

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

</asp:DropDownList>&nbsp;&nbsp;&nbsp;&nbsp;Region: <asp:DropDownList id="txtRegion" runat="server" font-names="Times New Roman" size="1">

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

<asp:listitem>Greater London</asp:listitem>

<asp:listitem>Merseyside & Gr. Manchester</asp:listitem>

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

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

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

<asp:listitem>South Central England</asp:listitem>

<asp:listitem>South-east England</asp:listitem>

<asp:listitem>South-west England</asp:listitem>

<asp:listitem>Tyne & Wear</asp:listitem>

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

<asp:listitem>West & South Yorkshire</asp:listitem>

<asp:listitem>West Central England</asp:listitem>

<asp:listitem>West Midlands</asp:listitem>

</asp:DropDownList></td></tr>

<tr><td colspan="4">&nbsp;Constituency: <asp:textbox id="txtConstituency" runat="server" font-names="Times New Roman"></asp:textbox></td></tr>

<tr>

<td align="middle" id="lblPlace">&nbsp;</td>

<td align="middle" id="lblParty">Party</td>

<td align="middle" id="lblName">Name</td>

<td align="middle" id="lblVotes">Votes</td>

</tr>

<tr>

<td align="middle"><b>1.</b></td>

<td align="middle"><asp:textbox id="txtFirstparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtFirst" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtFirstvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>2.</b></td>

<td align="middle"><asp:textbox id="txtSecondparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtSecond" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtSecondvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>3.</b></td>

<td align="middle"><asp:textbox id="txtThirdparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtThird" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtThirdvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>4.</b></td>

<td align="middle"><asp:textbox id="txtFourthparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtFourth" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtFourthvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>5.</b></td>

<td align="middle"><asp:textbox id="txtFifthparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtFifth" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtFifthvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>6.</b></td>

<td align="middle"><asp:textbox id="txtSixthparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtSixth" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtSixthvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>7.</b></td>

<td align="middle"><asp:textbox id="txtSeventhparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtSeventh" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtSeventhvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>8.</b></td>

<td align="middle"><asp:textbox id="txtEighthparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtEighth" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtEighthvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>9.</b></td>

<td align="middle"><asp:textbox id="txtNinthparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtNinth" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtNinthvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>10.</b></td>

<td align="middle"><asp:textbox id="txtTenthparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtTenth" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtTenthvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>11.</b></td>

<td align="middle"><asp:textbox id="txtEleventhparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtEleventh" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtEleventhvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>12.</b></td>

<td align="middle"><asp:textbox id="txtTwelfthparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtTwelfth" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtTwelfthvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>13.</b></td>

<td align="middle"><asp:textbox id="txtThirteenthparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtThirteenth" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtThirteenthvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>14.</b></td>

<td align="middle"><asp:textbox id="txtFourteenthparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtFourteenth" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtFourteenthvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>15.</b></td>

<td align="middle"><asp:textbox id="txtFifteenthparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtFifteenth" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtFifteenthvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td align="middle"><b>16.</b></td>

<td align="middle"><asp:textbox id="txtSixteenthparty" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtSixteenth" runat="server" ></asp:textbox></td>

<td align="middle"><asp:textbox id="txtSixteenthvotes" runat="server" ></asp:textbox></td>

</tr>

<tr>

<td colspan="4">&nbsp;</td>

</tr>

<tr>

<td colspan="2">

&nbsp;Turnout:

<asp:textbox id="txtTurnout" runat="server" ></asp:textbox> %</td>

<td colspan="2">

&nbsp;Swing:

<asp:textbox id="txtSwing" runat="server" ></asp:textbox>

% to

<asp:DropDownList id="txtSwingtoparty" runat="server" size="1">

<asp:listitem></asp:listitem>

<asp:listitem>Conservative</asp:listitem>

<asp:listitem>Liberal Democrats</asp:listitem>

<asp:listitem>Labour</asp:listitem>

<asp:listitem>UUP</asp:listitem>

<asp:listitem>SNP</asp:listitem>

<asp:listitem>DUP</asp:listitem>

<asp:listitem>Sinn Fein</asp:listitem>

<asp:listitem>Plaid Cymru</asp:listitem>

<asp:listitem>SDLP</asp:listitem>

<asp:listitem>Independent</asp:listitem>

<asp:listitem>Other</asp:listitem>

</asp:DropDownList>

</td>

</tr>

<tr>

<td colspan="4">

<span id="lblNotesspan">&nbsp;Notes: </span>

<asp:textbox id="txtNotes" runat="server" textMode="MultiLine" wrap="True" ></asp:textbox>

</td>

</tr>

<tr>

<td align="middle" colspan="4">

<br>

Winning party:

<asp:DropDownList id="txtWinningparty" runat="server" size="1">

<asp:listitem>Labour</asp:listitem>

<asp:listitem>Conservative</asp:listitem>

<asp:listitem>Liberal Democrats</asp:listitem>

<asp:listitem>UUP</asp:listitem>

<asp:listitem>SNP</asp:listitem>

<asp:listitem>DUP</asp:listitem>

<asp:listitem>Sinn Fein</asp:listitem>

<asp:listitem>Plaid Cymru</asp:listitem>

<asp:listitem>SDLP</asp:listitem>

<asp:listitem>Independent</asp:listitem>

<asp:listitem>Other</asp:listitem>

</asp:DropDownList>

<p>

Result:

<asp:DropDownList id="txtResult" runat="server" size="1">

<asp:listitem>No change</asp:listitem>

<asp:listitem>Con gain</asp:listitem>

<asp:listitem>Lib Dem gain</asp:listitem>

<asp:listitem>Lab gain</asp:listitem>

<asp:listitem>UUP gain</asp:listitem>

<asp:listitem>SNP gain</asp:listitem>

<asp:listitem>DUP gain</asp:listitem>

<asp:listitem>Sinn Fein gain</asp:listitem>

<asp:listitem>Plaid Cymru gain</asp:listitem>

<asp:listitem>SDLP gain</asp:listitem>

<asp:listitem>Independent gain</asp:listitem>

<asp:listitem>Other gain</asp:listitem>

</asp:DropDownList>

<br>&nbsp;

</td>

</tr>

</table><P>

<asp:button id="btnSend" onclick="onBtnSendClicked" runat="server" forecolor="White" backcolor="Navy" text="Add record"></asp:button>

<asp:button id="btnClear" onclick="onBtnClearClicked" runat="server" forecolor="White" backcolor="Navy" text="Clear"></asp:button>

</center>

<p>

</form>

</body>

</html>

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