Can anyone help with an asp query

fluiduk · 2 Aug 2006 at 11:50

Hi All

I am working on a search box that searches the db by keywords and manufacturer name.

Now I can have got that to work, my problem however is adding the option to search by all manufacturers.

Is there a value that I can submit via the form to show records from all manufacturers.

this is my query

Code:

SELECT *
FROM productquery
WHERE manu_name LIKE '%MMColParam2%' and smalldes LIKE '%MMColParam%'
ORDER BY currentprice DESC

Im guessing I will end up having to use some fancy show if kind of stuff

Thanks
Aaron

HTMLHugo · 2 Aug 2006 at 12:21

you could just have 2 sql statements and use an if between them ?

TrUz · 2 Aug 2006 at 13:00

Code:

string MyQuery = "";

if (combo.text == "ALL")
{
    MyQuery = "SELECT * FROM productquery WHERE smalldes LIKE '%MMColParam%' ORDER BY currentprice DESC"
}
else
{
    MyQuery = "SELECT * FROM productquery WHERE manu_name LIKE '%MMColParam2%' and smalldes LIKE '%MMColParam%' ORDER BY currentprice DESC"
}

Something along those lines should do the trick.

TrUz

fluiduk · 2 Aug 2006 at 13:30

Cheers guys

This is how I did it in the end

Code:

<%

Dim products
Dim products_numRows

Set products = Server.CreateObject("ADODB.Recordset")
products.ActiveConnection = MM_wizarddata_STRING
If Request.QueryString("manu_name") = "showall" Then
products.Source = "SELECT *  FROM productquery  WHERE fulldes LIKE '%" + Replace(products__MMColParam, "'", "''") + "%'  or smalldes LIKE '%" + Replace(products__MMColParam, "'", "''") + "%' or manu_name LIKE '%" + Replace(products__MMColParam, "'", "''") + "%' ORDER BY currentprice DESC"
Else
products.Source = "SELECT * FROM productquery  WHERE manu_name LIKE '%" + Replace(products__MMColParam2, "'", "''") + "%' and fulldes LIKE '%" + Replace(products__MMColParam, "'", "''") + "%' and smalldes LIKE '%" + Replace(products__MMColParam, "'", "''") + "%'ORDER BY currentprice DESC"
End If
products.CursorType = 0
products.CursorLocation = 2
products.LockType = 1
products.Open()

products_numRows = 0

%>

However, now that i have it working another problem has surfaced.

I want to be able to search by manufacturer and keyword. So If a record matches both the manufacturer and the keyword then it is displayed. So I thought something like this would do it

Code:

"SELECT * FROM productquery  WHERE manu_name LIKE '%" + Replace(products__MMColParam2, "'", "''") + "%' and fulldes LIKE '%" + Replace(products__MMColParam, "'", "''") + "%' or smalldes LIKE '%" + Replace(products__MMColParam, "'", "''") + "%'ORDER BY currentprice DESC"

However, the use of the last 'or' statement seems to make it come up with records from all manufacturers. Is there a way of splitting the query, so it looks for all the ones that match the manufacturer and that have an instance of the keywords, in any one of the 4 fields that they could be in.

Sounds rather scary to me lol
Any help is greatly appreciated

Thanks
Aaron