Multiple keyword search on one database field

ASP Free Forums Sponsor:

October 9th, 2009, 10:48 AM

igidttam

Registered User

Join Date: Oct 2009

Posts: 26

Time spent in forums: 5 h 23 m 27 sec
Reputation Power: 0

Multiple keyword search on one database field

I have asp code to search one field in an Access database using one keyword. What I need to do now is create code to search one field in an Access database using multiple keywords [the users want to be able to search this field using up to 3 keywords]. I'm not certain how to do this.

Here's snippets of the code I'm using to search the field using one keyword. This code works perfectly ..

Code:

Dim rstSearch  ' ADO recordset
Dim strSearch  ' The text being looked for
strSearch = Request.QueryString("search")

Here's the text box for the keyword to be entered

Code:

<form action="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>" />
<p>

Here's the sql ..

Code:

sql= "SELECT * " _
		& "FROM [RSA_Candidates] " _
		& "WHERE resume_doc LIKE '%"& Replace(strSearch, "'", "''") & "%' " _
                  		& "ORDER BY lastname;"

Here's where I write out the results in a table ..

Code:

Response.Write "<table border=1 cellspacing=2>"
  Response.Write ("<tr>") 
   Response.Write ("<td>" & "<B>"  & "Resume: " & "</B>" & "</td>")
    Response.Write ("<td>" &  Highlight(rs("resume_doc"), StrSearch, "<font color='blue'>", "</font>") & "</td>")
     Response.Write ("</tr>")

Any ideas on how to do this same search using multiple keywords? Thanks for the help!

October 9th, 2009, 03:35 PM

Krandor

Contributing User

Join Date: Aug 2008

Posts: 154

Time spent in forums: 1 Day 17 h 15 m 13 sec
Reputation Power: 71

Quote:

Originally Posted by igidttam

I did this very thing last year after someone told me it was "impossible".

Change your SQL to this:

Code:

sql= SELECT * FROM [RSA_Candidates]  WHERE resume_doc LIKE % & strSearch01 & '%'
OR resume_doc LIKE % & strSearch02 & '%'
OR resume_doc LIKE % & strSearch03 & '%'
ORDER BY lastname;

The syntax isn't exactly right but you should get the idea. You have 3 variables and it will display all the records that match any of the search strings.

Comments on this post

bigmike1212 agrees!

micky agrees!

October 9th, 2009, 03:49 PM

bigmike1212

Option Explicit, baby.

Join Date: Jan 2009

Location: Neighborhood of Make-Believe

Posts: 554

Time spent in forums: 5 Days 17 h 46 m 54 sec
Reputation Power: 146

I think it will involve adding some OR's to your SQL.

But before that: What happens now if they don't enter a search term and submit the form?
ie Request.QueryString("search") = ""

What does the SQL return or do you error trap that somehow?

You might want

Code:

<form action="<%= strURL %>" method="get">
<p>Enter up to three keywords:</p>
<input name="search1" value="<%= strSearch1 %>" />
<input name="search2" value="<%= strSearch2 %>" />
<input name="search3" value="<%= strSearch3 %>" />
<p>

Code:

Dim strSearch1, strSearch2, strSearch3
strSearch1 = Request.QueryString("search1")
strSearch2 = Request.QueryString("search2")
strSearch3 = Request.QueryString("search3")

Code:

sql= "SELECT * " _
& "FROM [RSA_Candidates] " _
& "WHERE resume_doc LIKE '%"& Replace(strSearch1, "'", "''") & "%' OR " _
& "resume_doc LIKE '%"& Replace(strSearch2, "'", "''") & "%' OR " _
& "resume_doc LIKE '%"& Replace(strSearch3, "'", "''") & "%' " _
& "ORDER BY lastname;"

Can't fix your highlight function because I can't see it.

The above question about a null string is to figure out if you need someting like:

Code:

if Request.QueryString("search2") <> "" then
strSearch2 = Request.QueryString("search2")
end if

or some other way of dealing with 1, 2, or 3 strings any one of which could be null.

EDIT: Krandor beat me in!!

__________________
Fremen United! Folding@Home team ID 169647. All Fremen welcome.

October 12th, 2009, 11:07 AM

igidttam

Registered User

Join Date: Oct 2009

Posts: 26

Time spent in forums: 5 h 23 m 27 sec
Reputation Power: 0

