|
| |
|
|
contactcp
Posts: 167
From: None
Status: offline
 |
Desperate call for fp_sQry= help - 7/26/2002 16:14:42
I have posted about this previously but I explained it wrong so here I go again. Please have a read through as I am getting desperate and tired after trying to solve it for 3 days and nights. I have learnt a lot of other things through the amount of stuff i have read through but not an answer to this. Anyway:
I have a form to search my database which I created in FP2000 (DRW). However, I think the fp_sQry=" code is completely wrong. I would be most grateful if someone can guide me in the right direction here.
I have 4 search entries to query the database and they are: (language from), (language to), (area) and (Name).
What I want them to search in the database is as follows:
language from = search in database under fields (languagefrom1,languagefrom2,languagefrom3) for a match.
language to = search in database under fields (languageto1,languageto2) for a match.
area = search in database under fields (area1, area2 through to area8) for a match.
name = Search under name field
The code that i have now is:
fp_sQry=" SELECT * FROM Results WHERE (Translatefrom1 LIKE ' %::Translatefrom1::%' OR Translatefrom2 LIKE ' %::Translatefrom2::%' OR Translatefrom3 LIKE ' %::Translatefrom3::%' AND Translateto1 LIKE ' %::Translateto1::%' OR Translateto2 LIKE ' %::Translateto2::%' AND Area1 LIKE ' %::Area1::%' OR Area2 LIKE ' %::Area2::%' OR Area3 LIKE ' %::Area3::%' OR Area4 LIKE ' %::Area4::%' OR Area5 LIKE ' %::Area5::%' OR Area6 LIKE ' %::Area6::%' OR Area7 LIKE ' %::Area7::%' OR Area8 LIKE ' %::Area8::%' AND Name LIKE ' %::Name::%' )"
fp_sDefault=" Translatefrom=NULL&Translateto=NULL&Area=NULL&Name=NULL"
And the code for the form is:
<td><input TYPE=" TEXT" NAME=" Translatefrom" VALUE=" <%=Request(" Translatefrom1" )%>" size=" 20" ></td>
<tr>
<td><input TYPE=" TEXT" NAME=" Translateto" VALUE=" <%=Request(" Translateto1" )%>" size=" 20" ></td>
</tr>
<tr>
<td><input TYPE=" TEXT" NAME=" Area" VALUE=" <%=Request(" Area1" )%>" size=" 20" ></td>
</tr>
<tr>
<td><input TYPE=" TEXT" NAME=" Name" VALUE=" <%=Request(" Name" )%>" size=" 20" ></td>
I really hope someone understands what I am trying to do and can offer some help!
|
|
|
|
rdouglass
Posts: 9224
From: Biddeford, ME USA
Status: offline
|
RE: Desperate call for fp_sQry= help - 7/27/2002 9:06:23
I have done a similar thing in building a simple search engine. However I built my SQL string outside the DRW then passed a variable to the DRW.
Also, you' ll need to pay close attention to your grouping of your AND' s and OR' s. If you don' t group them properly, you won' t get the expected results. You' ll see what I mean if you look at the example I mention below.
I put some example code on a page. This code comes from a page I use for an IntraNet help desk for finding tips and hints on internally developed applications. There is a lot more to this page, but I think you' ll get the idea.
Example Code
Note: If you' re not very comfortable with VBScript, it may look intimidating, but it really is just text string manipulation....
| |
|
|
|
rdouglass
Posts: 9224
From: Biddeford, ME USA
Status: offline
|
RE: Desperate call for fp_sQry= help - 7/27/2002 13:44:36
Sorry  Didn' t mean to insult anyone, but after you' ve played around with VBScript, it' s not that bad....[:p]
Anyways, yes I do have 5 fields and 5 strings. Again, my code was just an example of one way to do it, not your specific solution. My point was basically to build your query OUTSIDE the DRW. In order to do that, you' ll have to look at each / every field and determine if it is to be included in the query or no (if it has something, include it - if not, ignore it).
For instance, one of your sections could look something like:
myQueryString = myQueryString & " AND (languagefrom LIKE ' %::languagefrom1::%' OR languagefrom LIKE ' %::languagefrom2::%' OR languagefrom LIKE ' %::languagefrom3::%' )"
and the next one if needed would look similar but using language2:
myQueryString = myQueryString & " AND (languageto LIKE ' %::languagefrom1::%' OR languageto LIKE ' %::languagefrom2::%' OR languageto LIKE ' %::languagefrom3::%' )"
The way I see your problem is that one of the keys is: 1) to determine if there is something in the box, and 2) if so, add it to the query.
Am I understanding your problem correctly??
| |
|
|
|
contactcp
Posts: 167
From: None
Status: offline
|
RE: Desperate call for fp_sQry= help - 7/27/2002 14:48:45
These are what I am trying to achieve:
http://www.proz.com/?sp=wi&eid_c=34921
http://asp2.smelink.se/a1029/sfoe/sok_english.asp
These are just two, I could show you many more but they all do the same thing.
This is the form on my site:
http://www.english-partner.com/register.asp
So you get an idea how things are entered into my database. Maybe you have a better solution. I know that instead of having drop down lists I could get the user to enter text seperated by commas, but I have heard that the database will get messy once there is a lot of data in there.
What do you think?[:j]
| |
|
|
|
rdouglass
Posts: 9224
From: Biddeford, ME USA
Status: offline
|
RE: Desperate call for fp_sQry= help - 7/27/2002 15:31:27
OK, I think I' m getting there. However I still have 1 more question: Are all your search fields required? If they are (and you use drop-downs to ensure data validity), then your SQL code would probably look something like:
fp_sQry=" SELECT * FROM Results WHERE ((Translatefrom LIKE ' %::Translatefrom1::%' OR Translatefrom LIKE ' %::Translatefrom2::%' OR Translatefrom LIKE ' %::Translatefrom3::%' ) AND (Translateto LIKE ' %::Translateto2::%' OR Translateto LIKE ' %::Translateto2::%' ) AND (Area LIKE ' %::Area1::%' OR Area LIKE ' %::Area2::%' OR Area LIKE ' %::Area3::%' OR Area LIKE ' %::Area4::%' OR Area LIKE ' %::Area5::%' OR Area LIKE ' %::Area6::%' OR Area LIKE ' %::Area7::%' OR Area LIKE ' %::Area8::%' ) AND (Name LIKE ' %::Name::%' ))"
Notice the grouping. Logically, AND' s and OR' s are processed left to right unless grouped by parenthesis. Also notice I' ve only used 1 ' translatefrom' , ' translateto' , and ' area' fields submitted. IOW, I' m assuming the 4 Search fields (on the submitted form) are called ' translatefrom' , ' translateto' , ' area' , and ' name' (I' m using the examples as you showed for a basis). Lastly remember that this requires all fields to have something in them - I don' t believe the NULL value will work the way you want it to. That' s why you may need to do some VBScript (to determine if needed or not).
Any help there???
| |
|
|
|
rdouglass
Posts: 9224
From: Biddeford, ME USA
Status: offline
|
RE: Desperate call for fp_sQry= help - 7/27/2002 16:23:31
Nope, not at all. You' ll need to ' combine?' some of the stuff above. If you want to use one/some/all the fields I still believe you need to build your query outside the drw then call it. Something like:
<%
DIM mySQL
IF Request.Form(" translatefrom" ) > " " THEN
mySQL = " (Translatefrom LIKE ' %::Translatefrom1::%' OR Translatefrom LIKE ' %::Translatefrom2::%' OR Translatefrom LIKE ' %::Translatefrom3::%' )"
END IF
IF Request.Form(" translateto" ) > " " THEN
mySQL = mySQL & " AND (Translateto LIKE ' %::Translateto2::%' OR Translateto LIKE ' %::Translateto2::%' )"
END IF
IF Request.Form(" area" ) > " " THEN
mySQL = mySQL & " AND (Area LIKE ' %::Area1::%' OR Area LIKE ' %::Area2::%' OR Area LIKE ' %::Area3::%' OR Area LIKE ' %::Area4::%' OR Area LIKE ' %::Area5::%' OR Area LIKE ' %::Area6::%' OR Area LIKE ' %::Area7::%' OR Area LIKE ' %::Area8::%' )"
END IF
IF Request.Form(" name" ) > " " THEN
mySQL = mySQL & " AND (Name LIKE ' %::Name::%' )"
END IF%>
Now, in a dieted DRW, you' d use something like this:
fp_sQry=" SELECT * FROM myTable WHERE (" & mySQL & " )"
Using this method, the user can enter something in any or all the fields. Does that help / make sense???
| |
|
|
|
contactcp
Posts: 167
From: None
Status: offline
|
RE: Desperate call for fp_sQry= help - 7/27/2002 16:34:45
Thank you very much!
I haven' t tested it yet as I have been in front of the computer for about 11 hours, I am tired and the wife is getting p*ss*d off. I will try it tomorrow and let you know how i get on.
Thanks again!
| |
|
|
|
contactcp
Posts: 167
From: None
Status: offline
|
RE: Desperate call for fp_sQry= help - 7/28/2002 13:34:46
I am getting the following error:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ' ()' .
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
I copied and pasted the code:
<%
DIM mySQL
IF Request.Form(" translatefrom" ) > " " THEN
mySQL = " (Translatefrom LIKE ' %::Translatefrom1::%' OR Translatefrom LIKE ' %::Translatefrom2::%' OR Translatefrom LIKE ' %::Translatefrom3::%' )"
END IF
IF Request.Form(" translateto" ) > " " THEN
mySQL = mySQL & " AND (Translateto LIKE ' %::Translateto2::%' OR Translateto LIKE ' %::Translateto2::%' )"
END IF
IF Request.Form(" area" ) > " " THEN
mySQL = mySQL & " AND (Area LIKE ' %::Area1::%' OR Area LIKE ' %::Area2::%' OR Area LIKE ' %::Area3::%' OR Area LIKE ' %::Area4::%' OR Area LIKE ' %::Area5::%' OR Area LIKE ' %::Area6::%' OR Area LIKE ' %::Area7::%' OR Area LIKE ' %::Area8::%' )"
END IF
IF Request.Form(" name" ) > " " THEN
mySQL = mySQL & " AND (Name LIKE ' %::Name::%' )"
END IF%>
and changed the DRW' s code to :
<%
fp_sQry=" SELECT * FROM Results WHERE (" & mySQL & " )"
fp_sDefault=" Translatefrom1=NULL&Translatefrom2=NULL&Translatefrom3=NULL&Translateto1=NULL&Translateto2=NULL&Area1=NULL&Area2=NULL&Area3=NULL&Area4=NULL&Area5=NULL&Area6=NULL&Area7=NULL&Area8=NULL&Name=NULL"
fp_sNoRecords=" <tr><td colspan=3 align=left width=" " 100%" " >Nobody matched you criteria.</td></tr>"
fp_sDataConn=" register"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=" "
fp_sMenuValue=" "
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
What am I doing wrong?
| |
|
|
|
rdouglass
Posts: 9224
From: Biddeford, ME USA
Status: offline
|
RE: Desperate call for fp_sQry= help - 7/31/2002 9:32:50
Sorry I didn' t get back quicker. Can you post the full code if its not too large?? I suspect we may have a typo somewhere.......
| |
|
|
|
rdouglass
Posts: 9224
From: Biddeford, ME USA
Status: offline
|
RE: Desperate call for fp_sQry= help - 7/31/2002 15:53:26
It has to do with ' mySQL' being empty the first time thru. I put a quick line in right after the DIM statement that seemed to take care of it. You' ll just need to customize your " No Records Found" response. Change the beginning of the script to:
<%
DIM mySQL
mySQL = " (Name = ' QQQQ' )"
.....
or something like that to put some kind of request in. I used something that will probably never return a record.
| |
|
|
|
contactcp
Posts: 167
From: None
Status: offline
|
RE: Desperate call for fp_sQry= help - 7/31/2002 16:40:27
That got rid of the error message but then I could only do a search in translate from, all the others returned no match even when I know they exist.
I suppose I could send the results to another page. What do you think?
| |
|
|
|
rdouglass
Posts: 9224
From: Biddeford, ME USA
Status: offline
|
RE: Desperate call for fp_sQry= help - 8/1/2002 9:32:15
Sorry, my logic has been a little off the past few days; been taking med' s and am not myself. However, I think I put the ' cart before the horse' . I was accomidating empty ' mySQL' too early. I moved it to the end of the script and seems to work better now. Try using this instead - notice the last 3 lines:
<%
DIM mySQL
IF Request.Form(" Translatefrom" ) > " " THEN
mySQL = " (Translatefrom1 LIKE ' %::Translatefrom::%' OR Translatefrom2 LIKE ' %::Translatefrom::%' OR Translatefrom3 LIKE ' %::Translatefrom::%' )"
END IF
IF Request.Form(" Translateto" ) > " " THEN
IF mySQL > " " THEN
mySQL = mySQL & " AND "
END IF
mySQL = mySQL & " (Translateto1 LIKE ' %::Translateto::%' OR Translateto2 LIKE ' %::Translateto::%' )"
END IF
IF Request.Form(" Area" ) > " " THEN
IF mySQL > " " THEN
mySQL = mySQL & " AND "
END IF
mySQL = mySQL & " (Area1 LIKE ' %::Area::%' OR Area2 LIKE ' %::Area::%' OR Area3 LIKE ' %::Area::%' OR Area4 LIKE ' %::Area::%' OR Area5 LIKE ' %::Area::%' OR Area6 LIKE ' %::Area::%' OR Area7 LIKE ' %::Area::%' OR Area8 LIKE ' %::Area::%' )"
END IF
IF Request.Form(" Name" ) > " " THEN
IF mySQL > " " THEN
mySQL = mySQL & " AND "
END IF
mySQL = mySQL & " (Name LIKE ' %::Name::%' )"
END IF
IF mySQL < " " THEN
mySQL = " Name = ' QQQQ' "
END IF
%>
| |
|
|
|
rdouglass
Posts: 9224
From: Biddeford, ME USA
Status: offline
|
RE: Desperate call for fp_sQry= help - 8/1/2002 14:12:39
I used the < because I was testing for an empty condition. ' Empty' is less than " " and anything that was added (to mySQL) due to the script would be greater than.....
EDIT: You may actually want to put some kind of message in a record with name ' QQQQ' so that the ' No Records Found' actually means that and not a starting point.....
< Message edited by rdouglass -- 7/31/2002 2:14:55 PM >
| |
|
 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 
I can' t get it. 
