<% const pageSize = 30 dim orderby, category, keyword, where, browseall, page, comments, author if Request.QueryString = "repeat" or len(Request.QueryString("page")) > 0 then orderby = Session("lastsearch_orderby") category = MyCInt(Session("lastsearch_category")) keyword = Session("lastsearch_keyword") comments = Session("lastsearch_comments") author = Session("lastsearch_author") if Request.QueryString = "repeat" then page = MyCInt(Session("lastsearch_page")) else page = MyCInt(Request.QueryString("page")) end if else orderby = Request.QueryString("orderby") category = MyCInt(Request.QueryString("category")) keyword = SQLencode(Request.QueryString("keyword")) comments = Request.QueryString("comments") author = SQLencode(Request.QueryString("author")) end if Session("lastsearch_orderby") = orderby Session("lastsearch_category") = category Session("lastsearch_keyword") = keyword Session("lastsearch_comments") = comments Session("lastsearch_author") = author if Len(Request.QueryString("category")) > 0 then pagetitle = "Search Results" else pagetitle = "Browse Lessons" end if function min(x, y) if (x > y) then min = y else min = x end function %> Lessons Learned > Browse
" end if dim sorturl sorturl = Request.ServerVariables("SCRIPT_NAME") & "?category=" & Server.URLEncode(Request.QueryString("category")) & _ "&keyword=" & Server.URLEncode(Request.QueryString("keyword")) & "&orderby=" %>

<%=pagetitle %>

