ASP Search for Accesss DB?

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

<%
 Option Explicit
 Response.Buffer = True
 Response.Expires = 0
%>
<html>
<head>
 <style>
  body { font-family : Verdana; font-size : 8pt; }
 </style>
</head>
<body>
<!--#include file="editme.asp"-->
<%
' ADO Constants - Don't Change Them
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdText = &H0001
Const adUseClient = 3
Dim currentPage
 If Len(Request.QueryString("currentPage")) = 0 Then
        currentPage = 1
    Else
        currentPage = CInt(Request.QueryString("currentPage"))
    End If
Dim recordsToShow
 recordsToShow = 10

  ' Keyword/s to search
Dim strKeyword
 strKeyword = split(Trim(Request.QueryString("look_for")), " ")

 ' Title keyword/s to search
Dim strTitle
 strTitle = split(Trim(Request.QueryString("look_for")), " ")

 ' Our Connection Object
Dim con
 Set con = CreateObject("ADODB.Connection")
 con.Open strDB

 ' Our Recordset Object
Dim rs
 Set rs = CreateObject("ADODB.Recordset")
 rs.CursorLocation = adUseClient
 rs.PageSize = recordsToShow
 rs.CacheSize = recordsToShow

' Searching the records for the keywords entered
 Select Case UBound(strTitle) 
  Case 0   rs.Open "select * from T_WEBLOG where title like '%" & strTitle(0) & "%' order by mydate desc", con, adOpenForwardOnly, 
adLockReadOnly, adCmdText
  Case 1  rs.Open "select * from T_WEBLOG where title like '%" & strTitle(0) & "%' and title like '%" & strTitle(1) & "%' order by 
mydate desc", con
  Case 2  rs.Open "select * from T_WEBLOG where title like '%" & strTitle(0) & "%' and title like '%" & strTitle(1) & "%' and title 
like '%" & strTitle(2) & "%' order by mydate desc", con
  Case Else rs.Open "select * from T_WEBLOG where title like '%" & strTitle(0) & "%' and title like '%" & strTitle(1) & "%' and title 
like '%" & strTitle(2) & "%' order by mydate desc", con

  End Select

 ' If the returning recordset is not empty
 If Not rs.EOF Then
Dim totalpages
 totalpages = rs.PageCount
 rs.AbsolutePage = currentPage

 ' Showing total number of pages found and the current page number
    Response.Write "Displaying Page " & currentPage & " of " & totalPages & "<br>"
    Response.Write "Total Records Found : " & rs.RecordCount & "<br>"
    Response.Write "Search Results for : " & Request.QueryString("look_for")
    Response.Write "<br><br>"

 ' Showing relevant records
 Dim rcount, i, x
 For i = 1 To rs.PageSize
   rcount = i
   If currentPage > 1 Then
    For x = 1 To (currentPage - 1)
     rcount = 10 + rcount
    Next
   End If
  If Not rs.EOF Then
  Response.Write rcount & ") <b><a href=""" & rs("url") & """>" & rs("title") & "</a></b><br>" & vbcrlf
  Response.Write "" & rs("description") & "<br>" & vbcrlf
  Response.Write "URL : " & rs("url") & "</b><br>" & vbcrlf
  Response.Write "Last indexed on : " & rs("mydate") & "<br><br>" & vbcrlf
  rs.MoveNext
  End If
 Next

    ' Links to move through the records
    If currentPage > 1 Then
        Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?currentPage=" & currentPage - 1 & "&look_for=" & 
Server.URLEncode(Request.QueryString("look_for")) & """>Back</a>"
  Else
        Response.Write "<u style=""color : silver;"">Back</u>"
    End If

 Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"

 If CInt(currentPage) <> CInt(totalPages) Then
        Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?currentPage=" & currentPage + 1 & "&look_for=" & 
Server.URLEncode(Request.QueryString("look_for")) & """>Next</a>"
     Else
        Response.Write "<u style=""color : silver;"">Next</u>"
 End If

 Else
 Response.Write "<b>Sorry, no matching record was found.</b>" & vbcrlf 

 End If

 ' Done. Now release Objects
 con.Close
 Set con = Nothing
 Set rs = Nothing
%>
<br><br><br>
<div align="center" style="color:silver;"></div>
</body></html>