keyword search (Full Version)

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



Message

meridian -> keyword search (4/4/2005 22:47:04)

Hi all,

I am putting together a page that will allow employers to view profiles of potential candidates, searchable by position title and then use check boxes to enquire further.
The page works well with the exception of a limited search function.

The trial data includes position titles of "Plant or General Manager" and "Operations Manager"

When I enter manager both profiles return.
When I enter Operations Manager the Operations Manager profile returns
When I enter Plant Manager I receive no records returned

I am using the LIKE function.
How do I set it up so two or more key words entered will bring back results even if it is not an exact match. (eg. When plant manager is entered the Plant or General Manager profile returns).

Trail page can be viewed at meridiansearch trial page

Thanks in advance,

Larry





Spooky -> RE: keyword search (4/4/2005 23:00:32)

How does the SQL look for that search page?



meridian -> RE: keyword search (4/4/2005 23:04:05)

fp_sQry="SELECT * FROM Results WHERE ([position] LIKE '%::position::%')"
fp_sDefault="position="

Larry



Spooky -> RE: keyword search (4/5/2005 0:42:07)

Ok - "Plant Manager" is not a match.

"LIKE" searches for *word1 word2* (either side of the input)
You are expecting *word1*word2* (both terms exist anywhere)

example - if you enter "Plant%Manager" , the result will be correct.
What really needs to happen, is to replace the input spaces with a wildcard such as :

Position = replace(request.form("position")," ","%")
fp_sQry="SELECT * FROM Results WHERE ([position] LIKE '%"&position&"%')"





meridian -> RE: keyword search (4/5/2005 9:08:01)

Thanks Spooky,

Worked like a charm in providing the search results.

However when the page is entered for the first time I receive the error message

Database Results Error: mismatched parameter delimiters

This also appears if you submit without entering keywords.

Larry



Spooky -> RE: keyword search (4/5/2005 15:12:26)

You would need to look at providing defaults - if you want all records returned first off, you may do something like so :

Position = replace(request.form("position")," ","%")
If Position &""="" then Position = "%"



meridian -> RE: keyword search (4/5/2005 16:10:20)

Sorry Spooky,

Getting the same error when using this code

Position = replace(request.form("position")," ","%")
If Position &""="" then Position = "%"
fp_sQry="SELECT * FROM Results WHERE ([position] LIKE '%"&position&"%')"
fp_sDefault="position="


Do you mean replace the fp_sDefault="position=" with If Position &""="" then Position = "%"
I have tried this as well but same result

Thanks



Spooky -> RE: keyword search (4/5/2005 16:22:57)

What happens when you do this as well? (to view the SQL)

fp_sQry="SELECT * FROM Results WHERE ([position] LIKE '%"&position&"%')"
response.write fp_sQry



meridian -> RE: keyword search (4/5/2005 16:47:51)

This is the new error I'm getting

SELECT * FROM Results WHERE ([position] LIKE '%%%')Database Results Error: mismatched parameter delimiters

From this code

Position = replace(request.form("position")," ","%")
If Position &""="" then Position = "%"
fp_sQry="SELECT * FROM Results WHERE ([position] LIKE '%"&position&"%')"
response.write fp_sQry
fp_sDefault="position="



Spooky -> RE: keyword search (4/5/2005 17:05:16)

I believe thats a reaction to the new include files

Using this code :

Position = replace(replace(request.form("position"),"'","''")," ","%")
If Position &""="" then Position = "%"
fp_sQry="SELECT * FROM Results WHERE ([position] LIKE '%"&position&"%')"

AND using the old include files (from the DRW error FAQ thread at the top of this forum) should solve that



meridian -> RE: keyword search (4/6/2005 10:57:11)

No Luck

Replaced the includes file with the old and have used the following code:

<!--#include file="../_fpclass/fpdblib.inc"-->
<% 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 %>
<%
Position = replace(replace(request.form("position"),"'","''")," ","%")
If Position &""="" then Position = "%"
fp_sQry="SELECT * FROM Results WHERE ([position] LIKE '%"&position&"%')"
fp_sNoRecords="<tr><td colspan=3 align=left width=""100%"">No records match your search. Please try again</td></tr>"
fp_sDataConn="Trialdat"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&name=202&city=202&province=202&email=202&phone=202&position=202&desiredposition=202&description=203&Remote_computer_name=202&User_name=202&Browser_type=202&Timestamp=135&"
fp_iDisplayCols=3
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
<!--#include file="../_fpclass/fpdbrgn1.inc"-->