<% if orderby = "LTimestamp" then orderby = "LTimestamp DESC" if orderby = "LastPost" then orderby = "LastPost DESC" if orderby <> "LTimestamp DESC" AND orderby <> "LSubject" AND orderby <> "CTitle" AND orderby <> "LastPost DESC" AND orderby <> "ULastName,UFirstName" then orderby = "LTimestamp DESC" if orderby = "LSubject" then secondorderby = "" else secondorderby = ",LSubject" end if dim conn, rs, sql set conn = Server.CreateObject("ADODB.Connection") conn.Open connectionStr set rs = Server.CreateObject("ADODB.RecordSet") const ignoredWords = " $ 0 1 2 3 4 5 6 7 8 9 a about after all also an and another any are as at b be because been before being between both but by c came can come could d did do e each f for from g get got h had has have he her here him himself his how i if in into is it j k l like m make many me might more most much must my n near never not now o of on only or other our out over p q r s said same see should since some still such t take than that the their them then there these they this those through to too u under up v very w was way we well were what where which while who with would x y you your z " ' The keyword search clause will contain chr(0) placeholders which need to be replaced ' with the table name the keyword search is performed on. This allows the same where clause ' to be used in the lessons and in the discussions table full-text search. chr(1) needs ' to be replaced with the appropriate user field (DUser or LUser) if len(author) > 0 then where = where & " AND " & chr(1) & " IN (SELECT UID FROM [User] WHERE UFirstname LIKE '%" & author & _ "%' OR ULastName LIKE '%" & author & "%')" end if if len(keyword) > 0 then dim akeyword, i akeyword = split(lcase(escapesinglequotes(keyword)), " ") keyword = "" for i = LBound(akeyword) to UBound(akeyword) if instr(ignoredWords, " " & akeyword(i) & " ") = 0 then if len(keyword) > 0 then keyword = keyword & " AND " keyword = keyword & "FORMSOF (INFLECTIONAL, " & lcase(akeyword(i)) & ")" end if next where = where & " AND CONTAINS (" & chr(0) & ".*, '" & keyword & "')" end if dim LIDs, aLIDs, iLID LIDs = "" if (comments = "ON") AND (len(where) > 0) then sql = "SELECT DISTINCT DLesson FROM Discussion WHERE " & replace(replace(mid(where, 5), chr(0), "Discussion"), chr(1), "DUser") on error resume next rs.Open sql, conn, adOpenKeyset, adLockReadOnly if err.number = 0 then if not rs.EOF then aLIDs = rs.GetRows() for iLID = LBound(aLIDs, 2) to UBound(aLIDs, 2) LIDs = LIDs & "," & aLIDs(0, iLID) next LIDs = mid(LIDs, 2) end if end if rs.close on error goto 0 end if if len(LIDs) > 0 then where = " AND ((" & mid(where, 5) & ") OR LID IN (" & LIDs & "))" end if if category <> 0 then if category = -1 then where = where & " AND (CApproved = 0)" else where = where & " AND (CID = " & category & ")" end if end if if len(where) > 0 then where = " WHERE" & mid(where, 5) 'sql = "SELECT LTimestamp,LSubject,LRevealUser,LRevealOrg,LHitCount,UEmail,UFirstName,ULastName,UOrganization,CTitle,CApproved,CID,LID " & _ ' "FROM Lesson INNER JOIN Category ON LCategory = CID INNER JOIN [User] ON LUser = UID" & _ ' replace(replace(where, chr(0), "Lesson"), chr(1), "LUser") & " ORDER BY " & orderby sql = "SELECT LTimestamp,LSubject,LRevealUser,LRevealOrg,LHitCount,UID,UEmail,UFirstName,ULastName,UOrganization,CTitle,CApproved,CID,LID,LastPost,RUID,RFirstName,RLastName,NumReplies " & _ "FROM Lesson INNER JOIN Category ON LCategory = CID INNER JOIN [User] ON LUser = UID LEFT JOIN [Last Discussion Reply and Author] ON LID = RLID" & _ replace(replace(where, chr(0), "Lesson"), chr(1), "LUser") & " ORDER BY " & orderby & secondorderby 'response.write sql dim nokeywords 'on error resume next rs.Open sql, conn, adOpenKeyset, adLockReadOnly nokeywords = (err.Number <> 0) 'on error goto 0 if nokeywords then page = 0 else if (page < 0) then page = 0 if (page * pageSize + 1 > rs.RecordCount) then page = 0 if (page > 0) then rs.move page * pageSize end if dim morepages, nextpagesize, pagingRow if not nokeywords then morepages = (page > 0) or (not rs.EOF) else morepages = false end if pagingRow = "" if morepages and ((page > 0) or (not rs.EOF and min(pageSize, rs.RecordCount - (page + 1) * pageSize) > 0) ) then pagingRow = vbNewline & "
" if (page > 0) then pagingRow = pagingRow & "Previous " & pageSize & " Lessons" end if pagingRow = pagingRow & "  " if not rs.EOF and min(pageSize, rs.RecordCount - (page + 1) * pageSize) > 0 then nextpagesize = min(pageSize, rs.RecordCount - (page + 1) * pageSize) myS = IIf(nextpagesize <> 1, "s", "") pagingRow = pagingRow & "Next " & nextpagesize & " Lesson" & myS & "" end if pagingRow = pagingRow & "
<%= pagingRow %> <% if nokeywords then %> <% elseif rs.EOF then %> <% else dim count count = 0 while not rs.EOF and (count < pageSize) %> <% rs.moveNext count = count + 1 wend end if if not nokeywords then rs.Close rs = null end if %> <%= pagingRow%>
Category [<% if orderby <> "CTitle" then %>sort<% else %>sort<% end if %>] Subject [<% if orderby <> "LSubject" then %>sort<% else %>sort<% end if %>] Date
added
 [<% if orderby <> "LTimestamp DESC" then %>sort<% else %>sort<% end if %>]
Author [<% if orderby <> "ULastName,UFirstName" then %>sort<% else %>sort<% end if %>] Views Replies Last Post [<% if orderby <> "LastPost DESC" then %>sort<% else %>sort<% end if %>]
Your search returned 0 results, please use more specific keywords. Go back and revise your search.
Your search returned 0 results. Go back and revise your search.
<% if rs("CApproved") = 0 then Response.Write "Other" else Response.Write rs("CTitle") %>  " class="ongray"><%= rs("LSubject") %>  <%= FormatDateTime(rs("LTimestamp"), vbShortDate) %>  " class="ongray"><%= rs("UFirstName") & " " & rs("ULastName")%> <% if rs("LRevealOrg") then Response.Write " (" & rs("UOrganization") & ")" %>   <%=rs("LHitCount")%>  <% if IsNull(rs("NumReplies")) then Response.Write "0" else Response.Write rs("NumReplies") %>  <% if IsNull(rs("LastPost")) then Response.Write " " else Response.Write FormatDateTime(rs("LastPost"), vbShortDate) & " (" & rs("RFirstName") & " " & rs("RLastName") & ")" %> 
<% conn.Close conn = null Session("lastsearch_page") = page %>