|
| |
|
|
mar0364
Posts: 3246
Joined: 4/5/2002
From: Florida, US
Status: offline
 |
Search Help - 10/26/2005 14:34:52
I'm trying to change the querry to include the code. I've listed the whole page here incase I'm off mark on this. But I think my problem is the first code area below. How do change this dynamic query to include the code.
If strSearch = "" then
arrSearchWords=Split(" "," ")
Else
arrSearchWords=Split(strSearch," ")
End If
strSQL="SELECT * FROM Links WHERE code='"& code &"AND' "
For f = 0 to Ubound(arrSearchWords)
if f > 0 then strSQL = strSQL & "AND "
strSQL = strSQL & "[title] LIKE '%" & arrSearchWords(f) & "%' "
next
strSQL = strSQL & " OR "
For f1 = 0 to Ubound(arrSearchWords)
if f1 > 0 then strSQL = strSQL & "AND "
strSQL = strSQL & "[describe] LIKE '%" & arrSearchWords(f1) & "%' "
next
strSQL = strSQL & " OR "
For f1 = 0 to Ubound(arrSearchWords)
if f1 > 0 then strSQL = strSQL & "AND "
strSQL = strSQL & "[created] LIKE '%" & arrSearchWords(f1) & "%' "
next
Whole Page
<%
Dim strURL
Dim cnnSearch ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath ' path to our Access database (*.mdb) file
Dim strSQL
strURL = Request.ServerVariables("URL")
code = (request.querystring("rcv_cmpnum"))
strSearch = Request.QueryString("search")
strSearch = Replace(strSearch, "'", "''")
%>
<form action="<%= strURL %>" method="get">
<input type="Hidden" name="code" value="<%= code %>">
<input name="search" value="<%= strSearch %>" size="20" />
<input type="submit" value="Search" />
</form>
<%
If strSearch <> "" Then
strDBPath = Server.MapPath("data/mfl.mdb")
Set cnnSearch = Server.CreateObject("ADODB.Connection")
cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
Function Highlight(myString)
myKeywords = Request.form("Search")
code = Request.form("code")
arrmyKeywords = Split(myKeywords, " ")
myOutput = myString
For i = 0 to Ubound(arrmyKeywords)
myOutput = Replace(myOutput, arrmyKeywords(i), "<span style='background-color:yellow'>"&arrmyKeywords(i)&"</span>")
Next
Response.write myOutput
End Function
If strSearch = "" then
arrSearchWords=Split(" "," ")
Else
arrSearchWords=Split(strSearch," ")
End If
strSQL="SELECT * FROM Links WHERE code='"& code &"AND' "
For f = 0 to Ubound(arrSearchWords)
if f > 0 then strSQL = strSQL & "AND "
strSQL = strSQL & "[title] LIKE '%" & arrSearchWords(f) & "%' "
next
strSQL = strSQL & " OR "
For f1 = 0 to Ubound(arrSearchWords)
if f1 > 0 then strSQL = strSQL & "AND "
strSQL = strSQL & "[describe] LIKE '%" & arrSearchWords(f1) & "%' "
next
strSQL = strSQL & " OR "
For f1 = 0 to Ubound(arrSearchWords)
if f1 > 0 then strSQL = strSQL & "AND "
strSQL = strSQL & "[created] LIKE '%" & arrSearchWords(f1) & "%' "
next
response.write strSQL
Set rstSearch = cnnSearch.Execute(strSQL)
%>
<table border="0" cellpadding="2" width="467" style="border-collapse: collapse" cellspacing="0">
<%
if rstSearch.EOF then
response.write "<b>If you did not find the document based on your word search or phrase, please email the <a href='mailto:receiverwebmaster@fldfs.com'>webmaster</a> with: the word or phrase you used along with a description of the item you searched for.</b>"
Else
end if
Do While Not rstSearch.EOF
%>
<tr>
<th align="left" valign="top" width="75">Title</th>
<td width="384"><a href="<%= rstSearch.Fields("URL").Value %><%= rstSearch.Fields("auto_id").Value %><%= rstSearch.Fields("file_name").Value %>"><%= rstSearch.Fields("title").Value %></a> </td>
</tr>
<tr>
<th align="left" valign="top" width="75" height="22">Description</th>
<td width="384" height="22"><%= rstSearch.Fields("Describe").Value %> </td>
</tr>
<tr><td width="75"></td></tr>
<%
rstSearch.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>
_____________________________
Easy Web
“Stay committed to your decisions, but stay flexible in your approach.”
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Search Help - 10/26/2005 15:06:41
quote:
strSQL="SELECT * FROM Links WHERE code='"& code &"AND' "
Don't know about the rest but I do see 1 problem right there:
strSQL="SELECT * FROM Links WHERE code='"& code &"' AND "
(Moved your apostrophe.)
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Search Help - 10/26/2005 15:13:31
quote:
strSQL="SELECT * FROM Links WHERE code='"& code &"AND' "
For f = 0 to Ubound(arrSearchWords)
if f > 0 then strSQL = strSQL & "AND "
strSQL = strSQL & "[title] LIKE '%" & arrSearchWords(f) & "%' "
next
Another look and I see 2 more issues:
strSQL="SELECT * FROM Links WHERE code='"& code &"' ("
For f = 0 to Ubound(arrSearchWords)
if f > 0 then strSQL = strSQL & " ("
strSQL = strSQL & "[title] LIKE '%" & arrSearchWords(f) & "%') AND"
next
strSQL = left(strSQL,len(strSQL) - 3)
...
You had an extra "AND" in there. also when using AND's and OR's, I always try to group them logically. Try doing similar things with the next set. Then Resonse.write the SQL and you'll see what I mean.
What were looking for is something like:
SELECT * FROM myTable WHERE (code = 1234) AND (([title] LIKE '%word1%') AND ([title] LIKE '%word2%')) OR (([describe] LIKE '%word1%') AND ([describe] LIKE '%word2%'))
See what I'm doing?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mar0364
Posts: 3246
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: Search Help - 10/27/2005 10:20:15
Thanks! I feel like I'm getting closer but still having a problem. This is the response.write I get after making the changes.
Rich
SELECT * FROM Links WHERE code='' ([title] LIKE '%fir%') OR [describe] LIKE '%fir%' OR [created] LIKE '%fir%'
Microsoft JET Database Engine error '80040e14'
Invalid use of '.', '!', or '()'. in query expression 'code='' ([title] LIKE '%fir%') OR [describe] LIKE '%fir%' OR [created] LIKE '%fir%''.
/267/user_intro.asp, line 196
Code
If strSearch = "" then
arrSearchWords=Split(" "," ")
Else
arrSearchWords=Split(strSearch," ")
End If
strSQL="SELECT * FROM Links WHERE code='"& code &"' ("
For f = 0 to Ubound(arrSearchWords)
if f > 0 then strSQL = strSQL & "( "
strSQL = strSQL & "[title] LIKE '%" & arrSearchWords(f) & "%')AND"
next
strSQL = left(strSQL,len(strSQL)-3)
strSQL = strSQL & " OR "
For f1 = 0 to Ubound(arrSearchWords)
if f1 > 0 then strSQL = strSQL & "AND "
strSQL = strSQL & "[describe] LIKE '%" & arrSearchWords(f1) & "%' "
next
strSQL = strSQL & " OR "
For f1 = 0 to Ubound(arrSearchWords)
if f1 > 0 then strSQL = strSQL & "AND "
strSQL = strSQL & "[created] LIKE '%" & arrSearchWords(f1) & "%' "
next
response.write strSQL
_____________________________
Easy Web
“Stay committed to your decisions, but stay flexible in your approach.”
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Search Help - 10/27/2005 10:32:54
quote:
Links WHERE code='"& code &"' ("
I think it's failing right there.
Wha is the end result query you're looking for? Is it code=something AND blah, blah...
See what happens if you do this:
strSQL="SELECT * FROM Links WHERE code='"& code &"' AND ("
also, you'll need to come to grips with grouping your AND and OR's like this:
strSQL="SELECT * FROM Links WHERE code='"& code &"' ("
For f = 0 to Ubound(arrSearchWords)
if f > 0 then strSQL = strSQL & " ("
strSQL = strSQL & "([title] LIKE '%" & arrSearchWords(f) & "%') AND"
next
strSQL = left(strSQL,len(strSQL) - 3) & ")"
You'll need to do that for *each* section (describe, etc.)
Can you make those changes and Response.write the resulting SQL?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mar0364
Posts: 3246
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: Search Help - 10/27/2005 10:44:56
I'm looking for a query that will limit it's scope based on the code.
So if the code is 123 I want the search to only examine records that have 123 as the code. There may be 50 other codes in the table. Follow me?
Query I want
SELECT * FROM Links WHERE [code]=123 '' ([title] LIKE '%firs%') OR [describe] LIKE '%firs%' OR [created] LIKE '%firs%'
< Message edited by mar0364 -- 10/27/2005 10:54:07 >
_____________________________
Easy Web
“Stay committed to your decisions, but stay flexible in your approach.”
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Search Help - 10/27/2005 11:33:18
What does this code do for you?
If trim(strSearch&"") = "" then
strSQL="SELECT * FROM Links WHERE code='"& code &"'"
Else
arrSearchWords=Split(strSearch," ")
strSQL="SELECT * FROM Links WHERE code='"& code &"' AND ("
For f = 0 to Ubound(arrSearchWords)
strSQL = strSQL & "([title] LIKE '%" & arrSearchWords(f) & "%') AND "
next
strSQL = left(strSQL,len(strSQL)-5) & ") OR ("
For f = 0 to Ubound(arrSearchWords)
strSQL = strSQL & "([describe] LIKE '%" & arrSearchWords(f) & "%') AND "
next
strSQL = left(strSQL,len(strSQL)-5) & ") OR ("
For f = 0 to Ubound(arrSearchWords)
strSQL = strSQL & "([created] LIKE '%" & arrSearchWords(f) & "%') AND "
next
strSQL = left(strSQL,len(strSQL)-5) & ")"
End If
response.write strSQL
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mar0364
Posts: 3246
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: Search Help - 10/27/2005 11:44:49
1. Thanks for help.
2. I get the following response.write
SELECT * FROM Links WHERE code='' AND (([title] LIKE '%first%')) OR (([describe] LIKE '%first%')) OR (([created] LIKE '%first%'))
It's not grabing the code.
_____________________________
Easy Web
“Stay committed to your decisions, but stay flexible in your approach.”
| |
|
|
|
mar0364
Posts: 3246
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: Search Help - 10/27/2005 12:47:06
I'm getting very near.
If strSearch = "" then
arrSearchWords=Split(" "," ")
Else
arrSearchWords=Split(strSearch," ")
End If
strSQL="SELECT * FROM Links WHERE code='"& Request.querystring("code") &"'"
For f = 0 to Ubound(arrSearchWords)
if f > 0 then strSQL = strSQL & "AND "
strSQL = strSQL & "[title] LIKE '%" & arrSearchWords(f) & "%' "
next
strSQL = left(strSQL,len(strSQL)-3)
strSQL = strSQL & " OR "
For f1 = 0 to Ubound(arrSearchWords)
if f1 > 0 then strSQL = strSQL & "AND "
strSQL = strSQL & "[describe] LIKE '%" & arrSearchWords(f1) & "%' "
next
strSQL = strSQL & " OR "
For f1 = 0 to Ubound(arrSearchWords)
if f1 > 0 then strSQL = strSQL & "AND "
strSQL = strSQL & "[created] LIKE '%" & arrSearchWords(f1) & "%' "
next
response.write strSQL
Error
SELECT * FROM Links WHERE code='288'[title] LIKE '%f OR [describe] LIKE '%f%' OR [created] LIKE '%f%'
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'code='288'[title] LIKE '%f OR [describe] LIKE '%f%' OR [created] LIKE '%f%''.
/267/user_intro.asp, line 209
_____________________________
Easy Web
“Stay committed to your decisions, but stay flexible in your approach.”
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Search Help - 10/27/2005 12:55:09
Did you try using the Request.querystring("code") in the code I posted? That should have all those other fixes for the parens, AND's, OR's, etc.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mar0364
Posts: 3246
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: Search Help - 10/27/2005 13:06:36
I did and it gave me the same thing.
If trim(strSearch&"") = "" then
strSQL="SELECT * FROM Links WHERE code='"& Request.querystring(code) &"'"
Else
arrSearchWords=Split(strSearch," ")
strSQL="SELECT * FROM Links WHERE code='"& Request.querystring(code) &"' AND ("
For f = 0 to Ubound(arrSearchWords)
strSQL = strSQL & "([title] LIKE '%" & arrSearchWords(f) & "%') AND "
next
strSQL = left(strSQL,len(strSQL)-5) & ") OR ("
For f = 0 to Ubound(arrSearchWords)
strSQL = strSQL & "([describe] LIKE '%" & arrSearchWords(f) & "%') AND "
next
strSQL = left(strSQL,len(strSQL)-5) & ") OR ("
For f = 0 to Ubound(arrSearchWords)
strSQL = strSQL & "([created] LIKE '%" & arrSearchWords(f) & "%') AND "
next
strSQL = left(strSQL,len(strSQL)-5) & ")"
End If
response.write strSQL
I get this:
Request object error 'ASP 0102 : 80004005'
Expecting string input
/267/user_intro.asp, line 188
The function expects a string as input.
Line 188 is the 2nd request.querystring
_____________________________
Easy Web
“Stay committed to your decisions, but stay flexible in your approach.”
| |
|
|
|
mar0364
Posts: 3246
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: Search Help - 10/27/2005 13:45:33
There must be a flaw in my logic. When I search the results include documents that have different codes. If the code I am narrowing down is 123 when I do the search it brings back records with for example a code of 321. What else can I do to tighten up the search scope. I thought that making the code equal to that would only allow me to search within those results.
thanks!
Rich
_____________________________
Easy Web
“Stay committed to your decisions, but stay flexible in your approach.”
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Search Help - 10/27/2005 13:49:17
So no more errors? That's a good thing, yes? We're making progress.
How about running the query and response.write the results. Then post the SQL here. That would probably shed some more light.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mar0364
Posts: 3246
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: Search Help - 10/27/2005 13:58:26
This is the responce.write:
SELECT * FROM Links WHERE code='288' AND (([title] LIKE '%first%')) OR (([describe] LIKE '%first%')) OR (([created] LIKE '%first%'))
This is the code:
If trim(strSearch&"") = "" then
strSQL="SELECT * FROM Links WHERE code='"& Request.querystring("code") &"'"
Else
arrSearchWords=Split(strSearch," ")
strSQL="SELECT * FROM Links WHERE code='"& Request.querystring("code") &"' AND ("
For f = 0 to Ubound(arrSearchWords)
strSQL = strSQL & "([title] LIKE '%" & arrSearchWords(f) & "%') AND "
next
strSQL = left(strSQL,len(strSQL)-5) & ") OR ("
For f = 0 to Ubound(arrSearchWords)
strSQL = strSQL & "([describe] LIKE '%" & arrSearchWords(f) & "%') AND "
next
strSQL = left(strSQL,len(strSQL)-5) & ") OR ("
For f = 0 to Ubound(arrSearchWords)
strSQL = strSQL & "([created] LIKE '%" & arrSearchWords(f) & "%') AND "
next
strSQL = left(strSQL,len(strSQL)-5) & ")"
End If
response.write strSQL
Thanks!
Rich
_____________________________
Easy Web
“Stay committed to your decisions, but stay flexible in your approach.”
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Search Help - 10/27/2005 14:26:16
quote:
WHERE code='288'
I have 2 things to offer:
1. Is code a numeric or text value? If text, leave as is. If numeric, change to:
WHERE code="& Request.querystring("code")
2. Another change to the paren grouping:
...
Else
arrSearchWords=Split(strSearch," ")
strSQL="SELECT * FROM Links WHERE code='"& Request.querystring("code") &"' AND (("
...
strSQL = left(strSQL,len(strSQL)-5) & ")b])"
End If
See what I'm doing? I suspect it's more to do with the second one. VBScript does things from left to right within parens first so I'm trying to force it to do all the string searches outside of the code searc.
Does that make sense?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mar0364
Posts: 3246
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: Search Help - 10/28/2005 9:40:17
Thanks! That seems to have done it. We must have order.
Rich
_____________________________
Easy Web
“Stay committed to your decisions, but stay flexible in your approach.”
| |
|
 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 
Too bad I didn't understand your question fully. 