The SQL part I have. I need to use AND because the user wants all 3 conditions met. My Highlight function works perfectly. Now my only problem is the Response.Write statement which is in a table. I Believe it's something like this..Can you give me the correct syntax?

Code:

Response.Write "<table border=1 cellspacing=2>"
  Response.Write ("<tr>") 
   Response.Write ("<td>" & "<B>"  & "Resume: " & "</B>" & "</td>")
    Response.Write ("<td>" &  Highlight(rs("resume_doc"), StrSearch,  StrSearch1, StrSearch2,"<font color='blue'>", "</font>") & "</td>")
     Response.Write ("</tr>")

October 12th, 2009, 11:26 AM

Bron

Contributing User

Join Date: Mar 2005

Location: Moscow, Russian Federation

Posts: 123

Time spent in forums: 1 Day 8 h 20 m 26 sec
Reputation Power: 42

Check the closing bracket.

October 12th, 2009, 12:03 PM

igidttam

Registered User

Join Date: Oct 2009

Posts: 26

Time spent in forums: 5 h 23 m 27 sec
Reputation Power: 0

Quote:

Originally Posted by Bron

Check the closing bracket.

I'm getting this error
Microsoft VBScript runtime error '800a01c2'
Wrong number of arguments or invalid property assignment: 'Highlight'

Here's the line that is causing the issue

Code:

Response.Write ("<td>" &  Highlight(rs("resume_doc"), StrSearch, StrSearch1, StrSearch2,  "<font color='blue'>", 

"</font>") & "</td>")

It appears I'm not assigning my Highlight function properly.
Here's the highlight function code

Code:

Function Highlight(strText, strFind, strBefore, strAfter)
	Dim nPos
	Dim nLen
	Dim nLenAll
	
	nLen = Len(strFind)
	nLenAll = nLen + Len(strBefore) + Len(strAfter) + 1

	Highlight = strText

	If nLen > 0 And Len(Highlight) > 0 Then
		nPos = InStr(1, Highlight, strFind, 1)
		Do While nPos > 0
			Highlight = Left(Highlight, nPos - 1) & _
				strBefore & Mid(Highlight, nPos, nLen) & strAfter & _
				Mid(Highlight, nPos + nLen)

			nPos = InStr(nPos + nLenAll, Highlight, strFind, 1)
		Loop
	End If
End Function

I'm not certain how to apply this highlight function so that all three keywords get highlighted in each record returned from the search.
Thank you for the help!

October 12th, 2009, 03:12 PM

Krandor

Contributing User

Join Date: Aug 2008

Posts: 154

Time spent in forums: 1 Day 17 h 15 m 13 sec
Reputation Power: 71

[QUOTE]
Here's the line that is causing the issue

Code:

Response.Write ("<td>" &  Highlight(rs("resume_doc"), StrSearch, StrSearch1, 
StrSearch2, "<font color='blue'>", "</font>") & "</td>")

You seem to have 5 variables going in to a function that is supposed to handle 4. That's the main problem.

It's not clear what you are trying to do but it seems like we can show you an easier way if you tell us exactly what is supposed to be displayed.

October 12th, 2009, 03:27 PM

igidttam

Registered User

Join Date: Oct 2009

Posts: 26

Time spent in forums: 5 h 23 m 27 sec
Reputation Power: 0

The resume_doc field contains a candidates resume. The user wants to search this field using 3 keywords to identify candidates in the database that meet the criteria of the keywords entered. When the results are returned to the screen, they want to see those 3 keywords highlighted in the resume_doc field for each record that is returned from the search. For example, if the user is looking for a candidate that has experience with HTML, SAS and PHP, they enter these 3 keywords in three different text boxes and then we search the database for records that have all 3 of these keywords in the resume_doc field. Those 3 words will be highlighted on the screen in the resume field for the records that were returned meeting that criteria. Does this make sense?

October 12th, 2009, 03:57 PM

Krandor

Contributing User

Join Date: Aug 2008

Posts: 154

Time spent in forums: 1 Day 17 h 15 m 13 sec
Reputation Power: 71

Quote:

Originally Posted by igidttam

Try this then:

Code:

