The Birth of a Poll - Page 3

Beginning the Poll Code

Over the next few pages we are going to deal with coding this poll so that it actually does something that works. The code is going to get fairly long but I will attempt to keep it as brief as possible. Please remember that long code does not mean complicated code - the logic is fairly simple once you get the gist of everything that is going on.

The first thing you need to do is establish a connection to the database, like so:

dim Conn, rs_Questions, rs_Responses

set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = "dsn=polldb;database=polldb"
Conn.ConnectionTimeout = 60
Conn.CommandTimeout = 60
Conn.Open

set rs_Questions = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT TOP 1 * FROM tbl_questions " & _
                "ORDER BY PollDate DESC"
rs_Questions.open strSQL, Conn, 3, 3
'TOP 1 returns the topmost record only

'.RecordCount is a method of the ADO Recordset
'for counting the number of records in the set.
recCount = rs_Questions.RecordCount
PollID = rs_Questions("PollID")
PollQuestion = rs_Questions("PollQuestion")

Then we execute a simple SELECT query that returns all the rows in the tbl_questions table. The results of this query are stored in the rs_Questions Recordset. Finally, the records returned by the query are counted using the RecordCount method and stored in a variable named recCount (there is actually not any practical reason to do this here, but I just wanted to show you that you could).

Once we have the database connection established and ready for action, the next step is to start generating the code that will output the form to the client's browser and to begin processing the options for the Poll Question. Since we had our query sort the results by PollDate in descending order, the most recent poll will be the topmost record - which is what we want. That way, there will be no confusion as to which Poll Question to use.

Generating the Poll in HTML with the Appropriate Options

Everything is now in place for us to generate the visual interface for our polling system and to populate it with the options related to the topmost question in the rs_Questions Recordset.

So, the next step is to get a Recordset that contains all the options for our poll in question, and that means another query! This time the query and accompanying ASP looks like:

' Extract the options from the database
strSQL = "SELECT * FROM tbl_Responses " & _
	   "WHERE PollID = " & PollID
set rs_Responses = Server.CreateObject("ADODB.Recordset")
rs_Responses.Open strSQL, Conn, 3, 3

'calculate the total number of responses for the poll
'(you will see why later)
Total = 0
While Not rs_Responses.EOF
Total = Total +  rs_Responses("VoteCount")
	'stores the poll options one per row in a large
	'string of HTML content called PollOptHTML
	PollOptHTML =  PollOptHTML & _
	"<tr><td>" & rs_Responses("PollOption") & _
	"</td><td><input type=""radio""" & _
	"name=""pollopt"" value=""" & _
	rs_Responses("PollOptionID")  & """></td></tr>"
	rs_Responses.MoveNext
Wend

Updating the Database

Okay, stepping down from my mid-tutorial soapbox, let's move on to some more code. In this block we need to produce the final polling code that handles incoming votes.

strSQL = "UPDATE tbl_Responses " & _
    "SET VoteCount = VoteCount + 1 " & _
    "WHERE PollOptionID= " & _
    Request.QueryString("pollopt") & ";"

'Update the count for the PollOption
'yet another way to run a query against a database
'with ASP Conn.Execute
Conn.Execute strSQL

UPDATE table_name
SET col_name = value
{, col_name2 = value2, col_name3 = value3}
WHERE criteria ;

<form name="poll" action="poll.asp" method="get">
<table width="250">
	<tr><td colpan="2">
	<%= PollQuestion %>
	</td></tr>
	<%= PollOptHTML %>
	<tr><td colspan="2">
	<input type="submit" value="Submit Vote">
	</td></tr>
</table>
<input type="hidden" name="isSubmitted" value="yes">
</form>

Where to Start - Page 2
Poll Your Visitors with ASP
A Functional Polling System - Page 4