New to CF. Trouble w/ parameters.

itsecsol · Dec 31, 2001

I am etting this message:

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'question_id ='.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (10:1) to (10:61).

Date/Time: 12/31/01 12:53:21
Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows 98)
Remote Address: 127.0.0.1
HTTP Referer:

http://127.0.0.1//sybex/quiz_results.cfm

Query String: question_id=

dallasweb · Dec 31, 2001

Post the code from that .cfm page.

DeZiner · Dec 31, 2001

expression 'question_id ='. Get rid of the first single quote prior to question DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

pkinetics · Dec 31, 2001

Are you passing a value? Do you have a value? Output the variable before you run the query and see if you have a value.

itsecsol · Jan 2, 2002

There is a database called quiz.mdb with a table called questions with the fields:
question_id
question
correct_answer
answer1
answer2
answer3
answer4

This is the code from the home.cfm file

<!DOCTYPE html public "-//W3C//DTD HTML 4.0 Transitional//EN">

<cfif not isdefined("question_id&quot

>
<cfquery name="get_count" datasource="quiz" dbtype="odbc">
select count(question_id) as total_questions
from questions
</cfquery>
</cfif>

<CFQUERY NAME="get_question" DATASOURCE="quiz" DBTYPE="odbc">
SELECT question_id,
question,
answer1,
answer2,
answer3,
answer4
FROM questions
WHERE question_id = <CFIF ISDEFINED("question_id&quot

>
#question_id#
<CFELSE>
#RandRange(1, get_count.total_questions)#
</CFIF>
</CFQUERY>

<HTML>
<HEAD>
<TITLE>My Home Page</TITLE>
</HEAD>
<BODY bgcolor="#ffffff">
<DIV align="center">
<HR><B>Welcome to My Home Page!</B>
<HR><B>Today's date is:</B><BR>

<CFOUTPUT>
CFSET today = DateFormat(Now(), "dddd, mmmm d, yyyy&quot

><I>#today#</I></CFOUTPUT>
</DIV> <CFOUTPUT query="get_question">
<FORM action="quiz_results.cfm" method="post">
<P>
<HR><B>Your Question is:</B><BR><I>#question#</I><P>
<INPUT type="hidden" name="today" value="#today#">
<INPUT type="hidden" name="question_id" value="#question_id#">

<INPUT type="radio" name="user_answer" value="#answer1#">#answer1#<BR>
<INPUT type="radio" name="user_answer" value="#answer2#">#answer2#<BR>
<INPUT type="radio" name="user_answer" value="#answer3#">#answer3#<BR>
<INPUT type="radio" name="user_answer" value="#answer4#">#answer4#<BR>

<P><INPUT type="submit" value="Score Question!">
<INPUT TYPE="Button" value="Get New Question!"
ONCLICK="location.href='home.cfm'">
</FORM>
</CFOUTPUT>
</BODY>
</HTML>

this is from the quiz_results.cfm file

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
<title>Quiz Results</title>
</head>

<body bgcolor="#ffffff">

<cfif NOT IsDefined("form.user_answer&quot

OR
NOT IsDefined("form.question_id&quot

OR
NOT IsDefined("form.today&quot

>
<div align="center">
<hr><b>Welcome!</b><hr>
<i><a href="home.cfm">Please take my quiz!</a></i>
</div>
<cfabort>
</cfif>

<cfquery name="get_answer" datasource="quiz" dbtype="ODBC">
select question, correct_answer
from questions
where question_id = #form.question_id#
</cfquery>

<div align="center">
<hr><b>Quiz Results!</b>
<b>Thank you for taking my short quiz!</b><p>
</div>

<cfoutput query="get_answer">
<cfif user_answer IS correct_answer>
<b><font color="##008000">Congratulations!!! You are correct!</font></b><p>
<cfelse>
<b><font color="##ff0000">
I'm sorry, but that is the wrong answer. Please try again.</font></b><p>
</cfif>

<b>The question was:</b> #question#<br>
<cfif user_answer IS NOT correct_answer>
<b>Your answer was:</b> #form.user_answer#<br>
<b>The correct answer is:</b> #correct_answer#<br>
</cfif>
<b>You took the quiz on:</b> #form.today#<p>
</cfoutput>

<div align="center">
<i><a href="home.cfm?question_id=#question_id#">Take the quiz again?</a></i>
</div>

</body>
</html>

tleish · Jan 2, 2002

The problem could be in using the IsDefined(), because the variable could be defined but be blank. So you want to use both IsDefined() and something else like Len(). I add Trim() on the inside also because a space " " would evaluate to a length of 1, but Trim(" ") would evaluate to 0 because it removes the spaces.

You could try the following change:

<CFIF NOT IsDefined("question_id") OR NOT Len(Trim(question_id))>
<CFQUERY name="get_count" datasource="quiz" dbtype="odbc">
select count(question_id) as total_questions
from questions
</CFQUERY>
</CFIF>

<CFQUERY NAME="get_question" DATASOURCE="quiz" DBTYPE="odbc">
SELECT question_id,
question,
answer1,
answer2,
answer3,
answer4
FROM questions
WHERE question_id =
<CFIF IsDefined("question_id") AND Len(Trim(question_id))>
#question_id#
<CFELSE>
#RandRange(1, get_count.total_questions)#
</CFIF>
</CFQUERY> - tleish

itsecsol · Jan 2, 2002

tleish thank you very much!!
thank you to everyone else that responded to my question. i really appreciate the help.

itsecsol

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

New to CF. Trouble w/ parameters.

itsecsol

Programmer

dallasweb

Programmer

DeZiner

Programmer

pkinetics

Programmer

itsecsol

Programmer

tleish

Programmer

itsecsol

Programmer

Similar threads

Part and Inventory Search

Sponsor