asked on

ASP with Index Server question

Hello,

I'm trying to write a query to access Index server on our server.

My code (which I basically copied from a place on the web) is:

<%

Dim objConn, objRS, sqlString, strDocTitle

sqlString = "SELECT DocTitle FROM SCOPE('''/dev_goebase_com''') WHERE CONTAINS('mentoring')"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "provider=MSIDXS"

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open sqlString, objConn

Do While Not objRS.EOF
strDocTitle = objRS("DocTitle")
Response.Write(strDocTitle)
Loop

objRS.Close
Set objRS = Nothing
Set objConn = Nothing
%>

Now, this generates an error:

Microsoft OLE DB Provider for Indexing Service error '80040e14'

Incorrect syntax near '(null)'. SQLSTATE=42000

/library/newsearch.asp, line 12

Also, what I really want to do is substitute the name of a catalog to search, not a directory.

Can anybody provide guidance on how to do what I want?

Thanks,

Steve

Ryan Chong

Try change:

sqlString = "SELECT DocTitle FROM SCOPE('''/dev_goebase_com''') WHERE CONTAINS('mentoring')"

to:

sqlString = "SELECT DocTitle FROM SCOPE('" & Chr(34) & "/dev_goebase_com" & Chr(34) & "') WHERE CONTAINS('mentoring')"

Also, See my PAQ questions regarding Index Server:
https://www.experts-exchange.com/questions/20574007/Search-file-in-virtual-folder-using-Indexing-Service.html
https://www.experts-exchange.com/questions/20579509/Search-using-Indexing-Service-Cannot-compare-date.html

My resources on Index Server:
http://www.devtrain.de/artikel_705.aspx
http://support.microsoft.com/default.aspx?scid=KB;en-us;q179326
http://msdn.microsoft.com/library/default.asp?URL=/library/partbook/asp20/overviewofindexserver.htm
http://www.xefteri.com/articles/apr192002/default.aspx
http://www.hotscripts.com/ASP/Scripts_and_Components/Search_Engines/
http://www.learnasp.com/learn/indexserver.asp
http://www.asp-magazine.com/fr/iix/iix1.asp
http://www.adl.org/search/ixqlang.asp#Examples

Hope this helps

Steve Bohler

ASKER

Thanks.

I now get the error:

Microsoft OLE DB Provider for Indexing Service error '8004181d'

There is no catalog.

Ryan Chong

You need to create a Catalog in the Index Service first.

Try this:

1. Go to the Start > Programs > Administrative Tools > Computer Management
2. Click on Services and Applications, then select Indexing Service. If there is no Indexing Service, you may need to install this Component.
3. Right click the Indexing Service, then select New > Catalog.
4. Type in the Catalog Name (that will using later in your script), and the location you need to indexing.

Steve Bohler

ASKER

I already have a catalog (it's called "goebasedev").

When I inserted that between the Chr(34)'s, I still got the same error message.

Any thoughts?

Thanks,
Steve

Ryan Chong

Try:

sqlString = "SELECT DocTitle FROM SCOPE('" & Chr(34) & "/dev_goebase_com" & Chr(34) & "') WHERE FreeText(Contents,'mentoring') > 0 "

Steve Bohler

ASKER

I'm afraid the error message is the same.

Our catalog's name is goebasedev. Is there a place I should be specifying that?

Steve

Ryan Chong

Try other example on the links i posted above, which may use other technology (ixsso.Query - Index Server Object) to doing the File Indexing?

In additional, here is an example i had done few years ago:

Name the file as search.asp:

<%
      Server.ScriptTimeout = 500
      'on error resume next
      startURL = "http://www.mydomain.net/af"
      startURL1 = "http://www.mydomain.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("./"))
      map1 = "c:\sites\mydomain.com.sg\www\"
      'response.write("map: " & map)
      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 = "goebasedev" ' Catalog to query

                        if chkdoc <> "" then
                              strQuery = strQuery & " " & operator & " DocTitle like " & strQuery & " "
                        end if
                        if chksize <> "" then
                              strQuery = strQuery & " " & operator & " @Size " & fileequal & " " & CInt(filesize) * 1024 & " "
                        end if
                        if chkdate <> "" then
                              converteddate = right(datemodified,4) & "/" & mid(datemodified,4,2) & "/" & mid(datemodified,1,2)
                              strQuery = strQuery & " " & operator & " (@write >= " & converteddate & " 00:00:00 And @write <=" & converteddate & " 23:59:59) "
                        end if
                        strQuery = strQuery & " And (not #filename search.asp) And #filename *.|(htm|,html|,shtml|,asp|,aspx|) And #path " & map & "*"

                        .Query = strQuery ' Query text

                        'response.write(strQuery & "<br>")
                        .MaxRecords = 300 ' 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 = sortby & " [" & 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">
<%
       if instr(rstResults("Path"),map)>0 then
                  apath = Replace(rstResults("Path"),map,"")
                  fURL = startURL
            else
                  apath = Replace(rstResults("Path"),map1,"/")
                  fURL = startURL1
            end if
      %>
<a href="<%=fURL%><%=apath%>" target="_blank"><%=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">Rank:</font></td>
<td width="460" bgcolor="<%=color2%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2"><%=Round(rstResults( "Rank" )/10,2)%> %</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"><%=Int(cInt(rstResults( "size" ))/1024)%> k</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">Characterization:</font></td>
<td width="460" bgcolor="<%=color2%>" valign="top"><font face="Arial, Helvetica, sans-serif" size="2"><%=rstResults( "Characterization" )%></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
%>

Try customize the code to run..

Hope this works

ASKER CERTIFIED SOLUTION

modulo

membership

This solution is only available to members.

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

Start Free Trial