Multiple Text Box Searching...

[dodgyone]

I would like to have 4 text boxes which when submitted ALL searches one field in the database to give multiple results in the same loop. However, I would like it so that the user can decide to fill only one box in for example if they decide, leave the other 3 blank and still get the appropraite result and so on.

Is the filter and sql etc used below any way close to getting this?...

<%
w = Request.QueryString(&quot;w_etc&quot
x = Request.QueryString(&quot;x_etc&quot
y = Request.QueryString(&quot;y_etc &quot
z = Request.QueryString(&quot;y_etc&quot
%>

<%
' set up database conection
' this connection does not require an ODBC reference
path = Trim(Server.MapPath(&quot;/PATH&quot)
conn_string = &quot;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=&quot; + path + &quot;\DATABASE.mdb&quot;
Set con = Server.CreateObject(&quot;ADODB.Connection&quot
con.open conn_string

IF cstr(w) <> &quot;&quot; THEN
mode=&quot;filtered&quot;
mySQL = &quot;SELECT * FROM DB_table where FIELD like &quot; & chr(39) &cstr(w) & chr(39)&&quot;;&quot;
END IF

IF cstr(x) <> &quot;&quot; THEN
mode=&quot;filtered&quot;
mySQL = &quot;SELECT * FROM DB_table where FIELD like &quot; & chr(39) &cstr(x) & chr(39)&&quot;;&quot;
END IF

IF cstr <> &quot;&quot; THEN
mode=&quot;filtered&quot;
mySQL = &quot;SELECT * FROM DB_table where FIELD like &quot; & chr(39) &cstr & chr(39)&&quot;;&quot;
END IF

IF cstr(Z) <> &quot;&quot; THEN
mode=&quot;filtered&quot;
mySQL = &quot;SELECT * FROM DB_table where FIELD like &quot; & chr(39) &cstr(Z) & chr(39)&&quot;;&quot;
END IF

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open mySQL, con, 0, 1

%>

Any help would be appreciated on either the starting form or how to process the search criteria etc etc or any comments if you have similar examples on your pages.

Thanks in advance,

Marcus

 

[RoadRacer]

The solution you have shown will only do a search on your Z field should all 4 of them be filled in and would ignore the other 3. Should the user only fill in 1 field, you're set. What if they fill in only 2 or 3? Whichever they fill in last will be the only one searched on. Try this where all your If Statements are. I don't quite understand why you have the mode variable but I left it in:

tempSQL = &quot;SELECT * FROM DB_table where FIELD in (&quot;

IF cstr(w) <> &quot;&quot; THEN
mode=&quot;filtered&quot;
tempSQL = tempSQL & &quot;'&quot; & cstr(w) & &quot;',&quot;
END IF

IF cstr(x) <> &quot;&quot; THEN
mode=&quot;filtered&quot;
tempSQL = tempSQL & &quot;'&quot; & cstr(x) & &quot;',&quot;
END IF

IF cstr <> &quot;&quot; THEN
mode=&quot;filtered&quot;
tempSQL = tempSQL & &quot;'&quot; & cstr & &quot;',&quot;
END IF

IF cstr(Z) <> &quot;&quot; THEN
mode=&quot;filtered&quot;
tempSQL = tempSQL & &quot;'&quot; & cstr(z) & &quot;',&quot;
END IF

'Remove extra comma and finish SQL statement
mySQL = Left(tempSQL, Len(tempSQL) - 1) & &quot;&quot;

This will do a query with all 4 possible fields being plugged in. Drawback is that it could create an invalid statement shoudl the user submit all fields empty ... but you should stop this before we get to this point anyhow. =)

I wrote this here within the constraints of this little text box but I think my idea is conveyed properly. Excuse any little typo or oversight. Let me know if it helps.
Ed (RoadRacer) Holguin

&quot;I Hate Computers!&quot;

 

[dodgyone]

Thanks for the help Ed...

I've tried your code but the problem I've had all along is that the process page just does not show any results. The search criteria does not seem to be processed or there is seomthing wrong with the looping but this seems okay. The search criteria is entered into the URL to pass onto the page but that's all I seem to get.

Should I of put something on the search page which I've missed... it is just a plain form on .htm which uses 'GET' and actions to the process page.

Also I've reponse.writed the code just to see the output and it's set up as:

SELECT * FROM pic_table where Cadastre IN ('A1','A2','A3','A4');

This seems alright to me.... any ideas?

Cheers for taking the time out to help,

Marcus

 

[RoadRacer]

Ok, are you getting a recordset with the resulting Query?

If so, how are you looping through the recordset to display the results? Maybe the problem you have lies in there. Maybe you could post the gist of your loop to see how that's written. But most importantly, double check that the query is definitely returning a recordset. I'm not too clear on what you mean by it not showing any results. Do you get an error? Empty Recordset? etc ... Ed (RoadRacer) Holguin

&quot;I Hate Computers!&quot;