[thelist] sql filtering problem

[Joshua Olson]

----- Original Message -----
From: <dn at dittodesign.co.uk>
Subject: [thelist] sql filtering problem


> Hi all,

Hi Darren,

> Problems, problems, problems. *sigh*

No worries.

> Currently I land on the results page with this url
> brand_product.asp?Product_Type=Microwave&Brand=aeg
>
> and rather than show only microwaves by aeg it lists all microwaves in
> the DB.
>
> Where am I going wrong. Here's the code -
>
> Sql is -
> SELECT Brand_Logo, Brand_Logo_sml, ID, Product_Type, Brand, Model,
> Reference_No, Colour, image, Retail_Price, Delivery_Charge
> FROM kitchen_appliance
> WHERE Brand = 'MMColParam' and Product_Type='MMColParamP'

The resultant SQL you want is:

SELECT Brand_Logo, Brand_Logo_sml, ID, Product_Type, Brand, Model,
Reference_No, Colour, image, Retail_Price, Delivery_Charge
FROM kitchen_appliance
WHERE Brand LIKE 'aeg' and Product_Type LIKE 'Microwave'

The trick is to get that SQL string constucted.  The easiest code is:

SQL = "Brand_Logo, Brand_Logo_sml, ID, Product_Type, Brand, Model, " & _
"Reference_No, Colour, image, Retail_Price, Delivery_Charge " & _
"FROM kitchen_appliance " & _
"WHERE Brand LIKE '" & Request.QueryString("Brand") & "%' " & _
"and Product_Type LIKE '" & Request.QueryString("Product_Type") & "%'"

> Both MMColParam and MMColParamP
> are defined at the top of the page -

> <%
> Dim RsProduct__MMColParam
> RsProduct__MMColParam = "%"
> if (Request.QueryString("Brand") <> "") then RsProduct__MMColParam =
> Request.QueryString("Brand")
> %>
> <%
> Dim RsProduct__MMColParamP
> RsProduct__MMColParamP = "%"
> if (Request.QueryString("Product_Type")  <> "") then
RsProduct__MMColParamP
> = Request.QueryString("Product_Type")
> %>

The fact that you've overwritten RsProduct__MMColParam with the second set
of code is going to be a problem.  Try this code instead, since it's much
easier to understand.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim SearchProductType, SearchBrand
SeardProductType = "%"
SearchBrand = "%"
if (Request.QueryString("Brand") <> "") Then SearchBrand =
Request.QueryString("Brand")
if (Request.QueryString("ProductType") <> "") Then SearchBrand =
Request.QueryString("ProductType")
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Then, your final code could be:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL = "Brand_Logo, Brand_Logo_sml, ID, Product_Type, Brand, Model, " & _
"Reference_No, Colour, image, Retail_Price, Delivery_Charge " & _
"FROM kitchen_appliance " & _
"WHERE Brand LIKE '" & SearchBrand & "' " & _
"AND Product_Type='" & SearchProductType & "'"

Set MyRecordSet = MyConnection.Execute(SQL)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Does that Help?

-joshua