Access Database Update

I have a poll script on my site. When someone votes I am trying to add one to
the existing number of votes in the poll table for whatever answer they
choose. The number is not writing to the database. I don't get any errors,
the value just does not get written to the database for some reason. I hope
someone can help. Here's my code.

set objpoll=server.CreateObject("adodb.recordset")
objpoll.open "select * from tblpoll where id=#"&request.form("weekending")
&"#",getcon,3,3

objpoll.movefirst

If request.form("Answsers") = "Vote1" then
objpoll("Votes1") = objpoll("Votes1") + 1
end if
If request.form("Answsers") = "Vote2" then
objpoll("Votes2") = objpoll("Votes2") + 1
end if
If request.form("Answsers") = "Vote3" then
objpoll("Votes3") = objpoll("Votes3") + 1
end if
If request.form("Answsers") = "Vote4" then
objpoll("Votes4") = objpoll("Votes4") + 1
response.write(objpoll("votes4"))
end if
objpoll.update
objpoll.movelast
%>

Re: Access Database Update

Well, there's a more efficient method which you could be using

Dim strSQL = "UPDATE tblPoll SET Votes"

If request.form("Answsers") = "Vote1" then
strSQL = strSQL & "1 = Votes1 + 1 "
end if
If request.form("Answsers") = "Vote2" then
strSQL = strSQL & "2 = Votes2 + 1 "
end if
If request.form("Answsers") = "Vote3" then
strSQL = strSQL & "3 = Votes3 + 1 "
end if
If request.form("Answsers") = "Vote4" then
strSQL = strSQL & "4 = Votes4+ 1 "
end if

' (actually this could be even better with a select..case statement)

strSQL = strSQL & " WHERE id = " & request.form("weekending")

Response.Write strSQL ' this line is fairly useful
' open a connection, use Execute
set objConn = Server.CreateObject("ADODB.Connection")
objConn.open(connstring)
objConn.execute strSQL
objConn.Close()
set objConn = nothing


see if that throws an error.

--
Jason Brown
Microsoft GTSC, IIS

This posting is provided "AS IS" with no warranties, and confers no
rights.


"Erica" wrote in message
news:0BBDDC04-8C67-45A5-A592-F31924C17127@microsoft.com...
>I have a poll script on my site. When someone votes I am trying to add one
>to
> the existing number of votes in the poll table for whatever answer they
> choose. The number is not writing to the database. I don't get any errors,
> the value just does not get written to the database for some reason. I
> hope
> someone can help. Here's my code.
>
> set objpoll=server.CreateObject("adodb.recordset")
> objpoll.open "select * from tblpoll where id=#"&request.form("weekending")
> &"#",getcon,3,3
>
> objpoll.movefirst
>
> If request.form("Answsers") = "Vote1" then
> objpoll("Votes1") = objpoll("Votes1") + 1
> end if
> If request.form("Answsers") = "Vote2" then
> objpoll("Votes2") = objpoll("Votes2") + 1
> end if
> If request.form("Answsers") = "Vote3" then
> objpoll("Votes3") = objpoll("Votes3") + 1
> end if
> If request.form("Answsers") = "Vote4" then
> objpoll("Votes4") = objpoll("Votes4") + 1
> response.write(objpoll("votes4"))
> end if
> objpoll.update
> objpoll.movelast
> %>