SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Building a PASS Voting Solution – Part 6

Today we’ll try to finish up by looking at a couple of the semi-interesting parts of the code in this one page application. We’ll start with the page load event. On the first run of the page (not postback) I’m grabbing the two keys I need if they exist and calling the MyBallot.Validate method (show you that next). From there I show some various bits of text and load the choices if appropriate, and then move on to deciding what to show them based on what we know.

‘only do on first page load
If Not Page.IsPostBack Then

        ‘try to load from query string
        MyBallot.BallotID = New Guid(Request.QueryString(“BallotID”))
        MyBallot.VoterID = New Guid(Request.QueryString(“VoterID”))
    Catch ex As Exception
    End Try


    ‘load the list if all is good
    If MyBallot.EligibleToVote And MyBallot.HasVoted = False Then
    End If

    ’set this regardless
    lblBallotTitle.Text = Me.MyBallot.Title
    lblBallotDescription.Text = Me.MyBallot.Description
    lblBallotItem.Text = MyBallot.Title
    lblMin.Text = MyBallot.MinSelection.ToString
    lblMax.Text = MyBallot.MaxSelection.ToString
    lblClosing.Text = MyBallot.ClosingDate.tostring
End If

‘various cases, all mutually exclusive
pnlVote.Visible = False
If MyBallot.EligibleToVote And MyBallot.HasVoted = False Then
    pnlVote.Visible = True
ElseIf MyBallot.HasVoted = True Then
    ShowMessage(“You have already completed your vote in this ballot.”, displaymessage.IconToDisplay.InformationIcon)
ElseIf MyBallot.BallotID = Guid.Empty Then
    ShowMessage(“To vote on a ballot you must provide the ballotid in the URL – contact PASS HQ for assistance”, displaymessage.IconToDisplay.InformationIcon)
    pnlLookup.Visible = True
End If

Validate is where we call the proc GetBallotDetails, which you may recall returns both output parameters and a result set.

‘remove any old items
Me.EligibleToVote = False
Me.Title = “Unknown”
Me.Description = “”
Me.MinSelection = 1
Me.MaxSelection = 1
Me.HasVoted = False

Dim dr As SqlDataReader
Using cmd As New SqlCommand()
    With cmd
        .Connection = OpenDBConnection()
        .CommandType = CommandType.StoredProcedure
        .CommandText = “Voting.GetBallotDetails”
        .Parameters.Add(New SqlParameter(“@BallotID”, DbType.Guid))
        .Parameters(“@BallotID”).Value = ballotID
        .Parameters.Add(New SqlParameter(“@VoterID”, DbType.Guid))
        .Parameters(“@VoterID”).Value = VoterID
        .Parameters.Add(New SqlParameter(“@IsEligibleToVote”, DbType:=SqlDbType.Bit))
        .Parameters(“@IsEligibleToVote”).Direction = ParameterDirection.Output
        .Parameters.Add(New SqlParameter(“@HasVoted”, DbType:=SqlDbType.Bit))
        .Parameters(“@HasVoted”).Direction = ParameterDirection.Output

        dr = .ExecuteReader
        Do While dr.Read

            ‘just assign these each time, shouldn’t change
            Me.Title = dr!ItemTitle
            Me.MinSelection = dr!MinSelection
            Me.MaxSelection = dr!maxSelection
            Me.Description = dr!Description
            Me.ClosingDate = dr!ClosingDate

            ‘next we add the details
            Dim oDetail As New BallotDetail() With {.BallotDetailID = dr!BallotDetailID, .Title = dr!DetailTitle}


        HasVoted = cmd.Parameters(“@HasVoted”).Value
        EligibleToVote = cmd.Parameters(“@IsEligibleToVote”).Value

        dr = Nothing

    End With
End Using

Here is the code called when they finally vote. We check our local state to see if they have voted, and then we’ll check again within the proc to make sure! ExecuteProc is a helper method that handles calling a proc when it just takes parameters.

If Me.EligibleToVote = False Then
          Throw New Exception(“We have not identified you as an eligible voter for this ballot.”)
      ElseIf Me.HasVoted = True Then
          Throw New Exception(“You have already cast your vote for this ballot.”)
      End If

      ‘trim leading comma
      If SelectedDetails.Substring(0, 1) = “,” Then
          SelectedDetails = SelectedDetails.Substring(1, SelectedDetails.Length – 1)
      End If

      Dim Params(0 To 2) As SqlParameter

      Params(0) = VarcharParam(“@Details”, 8000, SelectedDetails, False)
      Params(1) = New SqlParameter(“@BallotID”, SqlDbType.UniqueIdentifier)
      Params(1).Value = BallotID
      Params(2) = New SqlParameter(“@VoterID”, SqlDbType.UniqueIdentifier)
      Params(2).Value = VoterID
      ExecuteProc(“Voting.CastVote”, Params)

      Me.HasVoted = True

There’s not much else that is interesting. It’s set up with a master page containing the logo, and the main page is a content page. I’m storing a Ballot object in session and it’s also set up to be accessible as a property of the page, making it easy to reference as a strongly typed object.

I came in right at 10 hours. Lost a little time thinking through how to handle multiple items on each ballot, couldn’t see a way to do that without adding a lot more complexity to the UI. It still needs some testing by someone other than me, and it’s a project where I think unit tests would be appropriate, lots of different cases that could be verified (eligible, not eligible, has/not voted, etc), but I think we can do most of that with UI testing that will be needed anyway.

By the time you read this I’ll have sent the bits off to HQ for install and testing. We’ll try it on a few board votes, and we may send out a test vote in the Connector too.


I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.


No comments.

Leave a Comment

Please register or log in to leave a comment.