Johannes1979
asked on
for john844
it' regarding to this question.
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=asp&qid=20150404
I can't integrate your refined search capabilities to my search engine
I've tried many ways, but failed, would you please do it for me??
this is the search string(search.asp) file:
<%@ Language=VBScript %>
<%
Option explicit
Response.Buffer = TRUE
Response.Expires = 0
Response.ExpiresAbsolute = Now - 1
if isempty(session("userID"))
response.redirect "./default.asp"
end if
Session("mainFrame") = "search.asp"
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<table width="985" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="394" height="137"></td>
<td width="72"></td>
<td width="11"></td>
<td width="52"></td>
<td width="102"></td>
<td width="354"></td>
</tr>
<tr>
<td height="41"></td>
<td></td>
<td></td>
<form name="form1" method="post" action="searchresult.asp">
<td valign="top">
<select name="select1">
<option>Title</option>
<option>Description</optio
</select>
</td>
<td></td>
<td></td>
</tr>
<tr>
<td height="10"></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td height="54"></td>
<td valign="top">
<input type="submit" name="Submit" value="Search">
</td>
<td></td>
<td valign="top" colspan="2">
<input type="text" name="textfield">
</form>
</td>
<td></td>
</tr>
<tr>
<td height="252"></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>
</body>
</html>
this is my searchresult.asp file
<%@ Language=VBScript %>
<%
Option explicit
Response.Buffer = TRUE
Response.Expires = 0
Response.ExpiresAbsolute = Now - 1
if isempty(session("userID"))
response.redirect "./default.asp"
end if
Session("mainFrame") = "searchresult.asp"
dim mycon, SearchField, Searchkey, recordset1, SQLStr, SQLStr2, theuser, filpath
set mycon=server.CreateObject(
mycon.Open "dsn=mydsn;uid=sayang;pwd=
IF Request("select1") = "Title" Then
SearchField = "filetitle"
Else
SearchField = "filedesc"
End IF
SearchKey = Replace(Request("textfield
Set Recordset1 = Server.CreateObject("ADODB
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath from User1, File1 where "&SearchField&" Like '%"&SearchKey&"%' And File1.userID = User1.userID order by File1.filetitle"
SQLStr2 = "Select User1.name, File1.userID, File1.filename, File1.filedesc, File1.filesize, File1.filepath from User1, File1 where "&SearchField&" Like '%"&SearchKey&"%' And File1.userID = User1.userID order by File1.filedesc"
IF Request("select1") = "Title" Then
Recordset1.Open SQLStr, mycon, 1, 3
else
Recordset1.Open SQLStr2, mycon, 1, 3
end if
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<script LANGUAGE="JavaScript">
function newWin(theFile) {
var newPopup = window.open(theFile)
}
</script>
<body bgcolor="#FFFFFF" text="#000000">
<%IF Request("select1") = "Title" Then %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size(KB)</td>
<td width="48%">Title of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
theuser= Recordset1("userID")
filpath= "../users/" & theuser & "/" & Recordset1("filename")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%"><input type="button" value="View" name="B1"
onclick="javascript:newWin
</tr>
<%
Recordset1.MoveNext
Loop
%>
<%Else %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size of Files(KB)</td>
<td width="48%">Description of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
filpath= "../users/" & theuser & "/" & Recordset1("filename")
theuser= Recordset1("userID")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%">
<input type="button" value="View" name="B1"
onclick="javascript:newWin
</td>
</tr>
<%
Recordset1.MoveNext
Loop
%>
<% end if %>
</table>
</td>
</tr>
</table>
</body>
</html>
<%
Recordset1.Close
%>
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=asp&qid=20150404
I can't integrate your refined search capabilities to my search engine
I've tried many ways, but failed, would you please do it for me??
this is the search string(search.asp) file:
<%@ Language=VBScript %>
<%
Option explicit
Response.Buffer = TRUE
Response.Expires = 0
Response.ExpiresAbsolute = Now - 1
if isempty(session("userID"))
response.redirect "./default.asp"
end if
Session("mainFrame") = "search.asp"
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<table width="985" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="394" height="137"></td>
<td width="72"></td>
<td width="11"></td>
<td width="52"></td>
<td width="102"></td>
<td width="354"></td>
</tr>
<tr>
<td height="41"></td>
<td></td>
<td></td>
<form name="form1" method="post" action="searchresult.asp">
<td valign="top">
<select name="select1">
<option>Title</option>
<option>Description</optio
</select>
</td>
<td></td>
<td></td>
</tr>
<tr>
<td height="10"></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td height="54"></td>
<td valign="top">
<input type="submit" name="Submit" value="Search">
</td>
<td></td>
<td valign="top" colspan="2">
<input type="text" name="textfield">
</form>
</td>
<td></td>
</tr>
<tr>
<td height="252"></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>
</body>
</html>
this is my searchresult.asp file
<%@ Language=VBScript %>
<%
Option explicit
Response.Buffer = TRUE
Response.Expires = 0
Response.ExpiresAbsolute = Now - 1
if isempty(session("userID"))
response.redirect "./default.asp"
end if
Session("mainFrame") = "searchresult.asp"
dim mycon, SearchField, Searchkey, recordset1, SQLStr, SQLStr2, theuser, filpath
set mycon=server.CreateObject(
mycon.Open "dsn=mydsn;uid=sayang;pwd=
IF Request("select1") = "Title" Then
SearchField = "filetitle"
Else
SearchField = "filedesc"
End IF
SearchKey = Replace(Request("textfield
Set Recordset1 = Server.CreateObject("ADODB
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath from User1, File1 where "&SearchField&" Like '%"&SearchKey&"%' And File1.userID = User1.userID order by File1.filetitle"
SQLStr2 = "Select User1.name, File1.userID, File1.filename, File1.filedesc, File1.filesize, File1.filepath from User1, File1 where "&SearchField&" Like '%"&SearchKey&"%' And File1.userID = User1.userID order by File1.filedesc"
IF Request("select1") = "Title" Then
Recordset1.Open SQLStr, mycon, 1, 3
else
Recordset1.Open SQLStr2, mycon, 1, 3
end if
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<script LANGUAGE="JavaScript">
function newWin(theFile) {
var newPopup = window.open(theFile)
}
</script>
<body bgcolor="#FFFFFF" text="#000000">
<%IF Request("select1") = "Title" Then %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size(KB)</td>
<td width="48%">Title of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
theuser= Recordset1("userID")
filpath= "../users/" & theuser & "/" & Recordset1("filename")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%"><input type="button" value="View" name="B1"
onclick="javascript:newWin
</tr>
<%
Recordset1.MoveNext
Loop
%>
<%Else %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size of Files(KB)</td>
<td width="48%">Description of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
filpath= "../users/" & theuser & "/" & Recordset1("filename")
theuser= Recordset1("userID")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%">
<input type="button" value="View" name="B1"
onclick="javascript:newWin
</td>
</tr>
<%
Recordset1.MoveNext
Loop
%>
<% end if %>
</table>
</td>
</tr>
</table>
</body>
</html>
<%
Recordset1.Close
%>
just noticed something else that I missed
move the SQLStr up into the if statement as well. I did not see that you were pulling different fields.
IF Request("select1") = "Title" Then
SearchField = "filetitle"
strOrderBy = "ORDER BY File1.filetitle"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1, File1
Else
SearchField = "filedesc"
strOrderBy = "ORDER BY File1.filedesc"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filedesc, File1.filesize, File1.filepath FROM User1, File1"
End IF
move the SQLStr up into the if statement as well. I did not see that you were pulling different fields.
IF Request("select1") = "Title" Then
SearchField = "filetitle"
strOrderBy = "ORDER BY File1.filetitle"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1, File1
Else
SearchField = "filedesc"
strOrderBy = "ORDER BY File1.filedesc"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filedesc, File1.filesize, File1.filepath FROM User1, File1"
End IF
ASKER
Error Type:
Microsoft VBScript runtime (0x800A0005)
Invalid procedure call or argument: 'left'
/project/searchresult.asp,
this is line 178:
strWhere = left(strWhere,len(strWhere
Microsoft VBScript runtime (0x800A0005)
Invalid procedure call or argument: 'left'
/project/searchresult.asp,
this is line 178:
strWhere = left(strWhere,len(strWhere
ASKER
the above one when if no string to search. I expect that it will do nothing if no string to be search
if there is string to search then the error is:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]'filetitle' is not a recognized OPTIMIZER LOCK HINTS option.
/project/searchresult.asp,
thank you so much
if there is string to search then the error is:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]'filetitle' is not a recognized OPTIMIZER LOCK HINTS option.
/project/searchresult.asp,
thank you so much
ASKER
SQLStr = "SELECT User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1, File1"
SQLStr = SQLStr & " " & strWhere & strOrderBy
Recordset1.Open SQLStr, mycon, 1, 3 --->this is line 39
SQLStr = SQLStr & " " & strWhere & strOrderBy
Recordset1.Open SQLStr, mycon, 1, 3 --->this is line 39
this is probably happening when there is no search string entered.
try putting a if statement around that line like
if len(strWhere) > 5 then
strWhere = left(strWhere,len(strWhere
end if
as for the other error, try doing a response.write SQLStr right before executing it.
copy the text from the resulting web page and then paste it into SQL query analyser. Run the statement from there and you will get a more detailed error message pointing you to the problem. If you need more help, post the statement in here so I can look at it.
John
try putting a if statement around that line like
if len(strWhere) > 5 then
strWhere = left(strWhere,len(strWhere
end if
as for the other error, try doing a response.write SQLStr right before executing it.
copy the text from the resulting web page and then paste it into SQL query analyser. Run the statement from there and you will get a more detailed error message pointing you to the problem. If you need more help, post the statement in here so I can look at it.
John
ASKER
I try to block all the related code and then putting
response.write SQLStr
the error now is:
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
/project/searchresult.asp,
this is line32:
strWhere = buildWhereClause(SearchFie
response.write SQLStr
the error now is:
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
/project/searchresult.asp,
this is line32:
strWhere = buildWhereClause(SearchFie
when you say block all the related code. what all are you commenting out.
If it got past that line before, you have commented out one of the lines above it that sets a needed variable(I am guessing).
Try putting it back the way it was and just add the line to show the sql statement.
If it got past that line before, you have commented out one of the lines above it that sets a needed variable(I am guessing).
Try putting it back the way it was and just add the line to show the sql statement.
ASKER
If i am putting the way it was, it got this error, so i couldn't see the response.write result
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]'filetitle' is not a recognized OPTIMIZER LOCK HINTS
option.
/project/searchresult.asp,
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]'filetitle' is not a recognized OPTIMIZER LOCK HINTS
option.
/project/searchresult.asp,
ASKER
seems it is related with this, because without that line it can display all the files inside though it's not matched to the search string, but a mess, display recursively of the same file.
SQLStr = SQLStr & " " & strWhere & strOrderBy
SQLStr = SQLStr & " " & strWhere & strOrderBy
ASKER
By the way these are the tables.
FILE1 table:
userID (numeric)
filename
filetitle
filedesc
filepath
filesize
USER1 table:
userID
name
password
email
FILE1 table:
userID (numeric)
filename
filetitle
filedesc
filepath
filesize
USER1 table:
userID
name
password
try changing
if len(strWhere) > 5 then
strWhere = left(strWhere,len(strWhere
end if
to
if len(strWhere) > 5 then
strWhere = " WHERE " & left(strWhere,len(strWhere
end if
show me the SQL statement that was printed to the web page. Try changing the above and then post the new SQL statement for me. You may have to view the source of the web page to get the sql statement if it is not visible.
if len(strWhere) > 5 then
strWhere = left(strWhere,len(strWhere
end if
to
if len(strWhere) > 5 then
strWhere = " WHERE " & left(strWhere,len(strWhere
end if
show me the SQL statement that was printed to the web page. Try changing the above and then post the new SQL statement for me. You may have to view the source of the web page to get the sql statement if it is not visible.
ASKER
If got search string the output is:
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1, File1 WHERE (filetitle LIKE '%fdsd%')ORDER BY File1.filetitle
if no search string:
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1, File1 ORDER BY File1.filetitle
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1, File1 WHERE (filetitle LIKE '%fdsd%')ORDER BY File1.filetitle
if no search string:
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1, File1 ORDER BY File1.filetitle
the statements look fine. will they run in query analyser?
if not try
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM
User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%fdsd%')ORDER BY File1.filetitle
and
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM
User1 INNER JOIN File1 ON User1.userID = File1.userID ORDER BY File1.filetitle
if not try
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM
User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%fdsd%')ORDER BY File1.filetitle
and
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM
User1 INNER JOIN File1 ON User1.userID = File1.userID ORDER BY File1.filetitle
ASKER
you mean run this statement:
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM
User1, File1 WHERE (filetitle LIKE '%fdsd%')ORDER BY File1.filetitle
do you play icq or any chat software??
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM
User1, File1 WHERE (filetitle LIKE '%fdsd%')ORDER BY File1.filetitle
do you play icq or any chat software??
ASKER
This is the output if the string is match, but if the string is not matched seemed to be fine.
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1, File1 WHERE (filetitle LIKE '%fsa%')ORDER BY File1.filetitle
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1, File1 WHERE (filetitle LIKE '%fsa%')ORDER BY File1.filetitle
ASKER
The records in the table is only 3 records
if i don't put any string to be searched there will be 24 records of result.
if i don't put any string to be searched there will be 24 records of result.
ASKER
give me few minutes seems the other 2 statement is correct
ASKER
john844:please check whether i edit the code the right way or not.
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'WHERE'.
/project/searchresult.asp,
searchresult.asp
<%@ Language=VBScript %>
<%
Option explicit
Response.Buffer = TRUE
Response.Expires = 0
Response.ExpiresAbsolute = Now - 1
if isempty(session("userID"))
response.redirect "./default.asp"
end if
Session("mainFrame") = "searchresult.asp"
dim mycon, SearchField, Searchkey, recordset1, SQLStr, SQLStr2, theuser, filpath, strWhere, strOrderBy
set mycon=server.CreateObject(
mycon.Open "dsn=mydsn;uid=sayang;pwd=
IF Request("select1") = "Title" Then
SearchField = "filetitle"
strOrderBy = "ORDER BY File1.filetitle"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE filetitle LIKE '%"&SQLStr&"%' ORDER BY File1.filetitle"
Else
SearchField = "filedesc"
strOrderBy = "ORDER BY File1.filedesc"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE filetitle LIKE '%"&SQLStr&"%' ORDER BY File1.filetitle"
End IF
SearchKey = Request("textfield")
strWhere = buildWhereClause(SearchFie
Set Recordset1 = Server.CreateObject("ADODB
'SQLStr = "SELECT User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath
'FROM User1, File1"
SQLStr = SQLStr & " " & strWhere & strOrderBy
response.write SQLStr
Recordset1.Open SQLStr, mycon, 1, 3
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<script LANGUAGE="JavaScript">
function newWin(theFile) {
var newPopup = window.open(theFile)
}
</script>
<body bgcolor="#FFFFFF" text="#000000">
<%IF Request("select1") = "Title" Then %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size(KB)</td>
<td width="48%">Title of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
theuser= Recordset1("userID")
filpath= "../users/" & theuser & "/" & Recordset1("filename")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%"><input type="button" value="View" name="B1"
onclick="javascript:newWin
</tr>
<%
Recordset1.MoveNext
Loop
Else %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size of Files(KB)</td>
<td width="48%">Description of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
filpath= "../users/" & theuser & "/" & Recordset1("filename")
theuser= Recordset1("userID")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%">
<input type="button" value="View" name="B1"
onclick="javascript:newWin
</td>
</tr>
<%
Recordset1.MoveNext
Loop
end if
'close and destroy any objects used in page
Recordset1.Close
mycon.Close
set Recordset1 = nothing
set mycon = nothing
%>
</table>
</td>
</tr>
</table>
</body>
</html>
<%
function buildWhereClause(byval strFieldName, byVal strSearch)
dim i
dim aryWords
dim strWhere
'replace any single quotes
strSearch = replace(strSearch,"'","''"
'remove OR from end of string if there
if ucase(right(strSearch,4)) = """OR""" then
strSearch = left(strSearch,len(strSear
end if
'remove NOT from end of string if there
if ucase(right(strSearch,5)) = """NOT""" then
strSearch = left(strSearch,len(strSear
end if
'remove FOLLOWED BY from end of string if there
if ucase(right(strSearch,13))
strSearch = left(strSearch,len(strSear
end if
'split the string at each space
aryWords = split(strSearch)
'loop through each item and build your Where clause string
'adjusting it for or or followed by
for i = lbound(aryWords,1) to ubound(aryWords,1)
if ucase(aryWords(i)) = """OR""" then
strWhere = left(strWhere,len(strWhere
i = i + 1
elseif ucase(aryWords(i)) = """FOLLOWED" then
strWhere = left(strWhere,len(strWhere
i = i + 2
elseif ucase(aryWords(i)) = """NOT""" then
strWhere = strWhere & "(" & strFieldName & " NOT LIKE '%" & aryWords(i+1) & "%') AND "
i = i + 1
else
strWhere = strWhere & "(" & strFieldName & " LIKE '%" & aryWords(i) & "%') AND "
end if
next
'trim off last and
if len(strWhere) > 5 then
strWhere = " WHERE " & left(strWhere,len(strWhere
end if
'return where clause
buildWhereClause = strWhere
end function
%>
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'WHERE'.
/project/searchresult.asp,
searchresult.asp
<%@ Language=VBScript %>
<%
Option explicit
Response.Buffer = TRUE
Response.Expires = 0
Response.ExpiresAbsolute = Now - 1
if isempty(session("userID"))
response.redirect "./default.asp"
end if
Session("mainFrame") = "searchresult.asp"
dim mycon, SearchField, Searchkey, recordset1, SQLStr, SQLStr2, theuser, filpath, strWhere, strOrderBy
set mycon=server.CreateObject(
mycon.Open "dsn=mydsn;uid=sayang;pwd=
IF Request("select1") = "Title" Then
SearchField = "filetitle"
strOrderBy = "ORDER BY File1.filetitle"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE filetitle LIKE '%"&SQLStr&"%' ORDER BY File1.filetitle"
Else
SearchField = "filedesc"
strOrderBy = "ORDER BY File1.filedesc"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE filetitle LIKE '%"&SQLStr&"%' ORDER BY File1.filetitle"
End IF
SearchKey = Request("textfield")
strWhere = buildWhereClause(SearchFie
Set Recordset1 = Server.CreateObject("ADODB
'SQLStr = "SELECT User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath
'FROM User1, File1"
SQLStr = SQLStr & " " & strWhere & strOrderBy
response.write SQLStr
Recordset1.Open SQLStr, mycon, 1, 3
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<script LANGUAGE="JavaScript">
function newWin(theFile) {
var newPopup = window.open(theFile)
}
</script>
<body bgcolor="#FFFFFF" text="#000000">
<%IF Request("select1") = "Title" Then %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size(KB)</td>
<td width="48%">Title of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
theuser= Recordset1("userID")
filpath= "../users/" & theuser & "/" & Recordset1("filename")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%"><input type="button" value="View" name="B1"
onclick="javascript:newWin
</tr>
<%
Recordset1.MoveNext
Loop
Else %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size of Files(KB)</td>
<td width="48%">Description of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
filpath= "../users/" & theuser & "/" & Recordset1("filename")
theuser= Recordset1("userID")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%">
<input type="button" value="View" name="B1"
onclick="javascript:newWin
</td>
</tr>
<%
Recordset1.MoveNext
Loop
end if
'close and destroy any objects used in page
Recordset1.Close
mycon.Close
set Recordset1 = nothing
set mycon = nothing
%>
</table>
</td>
</tr>
</table>
</body>
</html>
<%
function buildWhereClause(byval strFieldName, byVal strSearch)
dim i
dim aryWords
dim strWhere
'replace any single quotes
strSearch = replace(strSearch,"'","''"
'remove OR from end of string if there
if ucase(right(strSearch,4)) = """OR""" then
strSearch = left(strSearch,len(strSear
end if
'remove NOT from end of string if there
if ucase(right(strSearch,5)) = """NOT""" then
strSearch = left(strSearch,len(strSear
end if
'remove FOLLOWED BY from end of string if there
if ucase(right(strSearch,13))
strSearch = left(strSearch,len(strSear
end if
'split the string at each space
aryWords = split(strSearch)
'loop through each item and build your Where clause string
'adjusting it for or or followed by
for i = lbound(aryWords,1) to ubound(aryWords,1)
if ucase(aryWords(i)) = """OR""" then
strWhere = left(strWhere,len(strWhere
i = i + 1
elseif ucase(aryWords(i)) = """FOLLOWED" then
strWhere = left(strWhere,len(strWhere
i = i + 2
elseif ucase(aryWords(i)) = """NOT""" then
strWhere = strWhere & "(" & strFieldName & " NOT LIKE '%" & aryWords(i+1) & "%') AND "
i = i + 1
else
strWhere = strWhere & "(" & strFieldName & " LIKE '%" & aryWords(i) & "%') AND "
end if
next
'trim off last and
if len(strWhere) > 5 then
strWhere = " WHERE " & left(strWhere,len(strWhere
end if
'return where clause
buildWhereClause = strWhere
end function
%>
that looks right. what is being shown for the sql statement when it runs?
ASKER
john844: please ignore the above...
this is the response.write SQLStr:
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%cat dog%') ORDER BY File1.filetitle
It seems the refined search not working, here is the searchresult.asp:
<%@ Language=VBScript %>
<%
Option explicit
Response.Buffer = TRUE
Response.Expires = 0
Response.ExpiresAbsolute = Now - 1
if isempty(session("userID"))
response.redirect "./default.asp"
end if
Session("mainFrame") = "searchresult.asp"
dim mycon, SearchField, Searchkey, recordset1, SQLStr, SQLStr2, theuser, filpath, strWhere, strOrderBy
set mycon=server.CreateObject(
mycon.Open "dsn=mydsn;uid=sayang;pwd=
SearchKey = Request("textfield")
IF Request("select1") = "Title" Then
SearchField = "filetitle"
strOrderBy = "ORDER BY File1.filetitle"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%" &SearchKey& "%') ORDER BY File1.filetitle"
'SQLStr = SQLStr & " " & strWhere & strOrderBy
Else
SearchField = "filedesc"
strOrderBy = "ORDER BY File1.filedesc"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%" &SearchKey& "%') ORDER BY File1.filetitle"
'SQLStr = SQLStr & " " & strWhere & strOrderBy
End IF
strWhere = buildWhereClause(SearchFie
Set Recordset1 = Server.CreateObject("ADODB
response.write SQLStr
Recordset1.Open SQLStr, mycon, 1, 3
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<script LANGUAGE="JavaScript">
function newWin(theFile) {
var newPopup = window.open(theFile)
}
</script>
<body bgcolor="#FFFFFF" text="#000000">
<%IF Request("select1") = "Title" Then %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size(KB)</td>
<td width="48%">Title of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
theuser= Recordset1("userID")
filpath= "../users/" & theuser & "/" & Recordset1("filename")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%"><input type="button" value="View" name="B1"
onclick="javascript:newWin
</tr>
<%
Recordset1.MoveNext
Loop
Else %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size of Files(KB)</td>
<td width="48%">Description of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
filpath= "../users/" & theuser & "/" & Recordset1("filename")
theuser= Recordset1("userID")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%">
<input type="button" value="View" name="B1"
onclick="javascript:newWin
</td>
</tr>
<%
Recordset1.MoveNext
Loop
end if
'close and destroy any objects used in page
Recordset1.Close
mycon.Close
set Recordset1 = nothing
set mycon = nothing
%>
</table>
</td>
</tr>
</table>
</body>
</html>
<%
function buildWhereClause(byval strFieldName, byVal strSearch)
dim i
dim aryWords
dim strWhere
'replace any single quotes
strSearch = replace(strSearch,"'","''"
'remove OR from end of string if there
if ucase(right(strSearch,4)) = """OR""" then
strSearch = left(strSearch,len(strSear
end if
'remove NOT from end of string if there
if ucase(right(strSearch,5)) = """NOT""" then
strSearch = left(strSearch,len(strSear
end if
'remove FOLLOWED BY from end of string if there
if ucase(right(strSearch,13))
strSearch = left(strSearch,len(strSear
end if
'split the string at each space
aryWords = split(strSearch)
'loop through each item and build your Where clause string
'adjusting it for or or followed by
for i = lbound(aryWords,1) to ubound(aryWords,1)
if ucase(aryWords(i)) = """OR""" then
strWhere = left(strWhere,len(strWhere
i = i + 1
elseif ucase(aryWords(i)) = """FOLLOWED" then
strWhere = left(strWhere,len(strWhere
i = i + 2
elseif ucase(aryWords(i)) = """NOT""" then
strWhere = strWhere & "(" & strFieldName & " NOT LIKE '%" & aryWords(i+1) & "%') AND "
i = i + 1
else
strWhere = strWhere & "(" & strFieldName & " LIKE '%" & aryWords(i) & "%') AND "
end if
next
'trim off last and
if len(strWhere) > 5 then
strWhere = " WHERE " & left(strWhere,len(strWhere
end if
'return where clause
buildWhereClause = strWhere
end function
%>
this is the response.write SQLStr:
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%cat dog%') ORDER BY File1.filetitle
It seems the refined search not working, here is the searchresult.asp:
<%@ Language=VBScript %>
<%
Option explicit
Response.Buffer = TRUE
Response.Expires = 0
Response.ExpiresAbsolute = Now - 1
if isempty(session("userID"))
response.redirect "./default.asp"
end if
Session("mainFrame") = "searchresult.asp"
dim mycon, SearchField, Searchkey, recordset1, SQLStr, SQLStr2, theuser, filpath, strWhere, strOrderBy
set mycon=server.CreateObject(
mycon.Open "dsn=mydsn;uid=sayang;pwd=
SearchKey = Request("textfield")
IF Request("select1") = "Title" Then
SearchField = "filetitle"
strOrderBy = "ORDER BY File1.filetitle"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%" &SearchKey& "%') ORDER BY File1.filetitle"
'SQLStr = SQLStr & " " & strWhere & strOrderBy
Else
SearchField = "filedesc"
strOrderBy = "ORDER BY File1.filedesc"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%" &SearchKey& "%') ORDER BY File1.filetitle"
'SQLStr = SQLStr & " " & strWhere & strOrderBy
End IF
strWhere = buildWhereClause(SearchFie
Set Recordset1 = Server.CreateObject("ADODB
response.write SQLStr
Recordset1.Open SQLStr, mycon, 1, 3
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<script LANGUAGE="JavaScript">
function newWin(theFile) {
var newPopup = window.open(theFile)
}
</script>
<body bgcolor="#FFFFFF" text="#000000">
<%IF Request("select1") = "Title" Then %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size(KB)</td>
<td width="48%">Title of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
theuser= Recordset1("userID")
filpath= "../users/" & theuser & "/" & Recordset1("filename")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%"><input type="button" value="View" name="B1"
onclick="javascript:newWin
</tr>
<%
Recordset1.MoveNext
Loop
Else %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size of Files(KB)</td>
<td width="48%">Description of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
filpath= "../users/" & theuser & "/" & Recordset1("filename")
theuser= Recordset1("userID")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%">
<input type="button" value="View" name="B1"
onclick="javascript:newWin
</td>
</tr>
<%
Recordset1.MoveNext
Loop
end if
'close and destroy any objects used in page
Recordset1.Close
mycon.Close
set Recordset1 = nothing
set mycon = nothing
%>
</table>
</td>
</tr>
</table>
</body>
</html>
<%
function buildWhereClause(byval strFieldName, byVal strSearch)
dim i
dim aryWords
dim strWhere
'replace any single quotes
strSearch = replace(strSearch,"'","''"
'remove OR from end of string if there
if ucase(right(strSearch,4)) = """OR""" then
strSearch = left(strSearch,len(strSear
end if
'remove NOT from end of string if there
if ucase(right(strSearch,5)) = """NOT""" then
strSearch = left(strSearch,len(strSear
end if
'remove FOLLOWED BY from end of string if there
if ucase(right(strSearch,13))
strSearch = left(strSearch,len(strSear
end if
'split the string at each space
aryWords = split(strSearch)
'loop through each item and build your Where clause string
'adjusting it for or or followed by
for i = lbound(aryWords,1) to ubound(aryWords,1)
if ucase(aryWords(i)) = """OR""" then
strWhere = left(strWhere,len(strWhere
i = i + 1
elseif ucase(aryWords(i)) = """FOLLOWED" then
strWhere = left(strWhere,len(strWhere
i = i + 2
elseif ucase(aryWords(i)) = """NOT""" then
strWhere = strWhere & "(" & strFieldName & " NOT LIKE '%" & aryWords(i+1) & "%') AND "
i = i + 1
else
strWhere = strWhere & "(" & strFieldName & " LIKE '%" & aryWords(i) & "%') AND "
end if
next
'trim off last and
if len(strWhere) > 5 then
strWhere = " WHERE " & left(strWhere,len(strWhere
end if
'return where clause
buildWhereClause = strWhere
end function
%>
not sure how you are getting that sql statement.
in your code, you have the sections of code to add in the where clause commented out. try replacing the following code:
IF Request("select1") = "Title" Then
SearchField = "filetitle"
strOrderBy = "ORDER BY File1.filetitle"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath
FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%" &SearchKey& "%')
ORDER BY File1.filetitle"
'SQLStr = SQLStr & " " & strWhere & strOrderBy
Else
SearchField = "filedesc"
strOrderBy = "ORDER BY File1.filedesc"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath
FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%" &SearchKey& "%')
ORDER BY File1.filetitle"
'SQLStr = SQLStr & " " & strWhere & strOrderBy
End IF
strWhere = buildWhereClause(SearchFie
Set Recordset1 = Server.CreateObject("ADODB
with
IF Request("select1") = "Title" Then
SearchField = "filetitle"
strOrderBy = " ORDER BY File1.filetitle"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID"
Else
SearchField = "filedesc"
strOrderBy = " ORDER BY File1.filedesc"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID"
End IF
strWhere = buildWhereClause(SearchFie
SQLStr = SQLStr & strWhere & strOrderBy
Set Recordset1 = Server.CreateObject("ADODB
if this still does not work. copy the output sql statement into query analyser. run it there. if you get an error, let me know what it is if you can't fix it.
in your code, you have the sections of code to add in the where clause commented out. try replacing the following code:
IF Request("select1") = "Title" Then
SearchField = "filetitle"
strOrderBy = "ORDER BY File1.filetitle"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath
FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%" &SearchKey& "%')
ORDER BY File1.filetitle"
'SQLStr = SQLStr & " " & strWhere & strOrderBy
Else
SearchField = "filedesc"
strOrderBy = "ORDER BY File1.filedesc"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath
FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%" &SearchKey& "%')
ORDER BY File1.filetitle"
'SQLStr = SQLStr & " " & strWhere & strOrderBy
End IF
strWhere = buildWhereClause(SearchFie
Set Recordset1 = Server.CreateObject("ADODB
with
IF Request("select1") = "Title" Then
SearchField = "filetitle"
strOrderBy = " ORDER BY File1.filetitle"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID"
Else
SearchField = "filedesc"
strOrderBy = " ORDER BY File1.filedesc"
SQLStr = "Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID"
End IF
strWhere = buildWhereClause(SearchFie
SQLStr = SQLStr & strWhere & strOrderBy
Set Recordset1 = Server.CreateObject("ADODB
if this still does not work. copy the output sql statement into query analyser. run it there. if you get an error, let me know what it is if you can't fix it.
ASKER
When i put in the search string:
cat ""followed by"" running
The output is:
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%cat%') AND (filetitle LIKE '%""followed%') AND (filetitle LIKE '%by""%') AND (filetitle LIKE '%running%') ORDER BY File1.filetitle
cat ""followed by"" running
The output is:
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%cat%') AND (filetitle LIKE '%""followed%') AND (filetitle LIKE '%by""%') AND (filetitle LIKE '%running%') ORDER BY File1.filetitle
ASKER
When i put in the search string:
cat ""OR"" running
The output is:
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%cat%') AND (filetitle LIKE '%""OR""%') AND (filetitle LIKE '%running%') ORDER BY File1.filetitle
cat ""OR"" running
The output is:
Select User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1 INNER JOIN File1 ON User1.userID = File1.userID WHERE (filetitle LIKE '%cat%') AND (filetitle LIKE '%""OR""%') AND (filetitle LIKE '%running%') ORDER BY File1.filetitle
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am on aol IM now do you have this available?
ASKER
yup, correct already.
but how come it needs only 1 double quotes whereas the previous one need 1 double quotes??
but how come it needs only 1 double quotes whereas the previous one need 1 double quotes??
ASKER
thank you very much john844.
i've been trying this for 1 week but still fail :P
Luckily can inform you. thanks a lot man
i've been trying this for 1 week but still fail :P
Luckily can inform you. thanks a lot man
in the previous one we were trying to assign the variable trough code
SearchKey = "cat ""Followed by"" running"
variable now holds
cat "Followed by" running
when we are setting the variable like
SearchKey = Request("textfield") 'where cat "Followed by" running is entered by user on form
SearchKey now holds
cat "Followed by" running
we just had to specifically say add a Quote to our string. The way to do that was to use "" where we wanted " inside the string. This is because the whole string is surrounded by quotes. If we only put one " in the compiler would interpret this
SearchKey = "cat "Followed by" running"
as assign the string "cat " and then try to execute Followed by and then try to assign " running" to searchKey.
SearchKey = "cat ""Followed by"" running"
variable now holds
cat "Followed by" running
when we are setting the variable like
SearchKey = Request("textfield") 'where cat "Followed by" running is entered by user on form
SearchKey now holds
cat "Followed by" running
we just had to specifically say add a Quote to our string. The way to do that was to use "" where we wanted " inside the string. This is because the whole string is surrounded by quotes. If we only put one " in the compiler would interpret this
SearchKey = "cat "Followed by" running"
as assign the string "cat " and then try to execute Followed by and then try to assign " running" to searchKey.
ASKER
oo ic thank you so much
did not test the code so there might be some errors in it.
Let me know if you need more help with it.
<%@ Language=VBScript %>
<%
Option explicit
Response.Buffer = TRUE
Response.Expires = 0
Response.ExpiresAbsolute = Now - 1
if isempty(session("userID"))
response.redirect "./default.asp"
end if
Session("mainFrame") = "searchresult.asp"
dim mycon, SearchField, Searchkey, recordset1, SQLStr, SQLStr2, theuser, filpath, strWhere, strOrderBy
set mycon=server.CreateObject(
mycon.Open "dsn=mydsn;uid=sayang;pwd=
IF Request("select1") = "Title" Then
SearchField = "filetitle"
strOrderBy = "ORDER BY File1.filetitle"
Else
SearchField = "filedesc"
strOrderBy = "ORDER BY File1.filedesc"
End IF
SearchKey = Request("textfield")
strWhere = buildWhereClause(SearchFie
Set Recordset1 = Server.CreateObject("ADODB
SQLStr = "SELECT User1.name, File1.userID, File1.filename, File1.filetitle, File1.filesize, File1.filepath FROM User1, File1"
SQLStr = SQLStr & " " & strWhere & strOrderBy
Recordset1.Open SQLStr, mycon, 1, 3
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<script LANGUAGE="JavaScript">
function newWin(theFile) {
var newPopup = window.open(theFile)
}
</script>
<body bgcolor="#FFFFFF" text="#000000">
<%IF Request("select1") = "Title" Then %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size(KB)</td>
<td width="48%">Title of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
theuser= Recordset1("userID")
filpath= "../users/" & theuser & "/" & Recordset1("filename")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%"><input type="button" value="View" name="B1"
onclick="javascript:newWin
</tr>
<%
Recordset1.MoveNext
Loop
Else %>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="974" height="443" valign="top">
<table width="100%" border="1">
<tr>
<td width="15%">Publisher</td>
<td width="15%">Filename</td>
<td width="12%">Size of Files(KB)</td>
<td width="48%">Description of Files</td>
<td width="10%">Download!!</td
</tr>
<%
Do While Not Recordset1.EOF
filpath= "../users/" & theuser & "/" & Recordset1("filename")
theuser= Recordset1("userID")
%>
<tr>
<td width="15%"><%=Recordset1(
<td width="15%"><%=Recordset1(
<td width="12%"><%=Recordset1(
<td width="48%"><%=Recordset1(
<td width="10%">
<input type="button" value="View" name="B1"
onclick="javascript:newWin
</td>
</tr>
<%
Recordset1.MoveNext
Loop
end if
'close and destroy any objects used in page
Recordset1.Close
mycon.Close
set Recordset1 = nothing
set mycon = nothing
%>
</table>
</td>
</tr>
</table>
</body>
</html>
<%
function buildWhereClause(byval strFieldName, byVal strSearch)
dim i
dim aryWords
dim strWhere
'replace any single quotes
strSearch = replace(strSearch,"'","''"
'remove OR from end of string if there
if ucase(right(strSearch,4)) = """OR""" then
strSearch = left(strSearch,len(strSear
end if
'remove NOT from end of string if there
if ucase(right(strSearch,5)) = """NOT""" then
strSearch = left(strSearch,len(strSear
end if
'remove FOLLOWED BY from end of string if there
if ucase(right(strSearch,13))
strSearch = left(strSearch,len(strSear
end if
'split the string at each space
aryWords = split(strSearch)
'loop through each item and build your Where clause string
'adjusting it for or or followed by
for i = lbound(aryWords,1) to ubound(aryWords,1)
if ucase(aryWords(i)) = """OR""" then
strWhere = left(strWhere,len(strWhere
i = i + 1
elseif ucase(aryWords(i)) = """FOLLOWED" then
strWhere = left(strWhere,len(strWhere
i = i + 2
elseif ucase(aryWords(i)) = """NOT""" then
strWhere = strWhere & "(" & strFieldName & " NOT LIKE '%" & aryWords(i+1) & "%') AND "
i = i + 1
else
strWhere = strWhere & "(" & strFieldName & " LIKE '%" & aryWords(i) & "%') AND "
end if
next
'trim off last and
strWhere = left(strWhere,len(strWhere
'return where clause
buildWhereClause = strWhere
end function
%>