I get this error running the query:

Microsoft JET Database Engine error '80040e14' 

Syntax error (missing operator) in query expression 'Height = AND Weight 
= AND Age = AND Haircolor = '%%' AND Hairtype = '%%' AND Physique = '%%' 
AND Mothertongue = '%%' AND Mothertongue2 = '%%' AND Skills = '%%' AND 
Eyecolor = '%%' AND CarMake = '%%''. 

/dev/services/search4.asp, line 23


Here is the code, it's propably something very simple but my eyes
just can't catch it.


SQL = "SELECT id, Weight, Height, Haircolor, Hairtype, Hairlength, 
Physique, Eyecolor, Mothertongue, "
SQL = SQL & "Mothertongue2, CarMake, CarModel, CarColor, Vehicle, Unique, 
Skills, Comments FROM users "
SQL = SQL & "WHERE Height = " & Request.Form("txtHeight") & " AND Weight 
= " & Request.Form("txtWeight") & " "
SQL = SQL & "AND Age = " & Request.Form("txtAge") & " AND Haircolor = '%" 
& Request.Form("txtHaircolor") & "%' "
SQL = SQL & "AND Hairtype = '%" & Request.Form("txtHairtype") & "%' AND 
Physique = '%" & Request.Form("txtPhysique") & "%' "
SQL = SQL & "AND Mothertongue = '%" & Request.Form("txtMothertongue") 
& "%' AND Mothertongue2 = '%" & Request.Form("txtMothertongue2") & "%' "
SQL = SQL & "AND Skills = '%" & Request.Form("txtSkills") & "%' AND 
Eyecolor = '%" & Request.Form("txtEyecolor") & "%' "
SQL = SQL & "AND CarMake = '%" & Request.Form("txtCarMake") & "%' ORDER 
BY id"

Set rstSimple = cnnSimple.Execute(SQL)

The error message shows no values for Height and Weight.  Consider the part
of your sql statement:

...WHERE Height = " & Request.Form("txtHeight") ...

If txtHeight wasn't entered on the previous page, the sql can't process it
and just hangs.

1) for required values, use validation script on the form page to check
whether a meaningful value was entered on the page before sending the info
to the server.  You can set this to popup a message with something like
"Please enter Height", etc.  You can find numerous examples of this code on
Javascript sites.  This is good practice because it reduces traffic by
validating at the client, and your user won't have to wait for an error
message to be returned through the network.

2)  for non-required values, you can use conditionals to build your sql
string. Here's a partial example:

SQL = "SELECT id, Weight, Height, Haircolor, Hairtype, Hairlength,
Physique, Eyecolor, Mothertongue, "
SQL = SQL & "Mothertongue2, CarMake, CarModel, CarColor, Vehicle, Unique,
Skills, Comments FROM users "

if request.form("txtHeight")<>"" then
SQL = SQL & "WHERE Height = " & Request.Form("txtHeight") & "
end if

if request.form("Weight")<>"" then
SQL = SQL & "AND Weight = " & Request.Form("txtWeight") & "
end if

... etc.

This is fairly tedious, but prevents missing values from generating errors.
You don't need to test in the sql statement for the REQUIRED values if
you've tested for them on the form page.  You can put them all in a SQL 
clause before the first conditional test.

hth,
Mike

----- Original Message -----
From: "Marko Ramstedt" <marko.ramstedt@a...>
To: "ASP Database Setup" <asp_database_setup@p...>
Sent: Tuesday, August 20, 2002 11:06 PM
Subject: [asp_database_setup] Syntax error (missing operator) in query
expression


> I get this error running the query:
>
> Microsoft JET Database Engine error '80040e14'
>
> Syntax error (missing operator) in query expression 'Height = AND Weight
> = AND Age = AND Haircolor = '%%' AND Hairtype = '%%' AND Physique = '%%'
> AND Mothertongue = '%%' AND Mothertongue2 = '%%' AND Skills = '%%' AND
> Eyecolor = '%%' AND CarMake = '%%''.
>
> /dev/services/search4.asp, line 23
>
>
> Here is the code, it's propably something very simple but my eyes
> just can't catch it.
>
>
> SQL = "SELECT id, Weight, Height, Haircolor, Hairtype, Hairlength,
> Physique, Eyecolor, Mothertongue, "
> SQL = SQL & "Mothertongue2, CarMake, CarModel, CarColor, Vehicle, Unique,
> Skills, Comments FROM users "
> SQL = SQL & "WHERE Height = " & Request.Form("txtHeight") & " AND Weight
> = " & Request.Form("txtWeight") & " "
> SQL = SQL & "AND Age = " & Request.Form("txtAge") & " AND Haircolor = '%"
> & Request.Form("txtHaircolor") & "%' "
> SQL = SQL & "AND Hairtype = '%" & Request.Form("txtHairtype") & "%' AND
> Physique = '%" & Request.Form("txtPhysique") & "%' "
> SQL = SQL & "AND Mothertongue = '%" & Request.Form("txtMothertongue")
> & "%' AND Mothertongue2 = '%" & Request.Form("txtMothertongue2") & "%' "
> SQL = SQL & "AND Skills = '%" & Request.Form("txtSkills") & "%' AND
> Eyecolor = '%" & Request.Form("txtEyecolor") & "%' "
> SQL = SQL & "AND CarMake = '%" & Request.Form("txtCarMake") & "%' ORDER
> BY id"
>
> Set rstSimple = cnnSimple.Execute(SQL)
%%email.unsub%%
>
>
>