Need help with a WHERE statement

[Shane930]

Hi, I am working on a search page passing a variable to a master detail page.
The SQL works fine and shows the correct variable in the debug on the page but
it will not return the records using this variable. The SQL works as it should
when I cut and past in into SQLyog and run the query returning all of the
records requested. When I remove the WHERE statement all of the DB records
show up on the master detail page correctly. So I am baffled. I am on CF 7,
mySQL 4.1.

Here is the query:
SELECT make, model, veh_year, ext_color, int_color, primary_image_path,
mileage, price
FROM saa.vehicle_ad
WHERE make = '#FORM.make#'
ORDER BY price ASC

Here is the SQL from the debug:

SELECT make, model, veh_year, ext_color, int_color, primary_image_path,
mileage, price
FROM saa.vehicle_ad
WHERE make = 'Honda'
ORDER BY price ASC

Any help would be great, thanks in advance.

Shane

[JMGibson3]

2 possibilities:

1. turn on debuggin to view actual submitted SQL, perhaps Form.make is not
coming through ok.
2. DataSource setting in the Administrator is pointing to a different
database than what you expect.

[Shane930]

JM, thanks for the quick reply. The debug is on and that is the second select
in the above entry. I shows that the #FORM.make is changed to 'Honda' as I
selected this on the search form. When the WHERE statement is removed I get
all of the records returned on the detail page. So I know the datasource is
correct. This is not making sense to me at all.

Thanks

Shane

[paross1]

Could you have a case sensitivity issue? I know with my Oracle database, I have
to match the case of the data in the column with the value in the query, so you
may need to do the same with your particular instance. Or, are you sure that
there is a row where equals ?Honda??

SELECT make, model, veh_year, ext_color, int_color, primary_image_path,
mileage, price
FROM saa.vehicle_ad
WHERE LOWER(make) = '#lcase(FORM.make)#'
ORDER BY price ASC

or

SELECT make, model, veh_year, ext_color, int_color, primary_image_path,
mileage, price
FROM saa.vehicle_ad
WHERE LCASE(make) = '#LCASE(FORM.make)#'
ORDER BY price ASC

Phil

[Shane930]

Well I found that the table I was using in mySQL was some how corrupt so I
dropped it and rebuilt a new one and the page works as planned. Thanks to Dan
and George for their input. Now on to my next challenge!

Shane