Function Highlight(strText,strFind1,strFind2,strFind3,
sColor)

	strText = Replace(strText,strFind1,"<font color=" & sColor & ">" & strFind1 & "</font>")
	strText = Replace(strText,strFind2,"<font color=" & sColor & ">" & strFind2 & "</font>")
	strText = Replace(strText,strFind3,"<font color=" & sColor & ">" & strFind3 & "</font>")
	
	Highlight = strText
End Function

To call it, you use this:

Code:

<td>Response.Write ("<td>" &  Highlight(rs("resume_doc"), StrSearch1, StrSearch2, StrSearch3,"blue") & "</td>")

The first value is what comes from the results of the query. The next 3 are the search strings and the last one is the color you want to highlight.

#10

October 12th, 2009, 05:11 PM

igidttam

Registered User

Join Date: Oct 2009

Posts: 26

Time spent in forums: 5 h 23 m 27 sec
Reputation Power: 0

I made the change to the function and now getting this error...
Microsoft VBScript compilation error '800a03f2'

Expected identifier

/rsa/search_resume2.asp, line 53

Function Highlight(strText,strFind1,strFind2,strFind3,
------------------------------------------------------^

The highlight function looks like this now

Code:

Dim rstSearch  ' ADO recordset
Dim strSearch  ' The text being looked for
Dim strSearch1  ' The text being looked for
Dim strSearch2  ' The text being looked for
Dim strFind1
Dim strFind2
Dim strFind3

strSearch = Request.QueryString("search")
strSearch = Request.QueryString("search1")
strSearch = Request.QueryString("search2")


 Set conn = Server.CreateObject("ADODB.Connection")
 conn.open("RSA_262093")
%>

<%
'
Function Highlight(strText,strFind1,strFind2,strFind3,sColo  r)

strText = Replace(strText,strFind1,"<font color=" & sColor & ">" & strFind1 & "</font>")
	strText = Replace(strText,strFind2,"<font color=" & sColor & ">" & strFind2 & "</font>")
	strText = Replace(strText,strFind3,"<font color=" & sColor & ">" & strFind3 & "</font>")
	
	Highlight = strText
End Function

My sql looks like this

Code:

sql= "SELECT * " _
		& "FROM [RSA_Candidates] " _  
		& "WHERE resume_doc LIKE '%"& Replace(strSearch, "'", "''") & "%' AND " _
                & "resume_doc LIKE '%"& Replace(strSearch1, "'", "''") & "%' AND " _
                & "resume_doc LIKE '%"& Replace(strSearch2, "'", "''") & "%' " _
                  		& "ORDER BY lastname;"

And the response.write statement is

Code:

Response.Write "<table border=1 cellspacing=2>"
  Response.Write ("<tr>") 
   Response.Write ("<td>" & "<B>"  & "Resume: " & "</B>" & "</td>")
    Response.Write ("<td>" &  Highlight(rs("resume_doc"), StrSearch, StrSearch1, StrSearch2,"blue") & "</td>")
     Response.Write ("</tr>")

Not sure what I'm doing wrong? Any idea? Thanks for the help!

#11

October 12th, 2009, 05:18 PM

Krandor

Contributing User

Join Date: Aug 2008

Posts: 154

Time spent in forums: 1 Day 17 h 15 m 13 sec
Reputation Power: 71

This is just a wierdness of the formatting of the message, I tried to get around it but I couldn't.

Code:

Function Highlight(strText,strFind1,strFind2,strFind3,sColo  r)

The color variable is separated (due to this formatting glitch). It should be just one word. Once you do that, the Highlight function should work fine.

#12

October 13th, 2009, 10:28 AM

Bron

Contributing User

Join Date: Mar 2005

Location: Moscow, Russian Federation

Posts: 123

Time spent in forums: 1 Day 8 h 20 m 26 sec
Reputation Power: 42

I agree that now when all brackets are on place the fixing of typo with extra space should fix the problem. Let us kow if it dosen't.

#13

October 13th, 2009, 11:12 AM

igidttam

Registered User

Join Date: Oct 2009

Posts: 26

Time spent in forums: 5 h 23 m 27 sec
Reputation Power: 0

The page displays with the results but the keywords are not highlighted which leads me to believe my highlight function is not correct.
Here's the highlight function

Code:

<%
'
Function Highlight(strText, strFind1, strFind2, strFind3, sColor)

strText = Replace(strText,strFind1,"<font color=" & sColor & ">" & strFind1 & "</font>")
	strText = Replace(strText,strFind2,"<font color=" & sColor & ">" & strFind2 & "</font>")
	strText = Replace(strText,strFind3,"<font color=" & sColor & ">" & strFind3 & "</font>")
	
	Highlight = strText   
End Function
 


'*************************************************  ****************************
%>

Here's the code where the highlight function is used in the Response.write line

Code:

Response.Write ("<td>" &  Highlight(rs("resume_doc"), StrSearch, StrSearch1, StrSearch2,"blue") & "</td>")

The user wants all 3 keywords highlighted when the data is returned to the page. Do I have my brackets in the wrong place? Thank you again for the continued help & support. I appreciate it. Just need to get this last part figured out. Thanks!

#14

October 13th, 2009, 01:49 PM

Krandor

Contributing User

Join Date: Aug 2008

Posts: 154

Time spent in forums: 1 Day 17 h 15 m 13 sec
Reputation Power: 71

Quote:

Originally Posted by igidttam

The page displays with the results but the keywords are not highlighted which leads me to believe my highlight function is not correct.
Here's the highlight function

Code:

<%
'
Function Highlight(strText, strFind1, strFind2, strFind3, sColor)

strText = Replace(strText,strFind1,"<font color=" & sColor & ">" & strFind1 & "</font>")
	strText = Replace(strText,strFind2,"<font color=" & sColor & ">" & strFind2 & "</font>")
	strText = Replace(strText,strFind3,"<font color=" & sColor & ">" & strFind3 & "</font>")
	
	Highlight = strText   
End Function
 


'*************************************************  ****************************
%>

Here's the code where the highlight function is used in the Response.write line

Code:

Response.Write ("<td>" &  Highlight(rs("resume_doc"), StrSearch, StrSearch1, StrSearch2,"blue") & "</td>")

Your syntax seems to be correct. I tried it myself and it worked without problem.

I assume that your resume_doc field is a memo field and not a text field. I don't know if Replace works on memo variables. I never use them so I don't have anything to test.

If any of the gurus know this, please chime in.

The best I can up with at this point is to add a line in the Highlight function:

Code:

strText = cStr(strText)
strText = Replace(strText,strFind1,"<font color=" & strColor & ">" & strFind1 & "</font>")
strText = Replace(strText,strFind2,"<font color=" & strColor & ">" & strFind2 & "</font>")
strText = Replace(strText,strFind3,"<font color=" & strColor & ">" & strFind3 & "</font>")

The first line will convert strText variable which will make the rest easier. Replace the contents of the Highlight function with the above code.

Hopefully that will solve your problem.

#15

October 13th, 2009, 02:23 PM

igidttam

Registered User

Join Date: Oct 2009

Posts: 26

Time spent in forums: 5 h 23 m 27 sec
Reputation Power: 0

Yes the resume_doc field is a memo field in an Access database. I made the changes you suggested to the highlight function and nothing is highlighted now.

Here's the highlight function

Code:

'
Function Highlight(strText, strFind1, strFind2, strFind3, strColor)

strText = cStr(strText)
strText = Replace(strText,strFind1,"<font color=" & strColor & ">" & strFind1 & "</font>")
strText = Replace(strText,strFind2,"<font color=" & strColor & ">" & strFind2 & "</font>")
strText = Replace(strText,strFind3,"<font color=" & strColor & ">" & strFind3 & "</font>")

Highlight = strText   
End Function

I also noticed, when the results are displayed, it looks like it's only searching on one of the 3 keywords. Here's the sql code ..

Code:

 sql= "SELECT * " _
		& "FROM [RSA_Candidates] " _  
		& "WHERE resume_doc LIKE '%"& Replace(strSearch, "'", "''") & "%' AND " _
                & "resume_doc LIKE '%"& Replace(strSearch1, "'", "''") & "%' AND " _
                & "resume_doc LIKE '%"& Replace(strSearch2, "'", "''") & "%' " _
                  		& "ORDER BY lastname;"

I'm pretty sure the sql is correct.

Here's the response.write statement ..

Code:

Response.Write ("<td>" &  Highlight(rs("resume_doc"), StrSearch, StrSearch1, StrSearch2,"blue") & "</td>")

What else could be wrong with my code? Thanks for helping me with this....

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: