asked on

Search using Indexing Service: Cannot compare date

Hi,

Here is my code to searching the website using Indexing Service, all the part seems to be working but not the Date Modified.

The line got problem is at:
strQuery = strQuery & " " & operator & " (write like " & replace(mid(datemodified,4,3),"0","") & replace(mid(datemodified,1,3),"0","") & right(datemodified,4) & ") "

The question is How do i compare the date with the 'write' field in Indexing Service?

* i use a js file which can be downloaded from: http://www.activefusion.net/af/js/checkdate.js

Here is the source code, free to copy and test.

<% Response.expires = 0
      Server.ScriptTimeout = 500
      'on error resume next
      startURL = "http://www.domain.net"
      'startURL = "http://127.0.0.1"

      para = ""
      chkdoc = ""
      chksize = ""
      chkdate = ""
      fileequal = ""
      filesize = ""
      datemodified = ""
      operator = ""
      sortby = "Rank"
      orderby = "d"

      if isEmpty(request("para")) then
            gotPara = false
      else
            if Request("para") = "" then
                  gotPara = false
            else
                  para = Replace(Request("para"),"'","''")

                  chkdoc = "" & Request("chkdoc")
                  chksize = "" & Request("chksize")
                  chkdate = "" & Request("chkdate")
                  fileequal = "" & Request("fileequal")
                  filesize = "" & Request("filesize")
                  datemodified = "" & Request("datemodified")
                  operator = "" & Request("operator")
                  sortby = "" & Request("sortby")
                  orderby = "" & Request("orderby")

                  gotPara = True
            end if
      End if
%>
<html>
<head>
<title>Demo Search</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<Script languange = "Javascript">
      function chkformat(obj,format) {
            var ok = true;
            if (obj.value.length == 0) {
                  ok = false;
            } else {
                  for (var i=0; i<obj.value.length; i++) {
                        temp = "" + obj.value.substring(i, i+1);
                        if (format.indexOf(temp) == "-1") ok = false;break;
                  }
            }
            return ok;
      }


      function verify(frm) {
            if (frm.para.value == "") {
                  alert('Please enter Keyword to search');
                  frm.para.focus();
                  return false;
            }
            if (frm.chksize.checked) {
                  if (frm.fileequal.options[frm.fileequal.selectedIndex].value == "") {
                        alert('Please select File Size');
                        frm.fileequal.focus();
                        return false;
                  }
                  if (chkformat(frm.filesize,"0123456789") == false) {
                        alert('Please enter numeric File Size');
                        frm.filesize.focus();
                        frm.filesize.select();
                        return false;
                  }
            }
            if (frm.chkdate.checked) {
                  if (frm.datemodified.value == "") {
                        alert('Please enter Date Modified');
                        frm.datemodified.focus();
                        return false;
                  }
                  if (checkdate(frm.datemodified) == false) {
                        alert('Please enter valid Date Modified');
                        frm.datemodified.focus();
                        frm.datemodified.select();
                        return false;
                  }
            }
            if (frm.operator.options[frm.operator.selectedIndex].value == "") {
                  alert('Please select Operator');
                  frm.operator.focus();
                  return false;
            }
            if (frm.sortby.options[frm.sortby.selectedIndex].value == "") {
                  alert('Please select Sort By');
                  frm.sortby.focus();
                  return false;
            }
            if (frm.orderby.options[frm.orderby.selectedIndex].value == "") {
                  alert('Please select Order By');
                  frm.orderby.focus();
                  return false;
            }
            return true;
      }
</script>

