searching a dropdown

[janise]

Hello again dear experts!
I need your help please.

I am trying to implement a database search option whereby a user is presented with a dropdown option to select from.
I want to give the user the ability to search our database using 3 fields.

select * from tableName where field1 like '"%field1%"'
or field2 like '"%field2%'"
or field3 like '"%field3%"'

The syntax may be off but the idea is that with the select statement, the user is then given a dropdown box with
field1,field2 and field3.
If the user clicks on field1, he or she is taken to popup window with an textbox to perform the search.
If there is a hit, the result is displayed at the bottom of the popup.
If no hit, the user gets a message at the bottom of the popup screen that says no records found.
Can I get someone to please help me out or direct me to a link that will assist.
I believe that once I get the dropdown code that will take me to popup screen is working, I can figure out the rest.
Thanks in advance

 

[Tarwn]

If I am understanding correctly then I think you may not even need the popup window to enter the the search string. If you set up the dropdown to have ahuman understandable name for the field as the text and the actual field name for the value, then also give them the text input right there, you will be able to construct the SQL statement on the fly quite easily on the results page.

For example, say we have the following table:
MyTable:
my_id - autonumber, primary key
my_color - text field
my_car - text field
my_sign - text field

Then we could have the following form for the search page:

<form method=&quot;POST&quot; action=&quot;results.asp&quot;>
<b>Instructions:</b> Select the criteria you would like to search on and enter the text you would like to search for in that criteria.<br>
Criteria: 
<select name=&quot;selField&quot;>
   <option value=&quot;my_color&quot;>Colors</option>
   <option value=&quot;my_car&quot;>Cars</options>
   <option value=&quot;my_sign&quot;>Horoscope Signs</option>
</select><br>
Search For: <input type=&quot;text&quot; name=&quot;txtSearch&quot;>
</form>

Then our results.asp page could build the sql string like so:

Dim sql_string, str_text
'escape any single quotes to be safe
str_text = Replace(Request.Form("txtSearch"),"'","''")
sql_string = "SELECT * FROM MyTable WHERE " & Request.Form("selField") & " LIKE '%" & str_text & "%'"

So the user takes care of thr trouble of specifying the field for us and e just have to plug in their values.

If you do want to do this with a popup window then you will need to add some complications. First the page that will be displayed in the popup will need to have a hidden field to store the search criteria (field name). When you open the window you will need to pass that as part of the querystring based on the value of the dropdown. Then when the finish entering the search text in the child window you will need to submit that form with target="parent" so it will submit back to the main window. You then still have a popup window which you can get rid of from the main window in the results form onLoad. As you can see this is getting fairly complicated.

Let us know if you truly need the popup window and get stuck with the implementation.

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[janise]

thank you very much Tarwn.

The solution you provided is very nice, however, it is the user who wants it that way.

To add a little bit to your suggestion, now that we have a dropdown, this dropdown will be on the main page.
Once the user selects a choice from the dropdown, a popup window automatically opens up with an input text search box.
This input text search box allows the user to enter a search criteria like taking your field my_car for instance, the user can type :Mazda and the result will display immediately below the searchbox.
Yes indeed it is complicated.
Any help in implementing this will be greatly, greatly appreciated.