Build a Poll System in ASP.NET

Introduction

There are many websites that have polls on their front page where users (anonymous and members) give their opinions. You should also have noticed that although you are an anonymous user, you are not allowed to vote more than once. In this article, we will look at how to design a polling system, having a back-end to insert new polls along with their answers as well as how to process and store the votes of the users.

We will be using stored procedures extensively for interacting with MS SQL 2005 tables. You can download complete source code for this project here

Building blocks

There are many approaches to problem solving. One of them is the bottom-up approach. That is, we will begin to design the tables, write down the stored procedures that will interact with the tables and finally the remaining UI logic.

Functionalities

The functionalities that are to be implemented are:
  1) Put up an active poll:
      a. Enter the poll question
      b. Enter the poll answers / options

2) Displaying the current active poll.

3) Accept a vote from the user and recording the vote.

Table schema

We shall add a database (.mdf file) directly to our solution for simplicity. So, open Visual Web Developer 2005 Express edition and create a new website and call it "Polls". To add the SQL database file, right click on the project and click on "Add New Item". Then select "SQL Database" and name it "Poll.mdf".

Figure 1. Step to add a new Database File

Figure 2. Selecting a database file and naming it "Poll.mdf"

Next comes the definition of the tables as shown in the diagrams below.

Figure 3. Table "Polls" with "PK_PollId" as primary key

Figure 4. Table "PollOptions" with "PK_OptionId" as primary key and "FK_PollId" as a foreign key referencing the primary key "Pk_PollId" in the "Polls" table.

Figure 5. Table schema representing the tables' definitions. Pay attention to the primary and foreign key.

Stored Procedures

Stored procedures will be used to implement the functionalities discussed earlier. Right click on the Stored Procedures subfolder and select "Add New Stored Procedure".

1) Putting up an active poll

The algorithm to put an active poll is to first set all the polls as inactive and then insert the active one in the Polls table, as shown in the stored procedure below.

ALTER PROCEDURE dbo.NewPoll
       (
       @v_Question         VARCHAR(200)
       )
AS

--     FINDING THE ID OF THE NEW QUESTION

DECLARE @i_NextQuestionID INT

--     INITIALISIG QUESTION ID
       SET @i_NextQuestionID = 1

--     IF THERE ARE MORE POLLS, THEN SET THE QUESTION ID TO MAX ID + 1
       IF ((SELECT COUNT(*) FROM Polls) > 0)
       BEGIN
             SET @i_NextQuestionID = (SELECT MAX(PK_PollId) + 1 FROM Polls)
       END

--     FIRST, SET THE OTHER QUESTIONS AS INAVTIVE
       UPDATE Polls
       SET           Active = 0

--     INSERT THE NEW QUESTION IN THE TABLE POLLS
       INSERT INTO Polls
                    (PK_PollId, Question, Active)
       VALUES (@i_NextQuestionID, @v_Question, 1)

       RETURN

The input parameter @v_Question is the text describing the question of the poll.

Secondly, the options for the poll need to be linked with the question itself. This is done by the following store procedure, using the id of the active poll.

ALTER PROCEDURE dbo.SetPollOption
       (
       @v_option    VARCHAR(100)
       )
AS
--     FINDING THE ID OF THE NEW QUESTION
       DECLARE @i_NextPollOptionID       INT,
             @i_PollId                 INT

--     GETS THE ACTIVE POLL ID
       SELECT @i_PollId = PK_PollId FROM Polls WHERE Active = 1

--     INITIALISIG QUESTION ID
       SET @i_NextPollOptionID = 1

--     IF THERE ARE MORE POLLS, THEN SET THE QUESTION ID TO MAX ID + 1
       IF ((SELECT COUNT(*) FROM PollOptions) > 0)
       BEGIN
             SET @i_NextPollOptionID = (SELECT MAX(PK_OptionId) + 1 FROM PollOptions)
       END

--     INSERT THE NEW QUESTION IN THE TABLE POLLS
       INSERT INTO PollOptions
                    (PK_OptionId, FK_PollId, Answer, Votes)
       VALUES (@i_NextPollOptionID, @i_PollId, @v_option, 0)

       RETURN

@v_option holds description of the option

2) Displaying the current active poll.

The following stored procedure will retrieve the current active poll as well as the options linked with the poll. The stored procedure will return a dataset containing 2 tables, based on the 2 SELECT statement, the first containing the question and the second table containing the options.

ALTER PROCEDURE dbo.GetActivePoll
AS
--     GETS THE ACTIVE QUESTION FROM THE POLL TABLE
       SELECT PK_PollId, Question
       FROM   Polls
       WHERE Active = 1

--     GETS THE OPTIONS LINKED WITH THE ACTIVE POLL
       SELECT PK_OptionId, Answer, Votes
       FROM   PollOptions
       WHERE Fk_PollId IN
                    (SELECT PK_PollID FROM Polls WHERE Active = 1)

RETURN

3) Recording a vote

Recording a vote is simple. The only thing that has to be done is to increment the number of votes for the option that was selected. The stored procedure below illustrates how this is done.

ALTER PROCEDURE dbo.IncrementVote
       (
       @i_OptionId INT
       )

AS
--     GETS THE CURRENT NUMBER OF VOTES FOR THAT OPTION
       DECLARE      @i_NumberOfVoteS INT

       SELECT @i_NumberOfVotes = Votes
       FROM   PollOptions
       WHERE PK_OptionId = @i_OptionId

       UPDATE PollOptions
       SET           Votes = (@i_NumberOfVotes + 1)
       WHERE PK_OptionId = @i_OptionId

       RETURN

The stored procedure takes as parameter the option of the poll that was selected. The logic of preventing more than 1 time vote per poll will be explained later in this article.

Designing the User Interface and Implementation

