tonikaram
asked on
URGENT: insert database (asp), how to give search options on search form
Dear all,
In frontpage 2002 (& probably 2000): Insert -> Database -> Results, I create a connection to an access database, etc, etc until I have a search button that searches the accessdb through a cuztomizable SQL query, it works and returns results.
The question is, I would like to have options for different sql queries! the user must choose whether he wants to search the country field, or the name field, etc. why is it so hard to do/invisible in frontpage. To make things more clear, here is the piece of ASP coding I changed to do that, within the code created by Frontpage:
The query line:
fp_sQry="SELECT * FROM DRI WHERE (Country Like '%::search_text::%')"
is replaced by these:
-----------------
if Request("lstcriteria")="Co
if Request("lstcriteria")="In
-----------------
that way the query used is decided by the dropdown list "lstcriteria". i.e to search the country or the name. But this simple code modification doesn't work, as soon as I do it, the search returns all records on every search. Probably because there is some back-end coding that is done in other places by frontpage, that is not compatible with the two queries. Also, I know that opening the ASP page with frontpage automatically overwrites the modified code. I didn't open it, so that is not the problem.
How can I give the user options to search different fields??
Bare in mind that I am no programmer, I can write some code and some VB programs sometimes but that's it. ALso, I know that interdev can do this very easily, but I have no time to learn it! how to do it in a simple way?
Please answer.
Thanks,
Tony
In frontpage 2002 (& probably 2000): Insert -> Database -> Results, I create a connection to an access database, etc, etc until I have a search button that searches the accessdb through a cuztomizable SQL query, it works and returns results.
The question is, I would like to have options for different sql queries! the user must choose whether he wants to search the country field, or the name field, etc. why is it so hard to do/invisible in frontpage. To make things more clear, here is the piece of ASP coding I changed to do that, within the code created by Frontpage:
The query line:
fp_sQry="SELECT * FROM DRI WHERE (Country Like '%::search_text::%')"
is replaced by these:
-----------------
if Request("lstcriteria")="Co
if Request("lstcriteria")="In
-----------------
that way the query used is decided by the dropdown list "lstcriteria". i.e to search the country or the name. But this simple code modification doesn't work, as soon as I do it, the search returns all records on every search. Probably because there is some back-end coding that is done in other places by frontpage, that is not compatible with the two queries. Also, I know that opening the ASP page with frontpage automatically overwrites the modified code. I didn't open it, so that is not the problem.
How can I give the user options to search different fields??
Bare in mind that I am no programmer, I can write some code and some VB programs sometimes but that's it. ALso, I know that interdev can do this very easily, but I have no time to learn it! how to do it in a simple way?
Please answer.
Thanks,
Tony
Hi,
I forgot to tell you that your DRW should be dieted and the above code will be put after the <% which comes before the fp_sQry=.... and remove the original fp_sQry= ... line, because it will be between your if statements.
Regards
hhammash
I forgot to tell you that your DRW should be dieted and the above code will be put after the <% which comes before the fp_sQry=.... and remove the original fp_sQry= ... line, because it will be between your if statements.
Regards
hhammash
Hi,
Create a form
In the form put 1 drop down box
Put in it the names of the fields
Insert a textbox next to it (this will be the search box)
Name the drop down as lstCriteria and the textfield as SearchText.
Now, the user will type his/her search in the textbox and select the field from the drop down then presses submit.
The results DRW will take first the value that comes from the DROP down and see what it is, accordingly decides which sql to use.
hhammash
Create a form
In the form put 1 drop down box
Put in it the names of the fields
Insert a textbox next to it (this will be the search box)
Name the drop down as lstCriteria and the textfield as SearchText.
Now, the user will type his/her search in the textbox and select the field from the drop down then presses submit.
The results DRW will take first the value that comes from the DROP down and see what it is, accordingly decides which sql to use.
hhammash
Hi,
Here are another two suggestions:
Dim strChoice, strTextSearch
strChoice=request.form("ls
strTextSearch=request.form
if strChoice="Country" then
fp_sQry="select ....... where country
elseif strChoice="Name" then
fp_sQry="select ....... where country
end if
Save the page then run the form and start searching.
Another suggestion is to use the Select Statement instead of the Elseif:
Select Case strChoice
Case "Country"
fp_sQry="select ....... where country
Case "Name"
fp_sQry="select ....... where country
End Select
All the above code sample should come after:
<% end if %>
<%
and before
fp_sDefault="CustomerID="
Note that the original:
fp_sQry="SELECT .... will be deleted because you put other fp_Qry between your code.
Thanks
hhammash
Here are another two suggestions:
Dim strChoice, strTextSearch
strChoice=request.form("ls
strTextSearch=request.form
if strChoice="Country" then
fp_sQry="select ....... where country
elseif strChoice="Name" then
fp_sQry="select ....... where country
end if
Save the page then run the form and start searching.
Another suggestion is to use the Select Statement instead of the Elseif:
Select Case strChoice
Case "Country"
fp_sQry="select ....... where country
Case "Name"
fp_sQry="select ....... where country
End Select
All the above code sample should come after:
<% end if %>
<%
and before
fp_sDefault="CustomerID="
Note that the original:
fp_sQry="SELECT .... will be deleted because you put other fp_Qry between your code.
Thanks
hhammash
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks alot hhammash,
I see you have given alot of options!
ironically, it worked for me by simply adding one more criteria, and then changing the sql query as I want, including both searchable fields. now I have two text boxes, and leaving one blank will ignore that box. It is not what I intended, but works as well.
Now I am developping the search. I added three boxe (one as a drop-down), I need to display the results as links, want to ignore the ' character in the search text, and finally, I would like to use OR between the words entered instead of trying to match the exact string.
But the initial presentation of the search capability passed well!
I will work on the above, and might post a question about it pretty soon.. so keep an eye!
Thanks,
Tony
I see you have given alot of options!
ironically, it worked for me by simply adding one more criteria, and then changing the sql query as I want, including both searchable fields. now I have two text boxes, and leaving one blank will ignore that box. It is not what I intended, but works as well.
Now I am developping the search. I added three boxe (one as a drop-down), I need to display the results as links, want to ignore the ' character in the search text, and finally, I would like to use OR between the words entered instead of trying to match the exact string.
But the initial presentation of the search capability passed well!
I will work on the above, and might post a question about it pretty soon.. so keep an eye!
Thanks,
Tony
Hi Tony,
Welcome at any time.
hhammash
Welcome at any time.
hhammash
Try something like that.
If Request.Form("lstcriteria"
fp_sQry="SELECT * FROM DRI Where country ...."
End if
If Request.Form("lstcriteria"
fp_sQry="SELECT * FROM DRI WHERE Name .. (Name = '::Name::')"
End If
Let me know
hhamamsh