Simple WHERE clause in CFQUERY Has error executingdatabase query

[Johnny Jevidetti]

Hello,
I'm sort of new to CF, but have been doing this enough that this is very
frustrating! I'm using the standalone version of ColdFusion 6.1 on my Windows
XP professional computer and the Microsoft IIS web server.
I have a simple query:

<CFQUERY NAME="clientCheck" DATASOURCE="#DataSource#">
SELECT Company, Custno
FROM Customer
WHERE Company=#FORM.UserName#
</CFQUERY>

Basically, I'm working on developing an online account application form where
the visitor would enter some simple information in a form such as password and
user name. These values would be inserted into another table if the value
entered in the UserName field already exists in the Company field in the
Customer table (Foxpro table - ODBC Socket datasource) Which is what this query
is supposed to do - find the record where FORM.UserName is equal to the value
in the Company field. There should be one record returned, or zero records if
the value entered isn't in the table.

Instead I get this stupid error:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Visual FoxPro
Driver]Command contains unrecognized phrase/keyword.

The error occurred in
C:\Inetpub\wwwroot\WorkReportSiteII\SelectiveRepor ts.cfm: line 10

8 : SELECT Company, Custno
9 : FROM Customer
10 : WHERE Company=#FORM.UserName#
11 : </CFQUERY>
12 : <CFIF clientCheck.RecordCount EQ 0>

I've used WHERE clauses in other queries, using two tables and have no
problem. They also work with all the code examples in Ben Forta's Web
Application Construction Kit Book. But why won't it work when I try to do what
is listed above? Would improper use of session variables wreck havoc with a
CFQUERY? I wouldn't think so. I'm using session vars in this overall project
and they do seem to work like they should - but I most likely have much more
learning to do.

[Dan Bracuk]

My guess, company is a char field and you don't have quotes in your query.


Originally posted by: Johnny Jevidetti
Hello,
I'm sort of new to CF, but have been doing this enough that this is very
frustrating! I'm using the standalone version of ColdFusion 6.1 on my Windows
XP professional computer and the Microsoft IIS web server.
I have a simple query:

<CFQUERY NAME="clientCheck" DATASOURCE="#DataSource#">
SELECT Company, Custno
FROM Customer
WHERE Company=#FORM.UserName#
</CFQUERY>

Basically, I'm working on developing an online account application form where
the visitor would enter some simple information in a form such as password and
user name. These values would be inserted into another table if the value
entered in the UserName field already exists in the Company field in the
Customer table (Foxpro table - ODBC Socket datasource) Which is what this query
is supposed to do - find the record where FORM.UserName is equal to the value
in the Company field. There should be one record returned, or zero records if
the value entered isn't in the table.

Instead I get this stupid error:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Visual FoxPro
Driver]Command contains unrecognized phrase/keyword.

The error occurred in
C:\Inetpub\wwwroot\WorkReportSiteII\SelectiveRepor ts.cfm: line 10

8 : SELECT Company, Custno
9 : FROM Customer
10 : WHERE Company=#FORM.UserName#
11 : </CFQUERY>
12 : <CFIF clientCheck.RecordCount EQ 0>

I've used WHERE clauses in other queries, using two tables and have no
problem. They also work with all the code examples in Ben Forta's Web
Application Construction Kit Book. But why won't it work when I try to do what
is listed above? Would improper use of session variables wreck havoc with a
CFQUERY? I wouldn't think so. I'm using session vars in this overall project
and they do seem to work like they should - but I most likely have much more
learning to do.

[mxstu]

> WHERE Company=#FORM.UserName#

Is username a text value like 'jsmith'? If so, try putting single quotes around the form field value:

WHERE Company = '#FORM.UserName#'

[Johnny Jevidetti]

When I put quotes around company, which is a field in the table, I get the same error shown in my original post.

[mxstu]

Did you use single quotes? Also, if you turn on full debugging, what is the actual sql statement sent to the database? (ie. with the value of #FORM.UserName#

[Johnny Jevidetti]

Putting the quotes around #FORM.UserName# like --> "#FORM.UserName#" Seems to
have gotten me somewhere, but I can't figure out why since when I've been
getting the error message indicated in the original post, the value contained
in FORM.UserName is put down in place for the SQL statement in the lower part
of the "Error Occurred While Processing Request" screen. But it works! Now I
have to figure out why the INSERT CFQUERY isn't working...Turns out to be the
same deal --> putting "" around the # delimited variable names. Thanks!!

[Johnny Jevidetti]

I used double quotes which seems to be the standard in Ben Forta's "Web
Application Construction Kit" and now the problem seems to be solved. Finding
the user name and then inserting the username and password data into the
password file now all works! Thanks!

[Dan Bracuk]

Quoting is keyboard freindly because when you tab out of the textarea, the
reply button is in focus. Quick replies are keyboard unfreindly because when
you tab out of the textarea, the reply button is not even close to being in
focus. Guidelines are words. The inteface is action. Action speaks louder
than words.

Originally posted by: mxstu
"When posting messages...
Avoid excessive quoting of prior messages ** Use a descriptive title for your
posts ** Include the necessary information ** Don't cross-post or double-post"
Macromedia Online Forums Posting Guidelines

[mxstu]

Dan Baruk,

Sounds a little like laziness ;-) It is very suprising that you're not aware
of the reasons for such guidelines (unless you are new and haven't spent much
time in usenet groups). The reasons are basic common sense and simple
courtesy. Re-quoting every post is not only uneccessary but essentially
doubles the size of every thread. This generates uneccessary traffic every
time a page is viewed, and it makes threads more difficult for other people to
read. It also increases the amount of time it takes for each thread page to
load. Overall, it seems a bit inconsiderate. Especially for dial up-users.
Although, I'm sure they really appreciate it the extra time it takes to view a
thread ;-)