ASP Voting - using Access DB and DSN-less connection.

**r0ach** · Jul 12th, 2000, 07:44 AM

Hi,

I wanted to post this somewhere, so I immediately thought of my favorite Forum:

I wanted to have an 'easy to maintain and update' voting thingy in ASP, without adding 3rd Party components and stuff. This is what I came up with:

First, create a table in a database, and call it 'Voting'.

use this layout:

Code:

Field       Type                  Notes
-------------------------------------------------
ID          AutoNumber            Primary Key
Question    Text
Answer1     Text
Answer2     Text
Vote1       Number
Vote2       Number
TotalVotes  Number

OK. Now, create a gif of 1x1 pixel. Any color you want. This will be used for the Graph later on. Name it vote.gif
NOTE: You can make 2 different colored ones, if you want. I suggest you keep the color in sync with the color scheme of the site, though.

Right. That was the hard part.

Now for some ASP.
You will need two ASP pages. One to display the voting, and one to add the vote to the db. (There's probably better ways, but that's how I did it. Besides, this is a simple program)

Filename: vote.asp

Code:

<%@ LANGUAGE=VBScript %>
<%Option Explicit%>
<%
Response.Buffer = True
Response.Expires = -1000
' Before we build the voting poll, we must get some info from the DB
Dim objConn
Dim dbPath, strConn, strSQL
Dim objRS
Dim Question, Result1, Result2
Dim Answer1, Answer2
Dim VoteCount, Perc1, Perc2
'Open a DSN-less DB Connection
Set objConn = Server.CreateObject("ADODB.Connection")
dbPath = Server.MapPath("votes.mdb")
strConn = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
  "DBQ=" & dbPath & ";"
objConn.Open strConn
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM voting ORDER BY id DESC"
'Ordering it by id in descending order, will give us the last
'record in the DB first.  The last record should be the newest
'entry!
objRS.Open strSQL, objConn
Question = objRS("question")
Result1 = objRS("vote1")
Result2 = objRS("vote2")
Answer1 = objRS("answer1")
Answer2 = objRS("answer2")
VoteCount = objRS("totalvotes")
On Error Resume Next
'This is because the VoteCount may contain 0 and
'you cannot devide by 0
Perc1 = 0
Perc2 = 0
Perc1 = (Result1 / VoteCount) * 100
Perc2 = (Result2 / VoteCount) * 100

' See bottom for ReduceDecimal Script
Perc1 = ReduceDecimal(Perc1, 1)
Perc2 = ReduceDecimal(Perc2, 1)

'For Perc2, you could use:
'  Perc2 = 100 - Perc1
'instead, since Perc2 is the difference

objRS.Close
objConn.Close

'Now, we have all our info.  Let's create the page
%>
<HTML>
<HEAD>
<TITLE>ASP Voting Example</TITLE>
</HEAD>
<BODY>
<TABLE width=25% align=center>
  <TR>
    <TD align=center colspan=2>
      <!-- This is just for the heading -->
      <FONT face=verdana size=2>
        <B>
          ASP Voting Example
        </B>
      </FONT>
    </TD>
  </TR>
  <TR>
    <TD align=center colspan=2>
      <!-- This is for the question -->
      <FONT face=verdana size=1>
        <%
        Response.Write Question
        ' You could use =Question, but I prefer
        ' this way
        %>
      </FONT>
    </TD>
  </TR>
    <FORM method=post action=poll.asp>
    <TD align=right width=50%>
      <!-- This is for the voting Form -->
      <FONT face=verdana size=1>
        <%
        Response.Write Answer1
        %>
        <INPUT name=vote type=radio value=1>
        <BR>
        <%
        Response.Write Answer2
        %>
        <INPUT name=vote type=radio value=2>
      </FONT>
    </TD>
    <TD align=center>
      <INPUT name=votebut type=submit value=vote>
    </TD>
    </FORM>
  </TR>
  <TR>
    <!-- here we display percentages -->
    <TD align=center>
      <FONT face=verdana size=1>
        <%
          Response.Write Perc1 & "%"
        %>
      </FONT>
    </TD>
    <TD align=center>
      <FONT face=verdana size=1>
        <%
          Response.Write Perc2 & "%"
        %>
      </FONT>
    </TD>
  </TR>
  <TR>
    <!-- here we display the graph -->
    <TD align=center valign=bottom>
      <IMG src="vote.gif" width=15 height=
      <%
        Response.write perc1
      %>
      ></IMG>
    </TD>
    <TD align=center valign=bottom>
      <IMG src="vote.gif" width=15 height=
      <%
        Response.write perc2
      %>
      ></IMG>
    </TD>
  </TR>
  <TR>
    <!-- here we display respective options -->
    <TD align=center>
      <FONT face=verdana size=1>
        <%
          Response.Write Answer1
        %>
      </FONT>
    </TD>
    <TD align=center>
      <FONT face=verdana size=1>
        <%
          Response.Write Answer2
        %>
      </FONT>
    </TD>
  </TR>
  <TR>
    <TD align=center colspan=2>
      <FONT face=verdana size=1>
        Total votes : 
        <%
          Response.Write VoteCount
        %>
      </FONT>
    </TD>
  </TR>
