The value '' cannot be converted to a number ERROR

[TheScarecrow]

I am trying to greate a poll for my website.  I am getting this error when they user trys to vote and I dont understand why.  The error appears to be on the processing page when trying to add votes.  I get the total votes from the database then add 1 and then update the database.  THat is when I get there error.  can anyone help? Thanks.

CODE:

<!---BEGIN COLDFUSION CODE FOR POLL--->

<CFAPPLICATION NAME="Cookies"

sessionManagement = "Yes"

setDomainCookies = "Yes"

setClientCookies = "Yes">

<!---GET CURRENT POLL ID--->

<cfquery datasource="xxxxx" name="currentpollID" >

          SELECT *

    FROM OAREI_poll

    WHERE start_date <= <cfqueryparam cfsqltype="cf_sql_date"

value=#Now()#> and end_date >= <cfqueryparam cfsqltype="cf_sql_date"

value=#Now()#>

</cfquery>

<cfset SESSION.display = #currentpollID.poll_ID#>

<!---END CURRENT POLL ID--->

<!---TEST TO SEE IF COOKIE EXISTS--->

<CFIF Not IsDefined ("cookie.OAREIpoll")>

          <CFSET cookie.OAREIpoll = 0>

</CFIF>

<!---END COOKIE TEST--->

<!---TEST COOKIE VALUE TO DETERMINE DISPLAY POLL OR RESULTS--->

<CFIF #cookie.OAREIpoll# EQ 0>

    <cfquery datasource="xxxxx" name="Questions">

                    SELECT OAREI_question.question_ID, question_text, answer_ID, answer_text, OAREI_answers.question_ID, OAREI_answers.votes

              FROM OAREI_question, OAREI_answers

              WHERE OAREI_question.question_ID = #SESSION.display# AND OAREI_answers.question_ID = #SESSION.display#

              ORDER BY OAREI_answers.answer_ID

          </cfquery>

<cfelse>

    <cfif #cookie.OAREIpoll# EQ #currentpollID.poll_ID#>

              <cfquery datasource="xxxxx" name="Totals">

                  SELECT OAREI_question.question_text, SUM(OAREI_answers.votes) AS TotalVotes

                              FROM OAREI_question INNER JOIN OAREI_answers

                              ON OAREI_question.question_ID=OAREI_answers.question_ID

                              WHERE OAREI_question.question_ID=#cookie.OAREIpoll#

                              GROUP BY OAREI_question.question_text

        </cfquery>

        <cfquery datasource="xxxx" name="Results">

                              SELECT OAREI_answers.answer_text, OAREI_answers.votes

                              FROM OAREI_answers

                              WHERE OAREI_answers.question_ID = #Cookie.OAREIpoll#

                              ORDER BY OAREI_answers.answer_ID

                    </cfquery>

    </cfif>

</cfif>

The processing page form

<!---Begin RESULTS COLDFUSION--->

<cfif isDefined ('Form.QuestionID')>

          <cfif IsDefined ('Form.AnswerID')>

              <cfif IsDefined ("Cookie.OAREIpoll")>

                  <cfif #Cookie.OAREIpoll# EQ #SESSION.display#>

                      <br>

                <div align="center">Sorry, You can only vote once.</div>

                <cfabort>

            </cfif>

        </cfif>

        <cftransaction>

        <cfquery datasource="xxxxx" name="getVotes">

                  SELECT votes

            FROM OAREI_answers

            WHERE OAREI_answers.question_ID = #Form.QuestionID# and OAREI_answers.answer_ID = #Form.AnswerID#

        </cfquery>

        <cfset NewVotes = trim(getVotes.votes) + 1>

        <cfquery datasource="xxxx" name="NewVote">

                  UPDATE OAREI_answers

            SET votes = #NewVotes#

           WHERE OAREI_answers.question_ID = #Form.QuestionID# and OAREI_answers.answer_ID = #Form.AnswerID#

        </cfquery>

        </cftransaction>

        <!---SET COOKIE AND VALUE REMOVE--->

              <cfcookie name="OAREIpoll" value="#SESSION.display#" Expires="NEVER">

        <cfquery datasource="xxxxxx" name="showanswer">

                  SELECT *

            FROM OAREI_answers

            WHERE answer_ID = #Form.AnswerID#

        </cfquery>

        <cfquery datasource="xxxxx" name="showquestion">

                  SELECT *

            FROM OAREI_question

            WHERE question_ID = #Form.QuestionID#

        </cfquery>

        <cflocation url="poll.cfm">

    <cfelse>

              <br>

        <div align="center">Sorry, you didnt select anything. Please select an answer before hitting the submit button.</div>

          </cfif>

</cfif>

<cfif IsDefined ("URL.QuestionID")>

          <cfquery datasource="xxxxx" name="check">

              SELECT SUM(votes) AS AllVotes

        FROM OAREI_answers

        WHERE question_ID = #URL.question_ID#

    </cfquery>

    <cfif check.AllVotes is "0">

              <br>

        <div align="center">There are currently no votes for this poll. Vote now to be the first.</div>

    </cfif>

</cfif>

[BKBK]

I see Application.cfm code there as well. Could you please distinguish between the Application code and the processing page? You should also give us an indication of the line number, or at least the section of code, where the error occurs.

[BKBK]

General suggestions:

1) Put more information into the cfapplication tag, for example

<CFAPPLICATION NAME="Cookies"

applicationtimeout="#createTimeSpan(1,0,0,0)#"

sessiontimeout="#createTimeSpan(0,0,20,0)#"

sessionManagement = "Yes"

setDomainCookies = "Yes"

setClientCookies = "Yes">

2) There are quite a number of variables that are expected to be numeric. For example,

session.display

Form.QuestionID

Form.AnswerID

cookie.OAREIpoll

URL.question_ID

Add code similar to the following to test each one before use

<cfif isNumeric(Form.QuestionID) AND isNumeric(Form.AnswerID)>

<!--- business code such as query --->

<cfelse>

Form.QuestionID or Form.AnswerID is not a number.

</cfif>

[Dan_Bracuk]

You posted more code than I'm willing to read, but I did read this:  " I get the total votes from the database then add 1 and then update the database".

That seems very unnecessary.  If you are adding records somewhere for each vote, why store the total when you can select it with a query whenever you want?

[Steve Sommers]

The simple answer is that Form.QuestionID and/or Form.AnswerID is not the value you are expecting -- they are blank or not present.

The long answer is that there is more wrong with your code than a simple missing value. Your query is ripe for SQL injection. If someone with ill intents were to pass "QuestionID=0 delete from OAREI_answers" as a URL parameter, or better yet "QuestionID=0 drop table OAREI_answers", I'm sure you'll be wondering where your table went.

BKBK suggested some fixes but didn't really emphasize the dangers of your current code. You'll want to examine all your queries. Good luck.

[BKBK]

Steve Sommers wrote:

BKBK suggested some fixes but didn't really emphasize the dangers of your current code. You'll want to examine all your queries.

Did you see this:

Add code similar to the following to test each one before use

<cfif isNumeric(Form.QuestionID) AND isNumeric(Form.AnswerID)>

<!--- business code such as query --->

<cfelse>

Form.QuestionID or Form.AnswerID is not a number.

</cfif>

Which other kind of emphasis did you expect? Me bashing the guy with a mallet?

[Steve Sommers]

You don't need to bash him, that is why I'm here ;-), but you simply gave a suggestion on fixing a serious flaw yet didn't really explain what you were fixing -- handing him a fish vs. teaching him to fish. There is a high probability that there are several queries like this in the application and knowing why is just as important as to how. That's all. Now where do I find the 8 pound mallet?

[BKBK]

Steve Sommers wrote:

-- handing him a fish vs. teaching him to fish.

There are occasions that decide a man's hunger to have priority. In such cases I usually choose to deny myself the condescension or self-indulgence to teach him to hunt. I consider the issue 'The value '' cannot be converted to a number ERROR' to have such priority. Let him learn to fish on a full stomach.