SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ASP - Updating a Recordset Poll Database??

    Here's what i've got:
    A poll that has 5 options. Each option is a radio button:
    <input type="radio" name="pollOption" value="vote1">
    etc...through vote5.
    This all in a access db...with these fields:
    Question/Options1 - 5 / and fields for vote1 - vote5

    Problem:
    I can take the users response from the form...Now
    I just want to be able to input that into the proper field in the db.

    e.g. If they vote for "vote3" I want to find the vote3 field in the table and increment it by 1.

    Any advice?

  2. #2
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm wondering if there is an easier way, but you could pull the data from the database based on the submitted vote, then incerement it and then update your table. I'm omitting a lot, I hope you get the idea.

    Code:
    pollOption = Request.Form("pollOption")
    
    SELECT votecount FROM votetable WHERE votename = '" & pollOption & "';
    
    oldvote = CInt(rs(0))
    newvote = oldvote + 1
    
    UPDATE votetable SET votecount = '" & newvote & "' WHERE votename = '" & polloption & "';

  3. #3
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm?
    I understand most of what you wrote,
    execpt what are you referring to as "votecount"??

    Is that one field in the db?

    Right now I have 5 fiels in the db, votes1..votes2...votes3..etc.....
    Each one starts at 0 ..

  4. #4
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im not sure how your table is set up so i just made up that name.

    votecount would be the actual number that is being incremented in the database

    This was how I imagined it being:

    Code:
    votename   votecount
    vote1        2
    vote2        4
    i guess you have it set up as

    Code:
    vote1   vote2   vote3   vote4   vote5
      2       4       3       5       1
    ?

  5. #5
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes...I have it set up in the bottom example.

    Is that method ok?
    Or should I rework my db like in the top example??

  6. #6
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So I s'pose the query should look like this:

    Code:
    UPDATE votetable SET "' & pollOption & '" = "' & newvote & '"

  7. #7
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Depending on how many votes you're expecting it may be easier on teh server and the database to simply insert rows and then count them at that crucial moment of display.
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  8. #8
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First>
    In reference to earlier:
    "oldvote = CInt(rs(0))"
    rs refers to what variable??

    Second> Jeremy,
    Your method sounds straightforward...
    Basically inserting rows for each response and then adding up those rows?
    How are rows added up in SQL/ASP??

  9. #9
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    SELECT count(vote1) AS vote1count FROM tablename

    Or whatever.

    May not be the perfect way, and I'm sure others have better suggestions, but I hate the thought of having ot read from the db, increment and then update when all you "need" to do is insert and count.
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  10. #10
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by meztec
    First>
    In reference to earlier:
    "oldvote = CInt(rs(0))"
    rs refers to what variable??

    Second> Jeremy,
    Your method sounds straightforward...
    Basically inserting rows for each response and then adding up those rows?
    How are rows added up in SQL/ASP??
    rs(0) is just displaying the first (and only in this case) record. You could also use rs("votecount"). Since your table was not set up like I was thinking, this query is probably not correct and will not work.

    Jeremy's way sounds better, but you may have a huge table after a while. Each vote is going to be a new recordset. Of course, it is only text and only a few characters at that.

    Jeremy's way

    Code:
    database
    table >> votecount
    
    pollOption
    -------------
    vote1
    vote2
    vote1
    vote3
    vote3
    vote4
    vote3
    
    etc...
    
    
    pollOption = Request.Form ("pollOption")
    sql = INSERT INTO votecount VALUES ('" & pollOption & "');
    
    db connection
    
    display thank you message...

  11. #11
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    That's why I said "if you're not doing huge polls".

    Anyways, if you want to do the update method the code has already been posted.
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  12. #12
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My Code is listed below
    Im trying it through the update method rather than adding up the fields..
    But, something is off..don't know what though

    ---

    <%

    'Declare Variables
    Dim oConn 'Database connection variable
    Dim rsPoll 'Database recordset variable to hold the poll recordset
    Dim filePath 'Variable to hold the physical path of the database
    Dim pollQuestion 'Holds the Poll Question
    Dim intLoop 'Holds the number of loop sections


    'Database setup
    'Map database to physical path
    filePath = Server.MapPath("data/data.mdb")

    'Create a connection object to the database
    Set oConn = Server.CreateObject("ADODB.Connection")

    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath

    Set rsPoll = oConn.Execute("SELECT * From pollData")

    pollOption = Request("pollOption")

    oldvote = CInt(rsPoll(pollOption))
    newvote = oldvote + 1

    oConn.execute("UPDATE pollData SET '" & pollOption & "' = '" & newvote & "'")

    %>

    <html>

    <head>
    <title></title>
    </head>

    <body>


    Show poll results here....
    </body>
    </html>

  13. #13
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by meztec
    [B]My Code is listed below
    Im trying it through the update method rather than adding up the fields..
    But, something is off..don't know what though

    ---

    <%

    'Declare Variables
    Dim oConn 'Database connection variable
    Dim rsPoll 'Database recordset variable to hold the poll recordset
    Dim filePath 'Variable to hold the physical path of the database
    Dim pollQuestion 'Holds the Poll Question
    Dim intLoop 'Holds the number of loop sections


    'Database setup
    'Map database to physical path
    filePath = Server.MapPath("data/data.mdb")

    'Create a connection object to the database
    Set oConn = Server.CreateObject("ADODB.Connection")

    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath

    Set rsPoll = oConn.Execute("SELECT * From pollData")

    pollOption = Request("pollOption")

    oldvote = CInt(rsPoll(pollOption))[/b}
    newvote = oldvote + 1

    oConn.execute("UPDATE pollData SET '" & pollOption & "' = '" & newvote & "'")

    %>

    <html>

    <head>
    <title></title>
    </head>

    <body>


    Show poll results here....
    </body>
    </html>
    should be

    oldvote = CInt(rsPoll("pollOption"))

    forgot the quotes

  14. #14
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tried that earlier..Didn't work..
    Here's the error without quotes around that:

    Error Type:
    Microsoft JET Database Engine (0x80040E10)
    No value given for one or more required parameters.

    It's saying the error is on this line??
    oConn.execute("UPDATE pollData SET '" & pollOption & "' = '" & newvote & "'")

  15. #15
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm must be the syntax. It seems right..

    try to response.write the SQL statement and see what it spits out.

    it should be

    Code:
    UPDATE pollData SET vote3 = '2'
    That's with the single quotes.

  16. #16
    SitePoint Addict
    Join Date
    Sep 2000
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still no luck
    The bolded code is the problem code---
    Any ideas?

    Code:

    <%

    'Declare Variables
    Dim oConn 'Database connection variable
    Dim rsPoll 'Database recordset variable to hold the poll recordset
    Dim filePath 'Variable to hold the physical path of the database
    Dim pollQuestion 'Holds the Poll Question
    Dim intLoop 'Holds the number of loop sections


    'Database setup
    'Map database to physical path
    filePath = Server.MapPath("data/data.mdb")

    'Create a connection object to the database
    Set oConn = Server.CreateObject("ADODB.Connection")

    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath

    Set rsPoll = oConn.Execute("SELECT * From pollData")

    pollOption = Request("pollOption")

    oldvote = CInt(rsPoll(pollOption))
    newvote = oldvote + 1

    oConn.execute("UPDATE pollData SET "&pollOption&" = '" & newvote & "' WHERE ID="1")


    %>

    <html>

    <head>
    <title></title>
    </head>

    <body>


    Show poll results here....
    </body>
    </html>

  17. #17
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you response.write the sql statement?

  18. #18
    SitePoint Member
    Join Date
    Nov 2001
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Meztec,

    Try removing the single quotes round '" & newvote & "', ie make it " & newvote & ". If newvote is a number field in your database, it won't accept the quotes.


    [QUOTE]Originally posted by meztec

    Still no luck
    The bolded code is the problem code---
    Any ideas?

    Code:

    <%

    oConn.execute("UPDATE pollData SET "&pollOption&" = '" & newvote & "' WHERE ID="1")


    %>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •