Advanced Index Server Search Form <% Const STAT_BUSY = 0 Const STAT_ERROR = &H1 Const STAT_DONE = &H2 Const STAT_REFRESH = &H3 Const STAT_PARTIAL_SCOPE = &H8 Const STAT_NOISE_WORDS = &H10 Const STAT_CONTENT_OUT_OF_DATE = &H20 Const STAT_REFRESH_INCOMPLETE = &H40 Const STAT_CONTENT_QUERY_INCOMPLETE = &H80 Const STAT_TIME_LIMIT_EXCEEDED = &H100 Function GetCiOutOfDate(value) GetCiOutOfDate = value And STAT_CONTENT_OUT_OF_DATE end Function Function GetCiQueryIncomplete(value) GetCiQueryIncomplete = value And STAT_CONTENT_QUERY_INCOMPLETE end Function Function GetCiQueryTimedOut(value) GetCiQueryTimedOut = value And STAT_TIME_LIMIT_EXCEEDED end Function DebugFlag = FALSE NewQuery = FALSE UseSavedQuery = FALSE SearchString = "" QueryForm = Request.ServerVariables("PATH_INFO") if Request.ServerVariables("REQUEST_METHOD") = "POST" then SearchString = Request.Form("SearchString") DocAuthorRestriction = Request.Form("DocAuthorRestriction") FSRest = Request.Form("FSRest") FSRestVal = Request.Form("FSRestVal") FSRestOther = Request.Form("FSRestOther") FMMod = Request.Form("FMMod") FMModDate = Request.Form("FMModDate") SortBy = Request.Form("SortBy") Colset = Request.Form("ColChoice") ' NOTE: this will be true only if the button is actually pushed. if Request.Form("Action") = "Execute" then NewQuery = TRUE elseif Request.Form("pg") <> "" then UseSavedQuery=TRUE NextPageNumber = Request.Form("pg") end if End if %>

Advanced SQL Sample

Enter your query:
Document author:
Where File Size is:
Modified:
SQL Syntax Tips

<%if DebugFlag then%> 
    SearchString         = <%=SearchString%>
    DocAuthorRestriction = <%=DocAuthorRestriction%>
    FSRest               = <%=FSRest%>
    FSRestVal            = <%=FSRestVal%>
    FSRestOther          = <%=FSRestOther%>
    FMMod                = <%=FMMod%>
    FMModDate            = <%=FMModDate%>
    SortBy               = <%=SortBy%>
    Colset               = <%=Colset%>
    NewQuery             = <%=CStr(NewQuery)%>
<%end if%> <% if NewQuery then ' First Reset the Connection, command and recordSet Object. set session("AdoConnection") = nothing set session("AdoCommand") = nothing set session("Recordset") = nothing NextRecordNumber = 1 set AdoConnection = Server.CreateObject("ADODB.Connection") set AdoCommand = Server.CreateObject("ADODB.Command") AdoConnection.ConnectionString = "provider=msidxs" AdoConnection.Open set AdoCommand.ActiveConnection = AdoConnection SelectColumns = "DocTitle, vpath, path, filename, size, write, characterization" SelectString = "Select " + SelectColumns + " from Scope() " Composer = "" TheQuery = "" if SearchString <> "" then TheQuery = "contains('""" + SearchString + """')>0" Composer = " and " end if if FSRestVal <> "any" then if FSRestVal <> "other" then if FSRestVal <> "" then TheQuery = "Size " + FSRest + FSRestVal + " " + Composer + TheQuery end if else TheQuery = "Size " + FSRest + FSRestOther + " " + Composer + TheQuery end if Composer = " and " end if if DocAuthorRestriction <> "" then TheQuery = "Contains(DocAuthor, '""" + DocAuthorRestriction + """')>0 " + Composer + TheQuery Composer = " and " end if if FMMod <> "" AND FMMod <> "any" then if FMMod <> "since" then TheQuery = "(Write > " + FMMod + ") " + Composer + TheQuery else TheQuery = "(Write > '" + FMModDate + "') " + Composer + TheQuery end if end if if TheQuery <> "" then SelectString = SelectString + " where " + TheQuery +" "+ SortBy else SelectString = SelectString + SortBy +" " end if AdoCommand.CommandText = SelectString set RS = Server.CreateObject("AdoDB.Recordset") RS.CursorType = adOpenKeyset AdoCommand.Properties("Bookmarkable") = True RS.MaxRecords=300 RS.Open AdoCommand RS.PageSize = 10 ActiveQuery = TRUE %> <% elseif UseSavedQuery then if IsObject( Session("AdoConnection") ) And IsObject( Session("AdoCommand")) AND IsObject( Session("RecordSet") ) then set AdoConnection = Session("AdoConnection") set AdoCommand = Session("AdoCommand") set RS = Session("RecordSet") if RS.RecordCount <> -1 and NextPageNumber <> -1 then RS.AbsolutePage = NextPageNumber NextRecordNumber = RS.AbsolutePosition end if ActiveQuery = TRUE else Response.Write "ERROR - No saved query" end if end if %> <% if ActiveQuery then if not RS.EOF then LastRecordOnPage = NextRecordNumber + RS.PageSize - 1 CurrentPage = RS.AbsolutePage if RS.RecordCount <> -1 AND RS.RecordCount < LastRecordOnPage then LastRecordOnPage = RS.RecordCount end if Response.Write "Documents " & NextRecordNumber & " to " & LastRecordOnPage if RS.RecordCount <> -1 then Response.Write " of " & RS.RecordCount end if Response.Write " matching the query: " Response.Write "

" & chr(34) & AdoCommand.CommandText & "" & chr(34) & ".

" %> <% Do While Not RS.EOF and NextRecordNumber <= LastRecordOnPage %> <% ' This is the detail portion for Title, Abstract, URL, Size, and ' Modification Date. ' If there is a title, display it, otherwise display the virtual path. %> <% RS.MoveNext NextRecordNumber = NextRecordNumber+1 Loop %>
<%= NextRecordNumber%>. <%if VarType(RS("DocTitle")) = 1 or RS("DocTitle") = "" then%> "><%= Server.HTMLEncode( RS("filename") )%> <%else%> "><%= Server.HTMLEncode(RS("DocTitle"))%> <%end if%>
<%if VarType(RS("characterization")) = 8 and RS("characterization") <> "" then%> Abstract: <%= Server.HTMLEncode(RS("characterization"))%>
<%end if%>
" class="RecordStats" style="color:blue;">http://<%=Request("server_name")%><%=RS("vpath")%>
<%if RS("size") = "" then%>(size and time unknown)<%else%>size <%=RS("size")%> bytes - <%=RS("write")%> GMT<%end if%> <%if SearchString <> "" then%> <% QueryString = CStr(RS.Properties("Query Restriction")) %>
<% ' Construct the URL for hit highlighting WebHitsQuery = "CiWebHitsFile=" & Server.URLEncode( RS("vpath") ) WebHitsQuery = WebHitsQuery & "&CiRestriction=" & Server.URLEncode( QueryString ) WebHitsQuery = WebHitsQuery & "&CiBeginHilite=" & Server.URLEncode( "" ) WebHitsQuery = WebHitsQuery & "&CiEndHilite=" & Server.URLEncode( "" ) WebHitsQuery = WebHitsQuery & "&CiUserParam3=" & QueryForm %> Highlight matching terms in document using Summary mode. Summary Highlight matching terms in document. Full <%end if%>
<% else if NextRecordNumber = 1 then Response.Write "No documents matched the query

" else Response.Write "No more documents in the query

" end if end if ' NOT RS.EOF %> <%if GetCiOutofDate(RS.Properties("Rowset Query Status")) then%>

The index is out of date. <%end if%> <%if GetCiQueryIncomplete(RS.Properties("Rowset Query Status")) then%>

The query is too expensive to complete. <%end if%> <%if GetCiQueryTimedOut(RS.Properties("Rowset Query Status")) then%>

The query took too long to complete. <%end if%> <%SaveQuery = FALSE%> <%if CurrentPage > 1 and RS.RecordCount <> -1 then ' BUGBUG - use RS.Supports(adMovePrevious)%> <%SaveQuery = TRUE%> <%end if%> <%if Not RS.EOF then%> <%SaveQuery = TRUE%> <%end if%>
<% NextString = "Next " if RS.RecordCount <> -1 then NextSet = (RS.RecordCount - NextRecordNumber) + 1 if NextSet > RS.PageSize then NextSet = RS.PageSize end if NextString = NextString & NextSet & " documents" else NextString = NextString & " page of documents" end if %>
Page <%=CurrentPage%> <% if RS.PageCount <> -1 then Response.Write " of " & RS.PageCount end if ' If either of the previous or back buttons were displayed, save ' the query and the recordset in session variables. if SaveQuery then set Session("AdoConnection") = AdoConnection set Session("AdoCommand") = AdoCommand set Session("RecordSet") = RS else RS.close AdoConnection.Close Set RS = Nothing Set AdoCommand = Nothing Set AdoConnection = Nothing set Session("AdoConnection") = Nothing set Session("AdoCommand") = Nothing set Session("RecordSet") = Nothing end if end if %>