Hi all!
I have a problem in a search form with checkboxes and keyword.
I want to make a search with 3 options:
1) Based only on keyword
2) Based on a keyword + checkbox
3) Based on a checkboxes

In my aplication worked only 2 and 3 option, when i want a free
search with keyword and checkboxes unchecked i don't have any results.
Can anyone help me?

Thanks in advanced



My passages

1) I created a table

ID autonaumber
DESCRIPTION text
CATEGORY1 text default value 'no'
CATEGORY2 text default value 'no'
CATEGORY3 text default value 'no'

2) I created an update form
<textarea name="DESCRIPTION" cols="30"></textarea>
<input type="checkbox" name="CATEGORY1 " value=yes" >
<input type="checkbox" name="CATEGORY2 " value=yes" >
<input type="checkbox" name="CATEGORY3 " value=yes" >

3) I created a search form
<input type="text" name="PAROLA">
<input type="checkbox" name="CATEGORY1 " value=yes" >
<input type="checkbox" name="CATEGORY2 " value=yes" >
<input type="checkbox" name="CATEGORY3 " value=yes" >



4) I created a page of results:

<%
Dim rsRESULTS__var1
rsRESULTS__var1 = "1"
if (Request("CATEGORY1" <> "" then rsRESULTS__var1 =
Request("CATEGORY1"
%>
<%
Dim rsRESULTS__var2
rsRESULTS__var2 = "1"
if (Request("CATEGORY2" <> "" then rsRESULTS__var2 =
Request("CATEGORY2"
%>
<%
Dim rsRESULTS__var3
rsRESULTS__var3 = "1"
if (Request("CATEGORY3" <> "" then rsRESULTS__var3 =
Request("CATEGORY3"
%>

<%
Dim rsRESULTS__vardesc
rsRESULTS__vardesc = "%"
if (Request("parola" <> "" then rsRESULTS__vardesc =
Request("parola"
%>
<%
set rsRESULTS= Server.CreateObject("ADODB.Recordset"
rsRESULTS.ActiveConnection = MM_connPRODUCTION_STRING
rsRESULTS.Source = "SELECT * FROM dbo.JOBS WHERE DESCRIPTION LIKE '"
+ Replace(rsRESULTS__vardesc, "'", "''" + "' AND CATEGORY1 = '" +
Replace(rsRESULTS__var1, "'", "''" + "' OR CATEGORY2 ='" +
Replace(rsRESULTS__var2, "'", "''" + "' OR CATEGORY3 ='" +
Replace(rsRESULTS__var3, "'", "''" + "'
Replace(rsRESULTS.CursorType = 0
rsRESULTS.CursorLocation = 2
rsRESULTS.LockType = 3
rsRESULTS.Open()
rsRESULTS_numRows = 0
%>


Rebel

Edited by - frongia on 13 Jun 2002 18:15:04

Edited by - frongia on 13 Jun 2002 18:16:04

Edited by - frongia on 13 Jun 2002 18:17:11

Edited by - frongia on 13 Jun 2002 18:18:11

Use % sign.
Instead of
DESCRIPTION LIKE '" + Replace(rsRESULTS__vardesc, "'", "''" + "'

make it like
DESCRIPTION LIKE '%" + Replace(rsRESULTS__vardesc, "'", "''" + "%'"

Edited by - vfarcic on 13 Jun 2002 13:38:04

It gets a
sinthax error near
AND CATEGORY1 = '"
----------------------<

now i have

rsRESULTS.Source = "SELECT * FROM dbo.JOBS WHERE DESCRIPTION LIKE '%" + Replace(rsRESULTS__vardesc, "'", "''" + "%'" AND CATEGORY1 = '" + Replace(rsRESULTS__var1, "'", "''" + "' OR CATEGORY2 = '" + Replace(rsRESULTS__var2, "'", "''" + "' OR CATEGORY3 ='" + Replace(rsRESULTS__var3, "'", "''" + "'

Bye Giampaolo

Rebel

Edited by - frongia on 13 Jun 2002 18:11:40