<script language="Javascript1.2" src="js/af.js"></script>
<script language="Javascript1.2" src="js/checkdate.js"></script>
<body bgcolor="#FFFFFF" text="#000000">
<form name="form1" method="post" action="search.asp" onsubmit="Javascript:return verify(this);">
<table width="600" cellspacing="0" cellpadding="0" border="1" bordercolor="#000000">
<tr>
<td>
<table width="600" cellspacing="0" cellpadding="2">
<tr bgcolor="#006699">
<td width="5"> </td>
<td width="88"><b><font face="Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Search</font></b></td>
<td colspan="2"> </td>
</tr>
<tr>
<td width="5"> </td>
<td width="88"><font face="Arial, Helvetica, sans-serif" size="2"><b>Keyword:
</b></font> </td>
<td colspan="2">
<input type="text" name="para" maxlength="255" value="<%=para%>" size="30">
</td>
</tr>
<tr>
<td width="5"><font face="Arial, Helvetica, sans-serif" size="2"></font></td>
<td width="88"><font face="Arial, Helvetica, sans-serif" size="2"></font></td>
<td colspan="2"><font face="Arial, Helvetica, sans-serif" size="2"></font></td>
</tr>
<tr>
<td width="5"><font face="Arial, Helvetica, sans-serif" size="2"></font></td>
<td width="88"><font face="Arial, Helvetica, sans-serif" size="2"><b>Search
with:</b></font></td>
<td colspan="2"><font face="Arial, Helvetica, sans-serif" size="2"></font></td>
</tr>
<tr>
<td width="5"><font face="Arial, Helvetica, sans-serif" size="2"></font></td>
<td width="88" align="center"><font face="Arial, Helvetica, sans-serif" size="2">
<input type="checkbox" name="chkdoc" value="Yes" <%=chkopt(chkdoc,"Yes","checked")%>>
</font></td>
<td colspan="2"><font face="Arial, Helvetica, sans-serif" size="2">Document
Title</font></td>
</tr>
<tr>
<td width="5"><font face="Arial, Helvetica, sans-serif" size="2"></font></td>
<td width="88" align="center"><font face="Arial, Helvetica, sans-serif" size="2">
<input type="checkbox" name="chksize" value="Yes" <%=chkopt(chksize,"Yes","checked")%>>
</font></td>
<td width="87"><font face="Arial, Helvetica, sans-serif" size="2">Size
</font></td>
<td width="402">
<select name="fileequal">
<option value="=" <%=chkopt(fileequal,"=","selected")%>>=</option>
<option value="<" <%=chkopt(fileequal,"<","selected")%>><</option>
<option value="<=" <%=chkopt(fileequal,"<=","selected")%>><=</option>
<option value=">" <%=chkopt(fileequal,">","selected")%>>></option>
<option value=">=" <%=chkopt(fileequal,">=","selected")%>>>=</option>
</select>
<input type="text" name="filesize" maxlength="3" value="<%=filesize%>" size="2">
<font face="Arial, Helvetica, sans-serif" size="2">Kbs</font> </td>
</tr>
<tr>
<td width="5"><font face="Arial, Helvetica, sans-serif" size="2"></font></td>
<td width="88" align="center"><font face="Arial, Helvetica, sans-serif" size="2">
<input type="checkbox" name="chkdate" value="Yes" <%=chkopt(chkdate,"Yes","checked")%>>
</font></td>
<td width="87"><font face="Arial, Helvetica, sans-serif" size="2">Date
Modified</font></td>
<td width="402">
<input type="text" name="datemodified" maxlength="10" value="<%=datemodified%>" size="8">
<font face="Arial, Helvetica, sans-serif" size="2"> (dd/mm/yyyy)
</font></td>
</tr>
<tr>
<td width="5"> </td>
<td width="88"><font face="Arial, Helvetica, sans-serif" size="2"><b>Operator:</b></font></td>
<td colspan="2">
<select name="operator">
<option value="AND" <%=chkopt(operator,"AND","selected")%>>And</option>
<option value="OR" <%=chkopt(operator,"OR","selected")%>>Or</option>
</select>
</td>
</tr>
<tr>
<td width="5"><font face="Arial, Helvetica, sans-serif" size="2"></font></td>
<td width="88"><font face="Arial, Helvetica, sans-serif" size="2">
</font></td>
<td colspan="2"><font face="Arial, Helvetica, sans-serif" size="2"></font></td>
</tr>
<tr>
<td width="5" height="22"> </td>
<td width="88" height="22"><b><font face="Arial, Helvetica, sans-serif" size="2">Sort
By:</font></b></td>
<td colspan="2" height="22">
<select name="sortby">
<option value="Rank" <%=chkopt(sortby,"Rank","selected")%>>Rank</option>
<option value="DocTitle" <%=chkopt(sortby,"DocTitle","selected")%>>Title</option>
<option value="Path" <%=chkopt(sortby,"Path","selected")%>>Path</option>
<option value="Size" <%=chkopt(sortby,"Size","selected")%>>Size</option>
<option value="write" <%=chkopt(sortby,"write","selected")%>>Modified</option>
</select>
</td>
</tr>
<tr>
<td width="5"> </td>
<td width="88"><b><font face="Arial, Helvetica, sans-serif" size="2">Order
By:</font></b></td>
<td colspan="2">
<select name="orderby">
<option value="a" <%=chkopt(orderby,"a","selected")%>>Ascending</option>
<option value="d" <%=chkopt(orderby,"d","selected")%>>Descending</option>
</select>
</td>
</tr>
<tr>
<td width="5"> </td>
<td width="88"> </td>
<td colspan="2"> </td>
</tr>
<tr>
<td width="5"> </td>
<td colspan="3">
<input type="submit" name="Submit" value="Search Now">
<input type="reset" name="Reset" value="Reset">
</td>
</tr>
</table>
</td>
</tr>
</table>
<br>
</form>
<p>
<%
      map = lcase(Server.mappath("./"))
      if gotPara = True then

            Dim strQuery ' The text of our query
            Dim objQuery ' The index server query object
            Dim rstResults ' A recordset of results returned from I.S.
            Dim objField ' Field object for loop

            ' Retreive the query from the querystring
            strQuery = para

            ' If the query isn't blank them proceed
            If strQuery <> "" Then
                  ' Create our index server object
                  Set objQuery = Server.CreateObject("ixsso.Query")

                  ' Set it's properties
                  With objQuery
                        .Catalog = "af" ' Catalog to query
                        '.Catalog = "sites" ' Catalog to query

                        if chkdoc <> "" then
                              strQuery = strQuery & " " & operator & " DocTitle like " & strQuery & " "
                        end if
                        if chksize <> "" then
                              strQuery = strQuery & " " & operator & " @Size " & fileequal & " " & filesize & " "
                        end if
                        if chkdate <> "" then
                              'strQuery = strQuery & " " & operator & " (write between '" & left(datemodified,3) & getMonth(mid(datemodified,4,2)) & right(datemodified,5) & "' And '" & left(datemodified,3) & getMonth(mid(datemodified,4,2)) & right(datemodified,5) & "') "
                              'strQuery = strQuery & " " & operator & " (write like '" & left(datemodified,3) & getMonth(mid(datemodified,4,2)) & right(datemodified,5) & "') "
                              'strQuery = strQuery & " " & operator & " (write like '" & datemodified & "') "
                              'strQuery = strQuery & " " & operator & " write between '" & cdate(datemodified) & " 00:00:00' and '" & cdate(datemodified) & " 11:59:59' "
                              'strQuery = strQuery & " " & operator & " write = 4/8/2003 5:04:11 AM "
                              'strQuery = strQuery & " " & operator & " write like 4/8/2003 "
                              strQuery = strQuery & " " & operator & " (write like " & replace(mid(datemodified,4,3),"0","") & replace(mid(datemodified,1,3),"0","") & right(datemodified,4) & ") "
                        end if
                        strQuery = strQuery & " And (not #filename search.asp)"

                        .Query = strQuery ' Query text

                        'response.write(strQuery & "<br>")
                        '.MaxRecords = 10 ' Max # of records to return

                        ' What to sort records by. I'm sorting by rank [d]
                        ' which is [d]escending by how pertinent Index Server
                        ' thinks the result is. This way the most applicable
                        ' result should be first.
'                        .SortBy = "rank [d]"
                        .SortBy = sortby & " [" & orderby & "]"
                        '.SortBy = "write [" & orderby & "]"

                        'response.write(.SortBy & "<br>")
                        'response.end

                        ' Which columns to return. Column names must
                        ' be the same as the catalog's properties. Some
                        ' of them are: contents, filename, size, path,
                        ' vpath, hitcount, rank, create, write, DocTitle
                        ' DocSubject, DocAuthor, DocKeywords...
                        .Columns = "filename, contents, path, vpath, size, " _
                              & "characterization, create, write, DocTitle, DocSubject, DocAuthor, " _
                              & "DocKeywords, rank, hitcount"
                  End With

                  ' Get a recordset of our results back from Index Server
                  Set rstResults = objQuery.CreateRecordset("nonsequential")

                  ' Get rid of our Query object
                  Set objQuery = Nothing

                  ' Check for no records
                  If rstResults.EOF Then
                        Response.Write "<font face='Arial, Helvetica, sans-serif' size='2'>Sorry. No results found.</font>"
                  Else
                        ' Print out # of results
                        Response.Write "<p><font face='Arial, Helvetica, sans-serif' size='2'><strong>"
                        Response.Write rstResults.RecordCount
                        Response.Write "</strong> results found:</font></p>"

                        %>
