Desperate call for fp_sQry= help (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

contactcp -> 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!



bobby -> RE: Desperate call for fp_sQry= help (7/26/2002 16:23:25)

Are you trying to search all of these at one time? Or do you want to be able to select which one to search under?

Also, is the form asking for input? If so, you shouldn' t place the value=" " in the form field... ie: 
<input TYPE=" TEXT"  NAME=" Translatefrom"  size=" 20" >

instead of: 
<input TYPE=" TEXT"  NAME=" Translatefrom"  VALUE=" <%=Request(" Translatefrom1" )%>"  size=" 20" >



contactcp -> RE: Desperate call for fp_sQry= help (7/26/2002 17:17:29)

I want it to be able to search

language from = search in database under fields (languagefrom1,languagefrom2,languagefrom3) for a match.

e.g. If I typed in English in the text box it would provide me with everyone in the database that has English under field (languagefrom1 or languagefrom2 or languagefrom3)

And then narrow the results by finding someone who matches the (language from) and (language to). Not forgetting that if I typed German in (language to) this would be looking for anyone that has German under fields (languagefrom1 or languagefrom2).

And then narrow the results even further if required with the other two search fields.

Does this make any sense? [&:]



rdouglass -> 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....



contactcp -> RE: Desperate call for fp_sQry= help (7/27/2002 13:13:26)

That hurt Rdouglas [:(]

I can sort of see what is going on but [:@] I can' t get it.

Also, it seems that you have 5 search fields and five fileds to search through.
I have 4 search fields and 14 field to search through. Or have I just missed the idea here.

I have seen this type of search on many websites, so I can' t believe it is so difficult to do.



rdouglass -> RE: Desperate call for fp_sQry= help (7/27/2002 13:44:36)

Sorry [:o] 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 -> 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 -> 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???



contactcp -> RE: Desperate call for fp_sQry= help (7/27/2002 15:58:35)

Someone understands me! [:)]

The only thing is that with the links I provided you with, you only have to enter data in one of the fields. Entering data in more than one, narrows the results and thats what I need.

e.g. people would only enter text in the name field if they knew the translator they were looking for.

The only reason I changed the search name from " translatefrom1" to " translatefrom" is because i had followed an explination you had provided in a previous post:

quote:

Then on your search form, delete the Last_Name textbox and re-name First_Name box to Name. This would search both fields for text contained in the Name textbox. This is actually a " contains" query. If you want an exact name match, your query would look something like:


Am I making it too difficult now?



rdouglass -> 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 -> 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 -> 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 -> 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.......



contactcp -> RE: Desperate call for fp_sQry= help (7/31/2002 12:04:36)

I thought you had given up on me. [;)]

I have copied and pasted the whole page 

<% 
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 ' %::Translateto1::%'  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%>

<html>
<head>
</head>

<body>

<form BOTID=" 0"  METHOD=" POST"  ACTION=" trans.asp" >
  <table BORDER=" 0" >
    <tr>
      <td><b>Translatefrom1</b></td>
      <td><input TYPE=" TEXT"  NAME=" Translatefrom"  size=" 20" ></td>
    </tr>
    <tr>
      <td><b>Translateto1</b></td>
      <td><input TYPE=" TEXT"  NAME=" Translateto"  size=" 20" ></td>
    </tr>
      <td><b>Area1</b></td>
      <td><input TYPE=" TEXT"  NAME=" Area"  size=" 20" ></td>
    </tr>
    <tr>
      <td><b>Name</b></td>
      <td><input TYPE=" TEXT"  NAME=" Name"  size=" 20" ></td>
    </tr>
  </table>
  <br>
  <input TYPE=" Submit" ><input TYPE=" Reset" ><!--webbot bot=" SaveAsASP"  CLIENTSIDE
  SuggestedExt=" asp"  PREVIEW="  "  -->
  <p> </p>
</form>
<table width=" 100%" >
  <thead>
    <tr>
      <td><b>Name</b></td>
      <td><b>Email</b></td>
      <td><b>Country</b></td>
    </tr>
  </thead>
  <tbody>
<!--#include file=" _fpclass/fpdblib.inc" -->
<%
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
%>
<!--#include file=" _fpclass/fpdbrgn1.inc" -->
    <tr>
      <td><%=FP_FieldVal(fp_rs," Name" )%>
      </td>
      <td><%=FP_FieldVal(fp_rs," Email" )%>      
      </td>
      <td><%=FP_FieldVal(fp_rs," Country" )%>
      </td>
    </tr>
<!--#include file=" _fpclass/fpdbrgn2.inc" -->
  </tbody>
</table>

</body>

</html>



rdouglass -> RE: Desperate call for fp_sQry= help (7/31/2002 13:12:59)

Nope, no typo' s - It' s just me being an airhead![:o][:o]

I mixed up my numbering. I was putting the 1,2,3,... in the wrong place in mySQL. I' ve also added some blank field checking. This should work better....

<%
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%>

With the fix and addition, you should be able to search any / all fields now....



contactcp -> RE: Desperate call for fp_sQry= help (7/31/2002 15:03:14)

That worked a treat! Thank you very much for all your time!!![:D]

One thing, everytime I type the address to the asp page that has the search, the error message that I mentioned before keeps coming up: 

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


But when I type some things into the form and do a search it seems to work. However, when i type the addess into the address bar again and press enter the error is shown again.

Total guess, but is this because it is stored in the cache or do you think there still might be a problem? How can I get rid of it?



rdouglass -> 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 -> 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 -> 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
%>



contactcp -> RE: Desperate call for fp_sQry= help (8/1/2002 13:39:02)

rdouglass...

Thank you, thank you, thank you,

I am [:)][:D] and[:p], we got there in the end. Or should I say you did!

I posted my first post in a couple of forums, because i was desperate, and was told it was not possible. I knew it was because of the sites that I had seen with it.

Everything works fine now!!!

I have been trying to follow the things you have taught me over the last few post and I was just wondering why you did this < insted of >

IF mySQL < " " THEN
mySQL = " Name = ' QQQQ' "
END IF

instead of

IF mySQL > " " THEN
mySQL = " Name = ' QQQQ' "
END IF

Like the rest.

Where would beginners like me be without forums like this and people like you. Thanks again [;)]



rdouglass -> 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.....



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.125