|
| |
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
 |
Search using Multiple Keywords - 1/7/2003 20:03:57
Howdy All,
Does anybody know how to conduct an MS-Access search using a standard FP DRW form that has been dieted??? I' ve seen on many sites (including this one) where you can enter multiple keywords in a search... Anyone have any ideas on conducting a DRW or ASP Search using multiple keywords???
Thanks in Advance...
LLLuneeeee 
_____________________________
|
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Search using Multiple Keywords - 1/9/2003 5:34:31
Hi LLL,
I tried rdouglas' code, when I try it from frontpage it works fine but give SQL error.
If you go now to this address from here, you will get an error or (i) being out of range. Maybe I put the code in the wrong place. Click here to see the error.
http://www.fmhs.uaeu.ac.ae/immconf/CountryASP.asp
Here is how my page looks in HTML view:
<form BOTID=" 0" METHOD=" POST" ACTION=" CountryASP.asp" >
<table BORDER=" 0" >
<tr>
<td><b>Country</b></td>
<td>
<input TYPE=" TEXT" NAME=" Country" VALUE=" <%=Request(" Country" )%>" size=" 20" ></td>
</tr>
</table>
<p><br>
<input TYPE=" Submit" ><input TYPE=" Reset" ><!--webbot bot=" SaveAsASP" clientside suggestedext=" asp" preview=" " --></p>
<p> </p>
</form>
<table width=" 100%" border=" 1" >
<thead>
<tr>
<td><b>CustomerID</b></td>
<td><b>CompanyName</b></td>
<td><b>ContactName</b></td>
<td><b>ContactTitle</b></td>
<td><b>Country</b></td>
</tr>
</thead>
<tbody>
<!--#include file=" _fpclass/fpdblib.inc" -->
<%
DIM myArray, myQuerystring
myArray = split(Request.Form(" Country" )," " ) ' splitting on spaces
myQuerystring = " "
FOR i = 0 to (uBound(myArray)-1)
myQuerystring = myQuerystring & " ' " & myArray(i) & " ' ,"
NEXT
myQuerystring = myQuerystring & " ' " & myArray(i) & " ' "
%>
<% if 0 then %>
<SCRIPT Language=" JavaScript" >
document.write(" <div style=' background: yellow; color: black;' >The Database Results component on this page is unable to display database content. The page must have a filename ending in ' .asp' , and the web must be hosted on a server that supports Active Server Pages.</div>" );
</SCRIPT>
<% end if %>
<%
fp_sQry=" SELECT * FROM Customers WHERE Country IN (" & myQuerystring & " )"
fp_sDefault=" Country="
fp_sNoRecords=" <tr><td colspan=5 align=left width=" " 100%" " >No records returned.</td></tr>"
Best regards
Hisham
Best regards
Hisham
< Message edited by hhammash -- 1/9/2003 5:37:13 AM >
| |
|
|
|
garyb
Posts: 208
From: North Clarendon Vermont USA
Status: offline
|
RE: Search using Multiple Keywords - 1/9/2003 15:46:22
Here is an excellent example of how to do multiple keyword search with the ability of the user to choose all words, any word or exact match...
http://www.asp101.com/forum/display_message.asp?mid=164055
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Search using Multiple Keywords - 1/10/2003 9:44:50
Sorry ' bout not being around for a coupla' days - been quite busy...
Also sorry about the " IN" stuff - I' ve never had much luck with it and I was taking a shot at it. However I have been sucessful using the LIKE method similar to this:
<%DIM myArray, myQuerystring
myArray = split(Request.Form(" keywords" )," " ) ' splitting on spaces
myQuerystring = " (keyword LIKE ' %" & trim(myArray(0)) & " %' )"
IF uBound(myArray) > 0 THEN
FOR i = 1 to uBound(myArray)
myQuerystring = myQuerystring & " AND (keyword LIKE ' %" & trim(myArray(i)) & " %' )"
NEXT
END IF%>
Then use something like this in your DRW:
fp_sQry=" SELECT * FROM myTable WHERE " & myQuerystring
Again, the ' brute force' method but does seem to always work...[:' (] Hope it helps...
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Search using Multiple Keywords - 1/10/2003 11:55:50
Hi rdouglas,
Your first code (with the IN) almost worked. Please take a look at these cases and judge:
I pasted your code above the fp_sQry, saved the page then clicked the browse button. It showed the textbox and a yellow block of sql error. I typed in the textbox " I want to look for customers in USA and France" then pressed submit. It gave me results in both USA and France.
If I press refresh before I type a text I get a " The page cannot be displayed ...erro" and on that page I get that (i) is out of range.
If I put the page as a link on the forum I get the Page cannot be displayed.
Why did the code work the first time and will always work if I don' t press the refresh button to get rid of the sql error?
Thanks
Hisham
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Search using Multiple Keywords - 1/11/2003 13:25:45
Hi LLL,
So, what happened? did the code work?
I checked rdouglas' code again and again ( I mean the first one which searches with IN) it is almost working fine except it is saying the the (i) is out of range.
Best regards
Hisham
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Search using Multiple Keywords - 1/12/2003 15:03:08
I think I might see what' s happening. AND' s & OR' s are read (and executed) left to right, things done in parens first. So what I see happening is something like:
1 AND 2 OR 3
EDIT: Which is the same thing as (1 AND 2) OR 3!
What I think you want is:
1 AND (2 OR 3)
Do I see this correctly? If so, try these lines:
<%
Dim Keyw
Dim myquery
Dim myQuerystring
Keyw = split(Request.Form(" Keyword" )," " )
myQuerystring = " (ItemName Like ' %" & trim(Keyw(0)) & " %' "
myQuerystring = myQuerystring & " Or Description Like ' %" & trim(Keyw(0)) & " %' )"
if uBound(Keyw) > 0 Then
for i = 1 to uBound(Keyw)
myQuerystring = myQuerystring & " OR (ItemName Like ' %" & trim(Keyw(i)) & " %' )"
myQuerystring = myQuerystring & " OR (Description Like ' %" & trim(Keyw(i)) & " %' )"
next
end if
Keyw = " SELECT * FROM SpecialsDatabase WHERE (ProductType= ' ::ProductType::' AND ("
Keyw = Keyw + myQuerystring
Keyw = Keyw + " )) ORDER BY EQPSpecialPrice ASC"
myquery = keyw
response.write (myquery & " <BR>" )
%>
See the extra parens? I hope it helps...
< Message edited by rdouglass -- 1/12/2003 3:04:21 PM >
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Search using Multiple Keywords - 1/13/2003 5:29:49
Hi rdouglas,
Would you please check your first code which create an array then in sql search as (where fieldname in (" & mystring & " ). It is almost working and it is searching fine at the first load.
If I press refresh the page can not be display and the error is (i) is out of range.
It is important because IN difference from Like in the results.
Thanks
Hisham
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Search using Multiple Keywords - 1/13/2003 8:13:28
Hisham,
quote:
about the " IN" stuff - I' ve never had much luck with it and I was taking a shot at it
What specifically are you having a problem with?
EDIT: If I had to take a guess right now, I' d try this:
<%DIM myArray, myQuerystring
myArray = split(Request.Form(" keywords" )," " ) ' splitting on spaces
myQuerystring = " "
FOR i = 0 to (uBound(myArray)-1)
myQuerystring = myQuerystring & " ' " & myArray(i) & " ' ,"
NEXT
' don' t want a comma trailing the last one
myQuerystring = myQuerystring & " ' " & myArray(ubound(myArray)) & " ' " %>
Haven' t tested - just a guess...
< Message edited by rdouglass -- 1/13/2003 8:17:40 AM >
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Search using Multiple Keywords - 1/13/2003 8:15:33
Thanks rdougls
Hisham
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Search using Multiple Keywords - 1/16/2003 2:11:38
Hi rdouglas,
quote:
What specifically are you having a problem with?
EDIT: If I had to take a guess right now, I' d try this:
<%DIM myArray, myQuerystring
myArray = split(Request.Form(" keywords" )," " ) ' splitting on spaces
myQuerystring = " "
.....etc
I tried something with your last modified code using IN with the Where statement.
What I did was put the form on page alone, then created another ASP page with the dieted DRW and added your code before the DRW. It worked fine. The problem occurs when you put the form, your code and the dieted DRW on the same page.
Why do you think this happens.
On separate pages they are working perfectly.
Best regards
Hisham
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Search using Multiple Keywords - 1/16/2003 2:19:21
Hi LLL,
Glad it worked.
If you are interested, I would like to inform you that the code also worked using " WHERE IN" .
What to do is put the search form on a page, then create another page for the DRW, diet it, put the last code posted by rdouglas for using " IN" . Then post the form to the DRW page. It works perfectly.
Would you please post your successful page? Just the code and the SQL statement?
Thanks a lot LLL
Hisham
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
. But if you must use a DRW, I would try to build the query outside the DRW and pass it as a variable. Maybe something like: 
ky 