Results

Database Results Error: mismatched parameter delimiters

FYI - I did a google and yahoo search using the above error and it came back with many, many sites that have the same problem but have not fixed - YIKES!



BeTheBall -> RE: keyword search (4/6/2005 12:43:26)

What if you replace:

Position = replace(replace(request.form("position"),"'","''")," ","%")
If Position &""="" then Position = "%"
fp_sQry="SELECT * FROM Results WHERE ([position] LIKE '%"&position&"%')"

with simply:

Position = replace(request.form("position"),"'","''")
fp_sQry="SELECT * FROM Results WHERE ([position] LIKE '%"&position&"%')"



meridian -> RE: keyword search (4/6/2005 13:08:52)

Thanks Duane,

Still getting the same message.
I have been looking at a thread that you had helped out with on an apostraphe issue whereas the same message was being displayed.
I was hoping it might hold some answers. The thread is Apostraphe Issues

Thanks, Larry



BeTheBall -> RE: keyword search (4/6/2005 15:07:30)

My bad. I had forgotten what the original question was. [&:]

Having reread it, what I was thinking would definitely not work for you.



Spooky -> RE: keyword search (4/6/2005 15:28:35)

Ok- what about this ?

Position = replace(replace(request.form("position"),"'","''")," ","%")
If Position &""="" then Position = "%"
sQry = "SELECT * FROM Results WHERE ([position] LIKE '%"&position&"%')"
fp_sQry = sQry



meridian -> RE: keyword search (4/6/2005 16:19:35)

Sorry Spooky,

Still the same.
Grasping at straws but would the name position be causing any of this? I do know that it is a reserved word.



Spooky -> RE: keyword search (4/6/2005 16:26:40)

No, that should be correct []
To clarify, this code does work with no error - yes?

fp_sQry="SELECT * FROM Results WHERE ([position] LIKE '%Plant%Manager%')"



BeTheBall -> RE: keyword search (4/6/2005 17:08:09)

This is perhaps the most creative thing I have ever tried, but in theory it works. Try this:

Assuming this is the last code you tried:

Position = replace(replace(request.form("position"),"'","''")," ","%")
If Position &""="" then Position = "%"
sQry = "SELECT * FROM Results WHERE ([position] LIKE '%"&position&"%')"
fp_sQry = sQry

Replace the above with:

<%
varString = trim(replace(request.form("position"), "'","''"))
arrString = Split(varString, " ")
mySQL = "SELECT * FROM Results WHERE [position] LIKE '%"&arrString(0)&"%' "
If Ubound(arrString)> 0 Then
For i = 1 to Ubound(arrString)
mySQL = mySQL & " AND fldname LIKE '%"&arrString(i)&"%' "
Next
End If
fp_sQry = mySQL



meridian -> RE: keyword search (4/6/2005 17:10:38)

fp_sQry="SELECT * FROM Results WHERE ([position] LIKE '%Plant%Manager%')"

Yes the above works with no error.



meridian -> RE: keyword search (4/6/2005 17:24:01)

Hi Duane,

When the field is blank this returns error as:

Microsoft VBScript runtime error '800a0009'
Subscript out of range: '[number: 0]'

/traditional/trial.asp, line 154


When you fill in the search field with manager and submit, all manager positions return ok


When you fill in the search field with plant manager and submit the following error returns:

Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine



BeTheBall -> RE: keyword search (4/6/2005 17:30:59)

Sorry forgot to change fldname to [position] in one instance. I also have added an If/Then to address an empty form field. See where this one gets you:

