L
Lee Mundie
Hi, wonder if anyone can help me with this - This was originally a simple
search routine returning results back to itself!
Trying to be smart I tried to combine a no. items per page so I could
limited items displayed...
Okay problem! everything seems to be okay except it is returning no results,
when I know it should
the SQL string appears to be okay and if I remove the search criteria
handlers then it kinda works but still not right!
Can anyone tell me where the problem is - would be greatly appreciated.
Thanks in advance
Lee
<%
if Request.QueryString("order") = "" then
StrOrder = "p.prodCode"
Else
StrOrder = Request.QueryString("order")
End if
Const adOpenKeyset = 1
Const adLockReadOnly = 1
set conn = server.createobject("ADODB.Connection")
set rs = server.createobject("ADODB.Recordset")
conn.open "TTMYDB"
Function FormatStr(String)
on Error resume next
String = Replace(String, CHR(13), "")
String = Replace(String, CHR(10) & CHR(10), "</P><P>")
String = Replace(String, CHR(10), "<BR>")
FormatStr = String
End Function
If Request.QueryString("mode") = "doit" then
keywords = split(Request.Form("search"), " ")
boolthing = true
sql = "select distinct p.prodCode, p.prodName, p.productDesc, c.catalogName,
p.productPrice, c.catalogID from products p, catalogs c where
p.catalogID=c.catalogID order by " & StrOrder
for each word in keywords
if boolthing then
boolthing=false
sql = sql & "and ("
else
sql = sql & "or"
end if
sql = sql & " p.prodName like '%" & word & "%' "
sql = sql & "or p.productDesc like '%" & word & "%' "
sql = sql & "or p.prodCode like '%" & word & "%' "
sql = sql & "or c.catalogName like '%" & word & "%' "
next
sql = sql & ")"
Response.Write Sql
rs.open sql, conn, adOpenKeyset, adLockReadOnly
if rs.EOF or rs.BOF then
Response.Write "No Members Found"
Else
TotalRows = rs.RecordCount
rs.PageSize = 2
PageSize = rs.PageSize
TotalPages=RS.PageCount
select case Request("move")
case "first" pageNo = 1 ' First
case "prev" pageNo = cint(Session("page_num")) - 1 ' Next
case "next" pageNo = cint(Session("page_num")) + 1 ' Previous
case "last" pageNo = TotalPages ' Last
case Else PageNo = 1
End Select
if cint(PageNo) < 1 then PageNo = 1
if cint(PageNo) > TotalPages then pageNo = TotalPages
RS.AbsolutePage = PageNo
PageNumber=PageNo
Session("page_num") = PageNo
Response.Write "<td>"
HowMany = 0
Do until rs.EOF or HowMany => PageSize
Response.Write "<h1>Item: <a href='productresult.asp?strprodCode='" &
rs("prodCode") & "'>"
Response.Write "<b>" & rs("prodCode") & " - " & rs("prodName") & "</a></b>"
Response.Write
"<br> This product is
£"
Response.Write ""
rs.MoveNext
howmany = howmany + 1
loop
Response.Write " "
End if
%></td>
<% end if %>
search routine returning results back to itself!
Trying to be smart I tried to combine a no. items per page so I could
limited items displayed...
Okay problem! everything seems to be okay except it is returning no results,
when I know it should
the SQL string appears to be okay and if I remove the search criteria
handlers then it kinda works but still not right!
Can anyone tell me where the problem is - would be greatly appreciated.
Thanks in advance
Lee
<%
if Request.QueryString("order") = "" then
StrOrder = "p.prodCode"
Else
StrOrder = Request.QueryString("order")
End if
Const adOpenKeyset = 1
Const adLockReadOnly = 1
set conn = server.createobject("ADODB.Connection")
set rs = server.createobject("ADODB.Recordset")
conn.open "TTMYDB"
Function FormatStr(String)
on Error resume next
String = Replace(String, CHR(13), "")
String = Replace(String, CHR(10) & CHR(10), "</P><P>")
String = Replace(String, CHR(10), "<BR>")
FormatStr = String
End Function
If Request.QueryString("mode") = "doit" then
keywords = split(Request.Form("search"), " ")
boolthing = true
sql = "select distinct p.prodCode, p.prodName, p.productDesc, c.catalogName,
p.productPrice, c.catalogID from products p, catalogs c where
p.catalogID=c.catalogID order by " & StrOrder
for each word in keywords
if boolthing then
boolthing=false
sql = sql & "and ("
else
sql = sql & "or"
end if
sql = sql & " p.prodName like '%" & word & "%' "
sql = sql & "or p.productDesc like '%" & word & "%' "
sql = sql & "or p.prodCode like '%" & word & "%' "
sql = sql & "or c.catalogName like '%" & word & "%' "
next
sql = sql & ")"
Response.Write Sql
rs.open sql, conn, adOpenKeyset, adLockReadOnly
if rs.EOF or rs.BOF then
Response.Write "No Members Found"
Else
TotalRows = rs.RecordCount
rs.PageSize = 2
PageSize = rs.PageSize
TotalPages=RS.PageCount
select case Request("move")
case "first" pageNo = 1 ' First
case "prev" pageNo = cint(Session("page_num")) - 1 ' Next
case "next" pageNo = cint(Session("page_num")) + 1 ' Previous
case "last" pageNo = TotalPages ' Last
case Else PageNo = 1
End Select
if cint(PageNo) < 1 then PageNo = 1
if cint(PageNo) > TotalPages then pageNo = TotalPages
RS.AbsolutePage = PageNo
PageNumber=PageNo
Session("page_num") = PageNo
Response.Write "<td>"
HowMany = 0
Do until rs.EOF or HowMany => PageSize
Response.Write "<h1>Item: <a href='productresult.asp?strprodCode='" &
rs("prodCode") & "'>"
Response.Write "<b>" & rs("prodCode") & " - " & rs("prodName") & "</a></b>"
Response.Write
"<br> This product is
£"
Response.Write ""
rs.MoveNext
howmany = howmany + 1
loop
Response.Write " "
End if
%></td>
<% end if %>