</TABLE>
</BODY>
</HTML>

<%
' OK. Let's say you have 20 votes; 15 for option 1 and 5 for option 2
' Perc1 would be 75 and Perc2 would be 25
' But what if you have 30 votes; 20 for 1 and 10 for 2??
' Perc1 would be 66.6666666667 and Perc2 would be 33.333333333!
'This little script will format the result.
'I'm sure there are easier ways.

Function ReduceDecimal(ByVal number, ByVal digits)
  Dim strNum, dec, tmp, tmp2

  strNum = CStr(number)
  'Find position of decimal point
  dec = InStr(1, strNum, ".")
  If dec = 0 then 'there is no decimal
    'return old value
    ReduceDecimal = number
  Else
    'get the value of the digit after the one we want last,
    'so that we can round it properly
    tmp = Mid(strNum, dec + digits + 1, 1)
    '1 to 4 stays the same
    '5 to 9 becomes 1 more
    If tmp >= 5 then
      tmp2 = Mid(strNum, 1, dec + digits - 1) & (Mid(strNum, dec + digits, 1) + 1)
    Else
      tmp2 = Mid(strNum, 1, dec + digits)
    End If
    'return new value
    ReduceDecimal = Mid(tmp2, 1, dec + digits)
End If
End Function
%>

Filename: poll.asp

Code:

<%@ Language=VBScript %>
<!-- #include file="adovbs.inc" -->
<%
Dim objConn, objRS
Dim dbPath, strConn, strSQL
Dim vCount, Vote

Set objConn = Server.CreateObject("ADODB.Connection")
dbPath = Server.MapPath("votes.mdb")
strConn = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & dbPath & ";"
objConn.Open strConn
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM voting ORDER BY id DESC"
objRS.Open strSQL, objConn, adOpenDynamic, adLockPessimistic 
vCount = objRS("totalvotes")
If Request.Form("vote") = 1 then
  Vote = objRS("vote1")
  objRS("totalvotes") = vCount + 1
  objRS("vote1") = Vote + 1
  objRS.Update 
Else
  Vote = objRS("vote2")
  objRS("totalvotes") = vCount + 1
  objRS("vote2") = Vote + 1
  objRS.Update 
End If

objRS.Close
objConn.Close

Response.Redirect "vote.asp"
%>

And that's it.
To change the question, just add a new record to the db, with a choice of 2 answers, and 0 votes for each, and 0 votecount, and you are ready to go!

All the files; vote.asp, poll.asp, vote.gif, votes.mdb, and adovbs.inc should all be in the same folder.

PS: The code here was 'ripped' out of a page I did for my work's intranet. The work's one works. But I haven't tested this one. There might be some spelling errors, or I could have left out a tag or two. Technically, it should work.

NOTE: The include file "ADOVBS.INC" can be found at ASP101.

[Edited by r0ach on 07-12-2000 at 10:17 AM]

**r0ach** · Jul 12th, 2000, 09:08 AM

Put The following in the database to test it:

Code:

Field           Value
-------------------------------------
Question        Was this easy or not?
Answer1         Sure
Answer2         No Way!
Vote1           0
Vote2           0
TotalVotes      0

Thread: ASP Voting - using Access DB and DSN-less connection.

Thread Tools

Display

Oh, I forgot....

Posting Permissions