<%
If trim(replace(request.form("position")&"" <> ""
Then
varString = trim(replace(request.form("position"), "'","''"))
arrString = Split(varString, " ")
mySQL = "SELECT * FROM Results WHERE [position] LIKE '%"&arrString(0)&"%' "
If Ubound(arrString)> 0 Then
For i = 1 to Ubound(arrString)
mySQL = mySQL & " AND [position] LIKE '%"&arrString(i)&"%' "
Next
End If
Else
mySQL = "SELECT * FROM Results"
fp_sQry = mySQL



meridian -> RE: keyword search (4/6/2005 17:49:53)

Error now reads:

Microsoft VBScript compilation error '800a03ee'

Expected ')'

/traditional/trial.asp, line 152

If trim(replace(request.form("position")&"" <> ""
-------------------------------------------------^



BeTheBall -> RE: keyword search (4/6/2005 18:12:26)

Change:

If trim(replace(request.form("position")&"" <> ""

to

If trim(replace(request.form("position"))&"" <> ""



meridian -> RE: keyword search (4/6/2005 18:37:37)

Same error returns:

Microsoft VBScript compilation error '800a03ee'

Expected ')'

/traditional/trial.asp, line 152

If trim(replace(request.form("position"))&"" <> ""
--------------------------------------------------^


I place another bracket and received this error:


Microsoft VBScript compilation error '800a03f9'

Expected 'Then'

/traditional/trial.asp, line 152

If trim(replace(request.form("position")))&"" <> ""
---------------------------------------------------^



BeTheBall -> RE: keyword search (4/6/2005 18:43:18)

Sorry, I am cutting and pasting from a test page:

If trim(request.form("position")&"" <> "" Then



Spooky -> RE: keyword search (4/6/2005 18:49:24)

If trim(request.form("position"))&"" <> "" Then



BeTheBall -> RE: keyword search (4/6/2005 18:56:03)

[:@]

I vote for the elimination of () and end if's in all code. [:D]



meridian -> RE: keyword search (4/6/2005 20:14:09)

With the code:

<% end if %>
<%
If trim(request.form("position"))&"" <> "" Then
varString = trim(replace(request.form("position"), "'","''"))
arrString = Split(varString, " ")
mySQL = "SELECT * FROM Results WHERE [position] LIKE '%"&arrString(0)&"%' "
If Ubound(arrString)> 0 Then
For i = 1 to Ubound(arrString)
mySQL = mySQL & " AND [position] LIKE '%"&arrString(i)&"%' "
Next
End If
Else
mySQL = "SELECT * FROM Results"
fp_sQry = mySQL
fp_sNoRecords="<tr><td colspan=3 align=left width=""100%"">No records match your search. Please try again</td></tr>"
fp_sDataConn="Trialdat"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&name=202&city=202&province=202&email=202&phone=202&position=202&desiredposition=202&description=203&Remote_computer_name=202&User_name=202&Browser_type=202&Timestamp=135&"
fp_iDisplayCols=3
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>



Now displays error:

Microsoft VBScript compilation error '800a03f6'

Expected 'End'

E:\ACCOUNTS\MERIDIAN\TRADITIONAL\../_fpclass/fpdbrgn2.inc, line 62



Spooky -> RE: keyword search (4/6/2005 20:19:23)

Else
mySQL = "SELECT * FROM Results"
End if



meridian -> RE: keyword search (4/6/2005 22:00:23)

EUREKA!!!!
Thank-you Spooky and Duane - Working Great[:D]


For anyone looking to resolve the same problem, the following is the final version.
Thanks again to Spooky and Duane!

<% end if %>
<%
If trim(request.form("position"))&"" <> "" Then
varString = trim(replace(request.form("position"), "'","''"))
arrString = Split(varString, " ")
mySQL = "SELECT * FROM Results WHERE [position] LIKE '%"&arrString(0)&"%' "
If Ubound(arrString)> 0 Then
For i = 1 to Ubound(arrString)
mySQL = mySQL & " AND [position] LIKE '%"&arrString(i)&"%' "
Next
End If
Else
mySQL = "SELECT * FROM Results"
End If
fp_sQry = mySQL
fp_sNoRecords="<tr><td colspan=3 align=left width=""100%"">No records match your search. Please try again</td></tr>"
fp_sDataConn="Trialdat"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&name=202&city=202&province=202&email=202&phone=202&position=202&desiredposition=202&description=203&Remote_computer_name=202&User_name=202&Browser_type=202&Timestamp=135&"
fp_iDisplayCols=3
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625