ASP / SQL - "AND LIKE" QUESTION

[smudger115]

Help Please - i a bit ofg a newbie!

have i done something wrong here .....

<%
' get form values
licence = TRIM( Request( "PRO_valid_licence" ) )
Area = TRIM( Request( "PRO_country_area" ) )
City = TRIM( Request( "PRO_serving_city" ) )
RealName = TRIM( Request( "PRO_RealName" ) )
Name1 = TRIM( Request( "PRO_Name" ) )
ClothesSize = TRIM( Request( "Pro_clothes_size" ) )
DOB = TRIM( Request( "PRO_DATED" ) )
Mobility = TRIM( Request( "PRO_PRICE" ) )
ParentName = TRIM( Request( "PRO_Parent_Name" ) )
HomeTel = TRIM( Request( "PRO_Hometel" ) )
WorkTel = TRIM( Request( "PRO_WorkTel" ) )
Mobile = TRIM( Request( "PRO_Mobile" ) )
SOS1 = TRIM( Request( "PRO_SOS1" ) )
SOS2 = TRIM( Request( "PRO_SOS2" ) )
EYE = TRIM( Request( "PRO_EYE_Colour" ) )
Skin = TRIM( Request( "PRO_SKIN_COLOUR" ) )
Hair = TRIM( Request( "PRO_Hair_colour" ) )
Email = TRIM( Request( "PRO_Parent_email" ) )
%>
<%
' open record set

sqlString = "SELECT Pro_valid_licence, Pro_country_area, Pro_serving_city, Pro_realname, PRO_NAME, Pro_clothes_size, PRO_DATED, Pro_price, Pro_parentname, Pro_HomeTel, Pro_WorkTel, Pro_Mobile, Pro_SOS1, Pro_SOS2, Pro_Eye_Colour, Pro_Skin_colour, Pro_Hair_colour, Pro_Parent_email " &_
"FROM PRODUCTS "&_
"WHERE Pro_valid_licence LIKE '%" & Licence & "%' "&_
"AND pro_country_area LIKE '%" & Area & "%' "&_
"AND Pro_serving_city LIKE '%" & city & "%' "&_
"AND Pro_realName LIKE '%" & realname & "%' "&_
"AND PRO_NAME LIKE '%" & name1 & "%' "&_
"AND Pro_clothes_size LIKE '%" & clothessize & "%' "&_
"AND PRO_DATED LIKE '%" & DOB & "%' "&_
"AND PRO_PRICE LIKE '%" & mobility & "%' "&_
"AND Pro_parentName LIKE '%" & ParentName & "%' "&_
"AND Pro_HomeTel LIKE '%" & HomeTel & "%' "&_
"AND Pro_WorkTel LIKE '%" & WorkTel & "%' "&_
"AND Pro_Mobile LIKE '%" & Mobile & "%' "&_
"AND Pro_SOS1 LIKE '%" & SOS1 & "%' "&_
"AND Pro_SOS2 LIKE '%" & SOS2 & "%' "&_
"AND Pro_Eye_Colour LIKE '%" & EYE & "%' "&_
"AND Pro_Skin_colour LIKE '%" & Skin & "%' "&_
"AND Pro_Hair_colour LIKE '%" & Hair & "%' "&_
"AND Pro_Parent_email LIKE '%" & Email & "%' ) "&_
"Order BY PRO_NAME "
SET RSbabies = con.execute( sqlString )
%>

(all this resides on an ASP web page - which i have not included to hopefully make clearer!)
the query never returns any results - even when i feed it exact matches

have i missed something ?

regards Nigel

 

[OhioSteve]

First, verify that you can interact with Access. Write a very simple sql and display the results.

Second, evaluate your where clause. It looks like you may need several sets of parentheses in your where clause.

Third, consider simplifying your selection sql. You may want to let the user enter one value, put it into a where clause, and then display the results. The user can choose the exact record from that list.

 

[smudger115]

Hi Steve thanks for your reply

1 - yes i tried that first - connection is ok

2 - yes i think your right - but do you have any ideas where the missing parentheses belong ! (as i said i'm quite a newbie)

3 - unfortunately my client wants to narrow the search as much as possible (she is a modelling agent and her customers are very specific when selecting models IE brown hair, brown eyes, 150 cms height etc)

thanks

Nigel

 

[PHV]

Are ALL the fields in the where clause defined as text in the table ?
I don't think the Like operator is well suited for date or numeric fields.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[smudger115]

Hi PH

thanks for that - i double checked my fields some were set to MEMO - so i have changed to TEXT - but no change. I am fairly sure that its a problem with the positioning of Parentheses - but its hurting my head - think i will look again 2morro

regards Nigel

 

[lespaul]

If you are using a connection from an ASP page, the () that Jet SQL needs aren't an issue.

What do you expect to happen if they leave a field blank on the ASP Page?

leslie

 

[PHV]

Where is the opening parenthese corresponding to the following closing one ?
"AND Pro_Parent_email LIKE '%" & Email & "%' [highlight])[/highlight] "&_

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cmmrfrds]

Are any of the fields defined to allow nulls in the database. This '%%' will not catch nulls in a field. I am not sure about empty field, although it should catch spaces. So, in your test you have a record where EVERY field has content and matched the criteria?

 

[smudger115]

Hi ....

I spotted and removed that extra closing parentheses - but made no difference.

regarding nulls - no all fields should contain values. and yes i have given every field a value in my tests (and written them to double check)