I have a textbox in a searchform where the user can - if he wants - write a keyword. However, if he DOESN'T I do not want this to limit the number of hits. Therefore I write this in my SQL-statement:
....WHERE ('Description' LIKE '%Keyword%') OR ('Keyword' = '').
The first part is working but the second part isn't. What's wrong?



Edited by - brutus on 11 Aug 2004 01:06:09

Brutus, I had a similar search form..here's how I solved it within DMX:

SELECT * (or list the fields you want here)
FROM (YOUR TABLE NAME HERE)
WHERE DESCRIPTION LIKE 'MMColParam%' OR KEYWORD LIKE '%MMColParam2%'

Variable: MMColParam Run-time variable: Request.Form("DESCRIPTION"
Variable: MMColParam2 Run-time variable: Request.Form("KEYWORD"

Thanks for your answer, but I think you misunderstood my problem. DESCRIPTION is the database field and KEYWORD is the form variable. My SQL-statement therefore says: get the records where the database field contains the keyword. If the keyword field IN THE FORM is empty, you take all the records.

can you just give your field a default value of "%"
so if nothing is entered on the form then the query uses the wildcard

Sharing knowledge saves valuable time!

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]

Does this work even if the user first writes a keyword and then goes back and erases the keyword and makes a new search? <b></b>

Should do
If on page1.asp you have your search form which Posts its data over to page2.asp
and on page2.asp you build a recordset filtering it according to the values sent to it from page1.asp

Then if the textbox is empty when the page is submitted page2.asp will use the default value you've assigned to the textbox as nothing will be posted to it from the textbox field.

Sharing knowledge saves valuable time!

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]