<table width="600" cellspacing="0" cellpadding="2">
<%

                        ' Loop through results
                        i=1
                        Do While Not rstResults.EOF

                              if i mod 2 = 0 then
                                    color1 = "#DDFFDD"
                                    color2 = "#ECFFEC"
                              else
                                    color1 = "#DDFFF9"
                                    color2 = "#F4FFFD"
                              end if
                              i=i+1

                              ' Loop through Fields
                              ' Formatting leaves something to be desired,
                              ' but it'll work for now. We'll pretty things
                              ' up and link to the content in part II.
                              'For Each objField in rstResults.Fields

                              %>
<tr>
<td width="130" bgcolor="<%=color1%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2">Filename:
</font></td>
<td width="460" bgcolor="<%=color2%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2"><a href="<%=startURL%><%=rstResults("vpath")%>"><%=rstResults( "filename" )%></a> </font></td>
</tr>
<tr>
<td width="130" bgcolor="<%=color1%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2">Subject:</font></td>
<td width="460" bgcolor="<%=color2%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2"><%=rstResults( "DocTitle" )%></font></td>
</tr>
<tr>
<td width="130" bgcolor="<%=color1%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2">Size:</font></td>
<td width="460" bgcolor="<%=color2%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2"><%=rstResults( "size" )%></font></td>
</tr>
<tr>
<td width="130" height="24" bgcolor="<%=color1%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2">Path:
</font></td>
<td width="460" height="24" bgcolor="<%=color2%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2"><%=rstResults( "path" )%></font></td>
</tr>
<tr>
<td width="130" bgcolor="<%=color1%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2">Vpath:</font></td>
<td width="460" bgcolor="<%=color2%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2"><%=rstResults( "vpath" )%></font></td>
</tr>
<tr>
<td width="130" bgcolor="<%=color1%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2">Write:</font></td>
<td width="460" bgcolor="<%=color2%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2"><%=FormatDateTime(rstResults( "Write" ),1)%></font></td>
</tr>
<tr>
<td width="130" bgcolor="<%=color1%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2">Rank:</font></td>
<td width="460" bgcolor="<%=color2%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2"><%=rstResults( "Rank" )%></font></td>
</tr>
<tr>
<td width="130" bgcolor="<%=color1%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2">Characterization:</font></td>
<td width="460" bgcolor="<%=color2%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2"><%=rstResults( "Characterization" )%></font></td>
</tr>
<tr>
<td width="130" bgcolor="<%=color1%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2">DocKeyWords:</font></td>
<td width="460" bgcolor="<%=color2%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2"><%=rstResults( "DocKeyWords" )%></font></td>
</tr>
<%

                              rstResults.MoveNext
                        Loop
                        %>
</table>
                        <%
                  End If

                  ' Kill our recordset object
                  Set rstResults = Nothing
            End If


      End if
%>

</body>
</html>

<%
      function chkopt(newstype, v, selected)
            if newstype = v then
                  chkopt = selected
            else
                  chkopt = ""
            end if
      end function

      sub debug(v)
            response.write(v)
            response.end
      end sub

      function getMonth(v)
            Select case v
            case "01"
                  getMonth = "Jan"
            case "02"
                  getMonth = "Feb"
            case "03"
                  getMonth = "Mar"
            case "04"
                  getMonth = "Apr"
            case "05"
                  getMonth = "May"
            case "06"
                  getMonth = "Jun"
            case "07"
                  getMonth = "Jul"
            case "08"
                  getMonth = "Aug"
            case "09"
                  getMonth = "Sep"
            case "10"
                  getMonth = "Oct"
            case "11"
                  getMonth = "Nov"
            case "12"
                  getMonth = "Dec"
            end select
      End function
%>

ASKER CERTIFIED SOLUTION

lavinder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

ASKER

Hi lavinder,

Still have some problem on date..

How is i want to search the files for a specific date?

Example:

i want to search from that was updated on 8th of April 2003, so i wrote:

... And (@write = 2003/04/08) And ...

But somehow this is not working! i know that the 'Write' will contains time, so how do i compare them?

Ryan Chong

ASKER

sorry problem resolved, use:

... And (@write >= 2003/04/08 00:00:00 And @write <=2003/04/08 23:59:59) And ...

But weird that Between ... And is not working.. Why?

Ryan Chong

ASKER

However, i have 1 more problem,

How do i find that 'Path' of the records that start with
'e:\folder\subfolder' ?

Ryan Chong

ASKER

thks for the comment, time to close it