Below is the screen design where the poll would be input. The various options will be separated by the enter key and processed accordingly.

Figure 5. Screen for inputting a new poll. Note that the options must be separated by key

Clicking on the "Update Poll" button will set the poll as the active one with all the options. The following code illustrates the idea.

Protected Sub btnUpdatePoll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdatePoll.Click
        Try
            '   Adds the question
            addPollQuestion()

            '   adds the options
            Dim strOptions() As String = txtOptions.Text.Split(vbCrLf)

            For Each strOption As String In strOptions
                addPollOptions(strOption)
            Next
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

Firstly, the question is added to the table and then the options, in that order.

Private Sub addPollQuestion()
        Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
        Dim sqlConn As New SqlConnection(strConnString)

        sqlConn.Open()
        Dim sqlCmd As New SqlCommand()

        sqlCmd.CommandText = "NewPoll"
        sqlCmd.CommandType = Data.CommandType.StoredProcedure
        sqlCmd.Connection = sqlConn

        '   Creation parameters
        Dim sqlParamQuestion As New SqlParameter("@v_Question", Data.SqlDbType.VarChar)

        sqlParamQuestion.Value = txtQuestion.Text

        sqlCmd.Parameters.Add(sqlParamQuestion)

        '   Execute stored procedure
        sqlCmd.ExecuteNonQuery()

        '   Close connection
        sqlConn.Close()
    End Sub

Finally the function addPollOptions is called to add one by one the options to the table.

Private Sub addPollOptions(ByVal strOption As String)
        Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
        Dim sqlConn As New SqlConnection(strConnString)

        sqlConn.Open()
        Dim sqlCmd As New SqlCommand()

        sqlCmd.CommandText = "SetPollOption"
        sqlCmd.CommandType = Data.CommandType.StoredProcedure
        sqlCmd.Connection = sqlConn

        '   Creation parameters
        Dim sqlParamOption As New SqlParameter("@v_option", Data.SqlDbType.VarChar)

        sqlParamOption.Value = strOption

        sqlCmd.Parameters.Add(sqlParamOption)

        '   Execute stored procedure
        sqlCmd.ExecuteNonQuery()

        '   Close connection
        sqlConn.Close()
    End Sub

The screen below is the voting screen that allows the user to select an option for the poll and click on the vote button.

Figure 6. Screen showing how the options will look like for each poll. A button is also supplied for voting.

To display the poll, a label and a radiobuttonlist are used. The label"s text is bounded with the question and the radiobuttonlist bounded to the options, both obtained from the stored procedure "GetActivePoll".

The following code snippets do the job.
First, the active poll is retrieved from the table and stored in a dataset.

Private Function getActivePoll() As DataSet
        Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
        Dim sqlConn As New SqlConnection(strConnString)

        '   Opens the connection
        sqlConn.Open()
        Dim sqlCmd As New SqlCommand()

        sqlCmd.CommandText = "GetActivePoll"
        sqlCmd.CommandType = Data.CommandType.StoredProcedure
        sqlCmd.Connection = sqlConn

        '   Gets the dataset from the sp
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter(sqlCmd)

        '   Execute stored procedure
        da.Fill(ds)

        '   Close connection
        sqlConn.Close()

        Return ds
End Function

Next, the poll is bounded with the label and the radiobuttonlist.

Private Sub DisplayPoll()
        Try
            Dim ds As DataSet = getActivePoll()

            '   Displays the poll
            lblPollQuestion.Text = ds.Tables(0).Rows(0)("Question")

            Dim i As Integer = 0
            For Each dr As DataRow In ds.Tables(1).Rows
                rdoPollOptionList.Items.Add(dr("Answer"))
                rdoPollOptionList.Items(i).Value = dr("PK_OptionId")
                rdoPollOptionList.SelectedIndex = 0

                i = i + 1
            Next
        Catch ex As Exception
            Throw ex
End Try
End Sub

When the user clicks on the vote button, his vote will be either considered or rejected based on whether he already voted previously. We will make use of cookies to store whether he already voted or not. The property "expires" holds how long the cookie will be stored on hard disk before being expired.

Protected Sub btnVote_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVote.Click
        If Response.Cookies("Voted") Is Nothing Then
            Response.Cookies("Voted").Value = "Voted"
            Response.Cookies("Voted").Expires = DateTime.Now.AddDays(1)

            lblError.Visible = False

            '   Checks if the user can still vote by using cookie
            RecordVote()
        Else
            lblError.Visible = True
        End If
End Sub

If the vote is considered as a first time vote, the cookie is updated and the number of votes for that option incremented as illustrated by the function below. The id of the option is passed in the stored procedure and is obtained from rdoPollOptionList.SelectedValue.

Private Sub RecordVote()
        Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
        Dim sqlConn As New SqlConnection(strConnString)

        sqlConn.Open()
        Dim sqlCmd As New SqlCommand()

        sqlCmd.CommandText = "IncrementVote"
        sqlCmd.CommandType = Data.CommandType.StoredProcedure
        sqlCmd.Connection = sqlConn

        '   Creation parameters
        Dim sqlParamQuestion As New SqlParameter("@i_OptionId", Data.SqlDbType.Int)

        sqlParamQuestion.Value = rdoPollOptionList.SelectedValue

        sqlCmd.Parameters.Add(sqlParamQuestion)

        '   Execute stored procedure
        sqlCmd.ExecuteNonQuery()

        '   Close connection
        sqlConn.Close()
End Sub

Summary

In short, this tutorial explained about using stored procedures, cookies and binding data to various controls.

Extensions to the current application

Based on this example, many things can be achieved: live polls on websites, questionnaires etc... It is up to you to let your imagination flow.

Tutorial toolbar: Tell A Friend | Add to favorites | Feedback