Solved

Building a Keyword Search in ASP - Having trouble constructing array

Posted on 2004-08-06
10
405 Views
Last Modified: 2008-01-16
Hello:

I have been struggling this week with piecing together code to put together a keyword search of a database using ASP.

I downloaded a version of a search engine class that someone had posted on the internet and successfully adapted it to pull from my database. However, in trying to pull it into my display page, I could not get it to cooperate and pull from my database. I'm not sure how much of the code you need to see to help, but I have a feeling it is because some of the information in my original search page (which is based on a list/menu) is redundant with the class file. Theoretically I know what I want to do and I feel like it should be easy to have the computer read the input, parse it, match it against the fields in the database and return a result as an array...but it hasn't worked out that way!

I guess I have to attach all the code...and I apologize in advance because it is lengthy...

In additiion to some tips as to how I might fix this to properly create a keyword search, I guess I would like to know if it is better in general to use the class file or to just build necessary functions into the search page. The other searches that are built in the site do not use classes. Thanks for your help!!!

first...the search file

<%@LANGUAGE="VBSCRIPT"%>

<!--#include file="./cls_dbsearchengine.asp"-->

<%
'variable declaration
Dim rsSearchAll, ct, results, noCriteria

'create instance of the class
Set rsSearchAll = New DBSearchEngine
With rsSearchAll

      'db connection string
      .DBConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\Web\database\tillberg\aahv2.mdb;User ID=Admin;Password="
      
      'search type
      .SearchType = Request("type")

      'determine what to search
      if request("OrganizationName") = "on" then .SearchTitles
      if request("HistoricalSignificance") = "on" then .SearchDescriptions

      'gather results into a string
      results = .Search( request("keywords") )

      'get the count of records returned
      ct = .Count

      'find out whether or not the minimum search criteria
      'was entered...
      noCriteria = .bNoFieldsToSearch
End With

'free class instance (failure to explicitly free objects in
'ASP is generally considered a bad idea)
Set rsSearchAll = Nothing

'write the search results to the browser...
With Response
      if noCriteria then .Write("<p>NO FIELDS SELECTED, SEARCH NOT REFINED!</p>")
      .Write(ct & " records found.<BR>")
      .Write(results)
End With
%>


<%
Dim Repeat1__numRows
Repeat1__numRows = 10
Dim Repeat1__index
Repeat1__index = 0
rsSearchAll_numRows = rsSearchAll_numRows + Repeat1__numRows
%>
<%
'  *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

' set the record count


Dim rsSearchAll_total
rsSearchAll_total = rsSearchAll.RecordCount

' set the number of rows displayed on this page
If (rsSearchAll_numRows < 0) Then
  rsSearchAll_numRows = rsSearchAll_total
Elseif (rsSearchAll_numRows = 0) Then
  rsSearchAll_numRows = 1
End If

' set the first and last displayed record
rsSearchAll_first = 1
rsSearchAll_last  = rsSearchAll_first + rsSearchAll_numRows - 1

' if we have the correct record count, check the other stats
If (rsSearchAll_total <> -1) Then
  If (rsSearchAll_first > rsSearchAll_total) Then rsSearchAll_first = rsSearchAll_total
  If (rsSearchAll_last > rsSearchAll_total) Then rsSearchAll_last = rsSearchAll_total
  If (rsSearchAll_numRows > rsSearchAll_total) Then rsSearchAll_numRows = rsSearchAll_total
End If
%>
<%
' *** Recordset Stats: if we don't know the record count, manually count them

If (rsSearchAll_total = -1 AND (NOT rsSearchAll.EOF)) Then

  ' count the total records by iterating through the recordset
  rsSearchAll_total=0
  While (Not rsSearchAll.EOF)
    rsSearchAll_total = rsSearchAll_total + 1
    rsSearchAll.MoveNext
  Wend

  ' reset the cursor to the beginning
  If (rsSearchAll.CursorType > 0) Then
    rsSearchAll.MoveFirst
  Else
    rsSearchAll.Requery
  End If

  ' set the number of rows displayed on this page
  If (rsSearchAll_numRows < 0 Or rsSearchAll_numRows > rsSearchAll_total) Then
    rsSearchAll_numRows = rsSearchAll_total
  End If

  ' set the first and last displayed record
  rsSearchAll_first = 1
  rsSearchAll_last = rsSearchAll_first + rsSearchAll_numRows - 1
  If (rsSearchAll_first > rsSearchAll_total) Then rsSearchAll_first = rsSearchAll_total
  If (rsSearchAll_last > rsSearchAll_total) Then rsSearchAll_last = rsSearchAll_total

End If
%>
<%
' *** Move To Record and Go To Record: declare variables

Set MM_rs    = rsSearchAll
MM_rsCount   = rsSearchAll_total
MM_size      = rsSearchAll_numRows
MM_uniqueCol = ""
MM_paramName = ""
MM_offset = 0
MM_atTotal = false
MM_paramIsDefined = false
If (MM_paramName <> "") Then
  MM_paramIsDefined = (Request.QueryString(MM_paramName) <> "")
End If
%>
<%
' *** Move To Record: handle 'index' or 'offset' parameter

if (Not MM_paramIsDefined And MM_rsCount <> 0) then

  ' use index parameter if defined, otherwise use offset parameter
  r = Request.QueryString("index")
  If r = "" Then r = Request.QueryString("offset")
  If r <> "" Then MM_offset = Int(r)

  ' if we have a record count, check if we are past the end of the recordset
  If (MM_rsCount <> -1) Then
    If (MM_offset >= MM_rsCount Or MM_offset = -1) Then  ' past end or move last
      If ((MM_rsCount Mod MM_size) > 0) Then         ' last page not a full repeat region
        MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
      Else
        MM_offset = MM_rsCount - MM_size
      End If
    End If
  End If

  ' move the cursor to the selected record
  i = 0
  While ((Not MM_rs.EOF) And (i < MM_offset Or MM_offset = -1))
    MM_rs.MoveNext
    i = i + 1
  Wend
  If (MM_rs.EOF) Then MM_offset = i  ' set MM_offset to the last possible record

End If
%>
<%
' *** Move To Record: if we dont know the record count, check the display range

If (MM_rsCount = -1) Then

  ' walk to the end of the display range for this page
  i = MM_offset
  While (Not MM_rs.EOF And (MM_size < 0 Or i < MM_offset + MM_size))
    MM_rs.MoveNext
    i = i + 1
  Wend

  ' if we walked off the end of the recordset, set MM_rsCount and MM_size
  If (MM_rs.EOF) Then
    MM_rsCount = i
    If (MM_size < 0 Or MM_size > MM_rsCount) Then MM_size = MM_rsCount
  End If

  ' if we walked off the end, set the offset based on page size
  If (MM_rs.EOF And Not MM_paramIsDefined) Then
    If (MM_offset > MM_rsCount - MM_size Or MM_offset = -1) Then
      If ((MM_rsCount Mod MM_size) > 0) Then
        MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
      Else
        MM_offset = MM_rsCount - MM_size
      End If
    End If
  End If

  ' reset the cursor to the beginning
  If (MM_rs.CursorType > 0 AND (NOT rsSearchAll.EOF)) Then
    MM_rs.MoveFirst
  Else
    MM_rs.Requery
  End If

  ' move the cursor to the selected record
  i = 0
  While (Not MM_rs.EOF And i < MM_offset)
    MM_rs.MoveNext
    i = i + 1
  Wend
End If
%>
<%
' *** Move To Record: update recordset stats

' set the first and last displayed record
rsSearchAll_first = MM_offset + 1
rsSearchAll_last  = MM_offset + MM_size
If (MM_rsCount <> -1) Then
  If (rsSearchAll_first > MM_rsCount) Then rsSearchAll_first = MM_rsCount
  If (rsSearchAll_last > MM_rsCount) Then rsSearchAll_last = MM_rsCount
End If

' set the boolean used by hide region to check if we are on the last record
MM_atTotal = (MM_rsCount <> -1 And MM_offset + MM_size >= MM_rsCount)
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters

' create the list of parameters which should not be maintained
MM_removeList = "&index="
If (MM_paramName <> "") Then MM_removeList = MM_removeList & "&" & MM_paramName & "="
MM_keepURL="":MM_keepForm="":MM_keepBoth="":MM_keepNone=""

' add the URL parameters to the MM_keepURL string
For Each Item In Request.Search
  NextItem = "&" & Item & "="
  If (InStr(1,MM_removeList,NextItem,1) = 0) Then
    MM_keepURL = MM_keepURL & NextItem & Server.URLencode(Request.Search(Item))
  End If
Next

' add the Form variables to the MM_keepForm string
For Each Item In Request.Form
  NextItem = "&" & Item & "="
  If (InStr(1,MM_removeList,NextItem,1) = 0) Then
    MM_keepForm = MM_keepForm & NextItem & Server.URLencode(Request.Form(Item))
  End If
Next

' create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
if (MM_keepBoth <> "") Then MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
if (MM_keepURL <> "")  Then MM_keepURL  = Right(MM_keepURL, Len(MM_keepURL) - 1)
if (MM_keepForm <> "") Then MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)

' a utility function used for adding additional parameters to these strings
Function MM_joinChar(firstItem)
  If (firstItem <> "") Then
    MM_joinChar = "&"
  Else
    MM_joinChar = ""
  End If
End Function
%>
<%
' *** Move To Record: set the strings for the first, last, next, and previous links

MM_keepMove = MM_keepBoth
MM_moveParam = "index"

' if the page has a repeated region, remove 'offset' from the maintained parameters
If (MM_size > 0) Then
  MM_moveParam = "offset"
  If (MM_keepMove <> "") Then
    params = Split(MM_keepMove, "&")
    MM_keepMove = ""
    For i = 0 To UBound(params)
      nextItem = Left(params(i), InStr(params(i),"=") - 1)
      If (StrComp(nextItem,MM_moveParam,1) <> 0) Then
        MM_keepMove = MM_keepMove & "&" & params(i)
      End If
    Next
    If (MM_keepMove <> "") Then
      MM_keepMove = Right(MM_keepMove, Len(MM_keepMove) - 1)
    End If
  End If
End If

' set the strings for the move to links
If (MM_keepMove <> "") Then MM_keepMove = MM_keepMove & "&"
urlStr = Request.ServerVariables("URL") & "?" & MM_keepMove & MM_moveParam & "="
MM_moveFirst = urlStr & "0"
MM_moveLast  = urlStr & "-1"
MM_moveNext  = urlStr & Cstr(MM_offset + MM_size)
prev = MM_offset - MM_size
If (prev < 0) Then prev = 0
MM_movePrev  = urlStr & Cstr(prev)
%>

<html>

<head>
<meta http-equiv="content-type" content="text/html;charset=ISO-8859-1">
<title>African American Heritage in Virginia</title>
<link rel="stylesheet" href="aahv.css">
<script language="JavaScript" src="nav.js"></script>
</head>

<body bgcolor="#cc9933" text="#363636" link="#336666" alink="#996600" vlink="#663300" leftmargin="0" marginwidth="0" topmargin="0" marginheight="0">
<table border="0" cellpadding="1" cellspacing="0" width="95%" bgcolor="#996600">
  <td height="90">
  <tr>
    <td> <table border="0" cellpadding="0" cellspacing="0" width="100%" bgcolor="#cc9933">
        <tr height="50">
          <td width="116" rowspan="3" background="images/chrome_sidebar.gif" valign="top"><a href="http://www.virginia.edu/vfh/aahv/"><img src="images/logo_aahv.gif" width="116" height="147" border="0" alt="AAHV logo"></a><img src="images/pixel.gif" width="116" height="7" border="0">
            <table border="0" cellpadding="0" cellspacing="0" width="115" height="154">
              <tr height="26">
                <td width="115" height="26"><a href="http://www.virginia.edu/vfh/aahv/index.html" onMouseOver="imgOn('homeLINK')" onMouseOut="imgOff('homeLINK')" title="AAHV Home Page"><img src="images/nav_home_00.gif" width="115" height="26" border="0" name="homeLINK" alt="Back to AAHV Home Page"></a></td>
              </tr>
              <tr height="26">
                <td width="115" height="26"><a href="http://www.virginia.edu/vfh/aahv/about.html" onMouseOver="imgOn('aboutLINK')" onMouseOut="imgOff('aboutLINK')" title="About AAHV"><img src="images/nav_about_00.gif" width="115" height="26" border="0" alt="About AAHV" name="aboutLINK"></a></td>
              </tr>
              <tr height="26">
                <td width="115" height="26"><a href="http://www.virginia.edu/vfh/aahv/data.html" onMouseOver="imgOn('dataLINK')" onMouseOut="imgOff('dataLINK')" title="Heritage Sites Database"><img src="images/nav_data_00.gif" width="115" height="26" border="0" alt="Heritage Sites Database" name="dataLINK"></a></td>
              </tr>
              <tr height="26">
                <td width="115" height="26"><a href="http://www.virginia.edu/vfh/aahv/grants.html" onMouseOver="imgOn('grantsLINK')" onMouseOut="imgOff('grantsLINK')" title="Mini-Grants Program"><img src="images/nav_grants_00.gif" width="115" height="26" border="0" alt="Mini-Grants Program" name="grantsLINK"></a></td>
              </tr>
              <tr height="26">
                <td height="26" width="115"><a href="http://www.virginia.edu/vfh/aahv/exhibit.html" onMouseOver="imgOn('exhibitLINK')" onMouseOut="imgOff('exhibitLINK')" title="Exhibit"><img src="images/nav_exhibit_00.gif" width="115" height="26" border="0" alt="Exhibit - &quot;Don't Grieve After Me&quot;" name="exhibitLINK"></a></td>
              </tr>
              <tr height="24">
                <td height="24" width="115"><a href="http://www.virginia.edu/vfh/aahv/trails.html" onMouseOver="imgOn('trailsLINK')" onMouseOut="imgOff('trailsLINK')" title="Heritage Trails &amp; Tourism"><img src="images/nav_trails_00.gif" width="115" height="24" border="0" alt="Heritage Trails &amp; Tourism" name="trailsLINK"></a></td>
              </tr>
            </table>
            <br> <br> </td>
          <td valign="top" height="50" width="100%"> <table border="0" cellpadding="0" cellspacing="0" width="100%" height="53">
              <tr height="13">
                <td background="images/banner_chrome_top.gif" height="13" width="100%"><img src="images/banner_chrome_top.gif" width="1" height="13" border="0"></td>
                <td rowspan="3"><img src="images/banner_cap.gif" width="10" height="53" border="0" alt="banner cap"></td>
              </tr>
              <tr height="35">
                <td width="100%" height="35" bgcolor="#996600"><a href="http://www.virginia.edu/vfh/aahv/index.html"><img src="images/banner_aahv.gif" width="494" height="35" border="0" alt="African American Heritage in Virginia"></a></td>
              </tr>
              <tr height="5">
                <td height="5" background="images/banner_chrome_bottom.gif" width="100%"><img src="images/banner_chrome_bottom.gif" width="1" height="5" border="0"></td>
              </tr>
            </table></td>
        </tr>
        <tr>
          <td width="100%" valign="top"> <table border="0" cellpadding="6" cellspacing="0" width="100%">
              <tr>
                <td valign="top"> <table border="0" cellpadding="2" cellspacing="0" width="100%" bgcolor="#336666">
                    <tr>
                      <td valign="top"> <table border="0" cellpadding="10" cellspacing="0" width="100%" bgcolor="#ffffea">
                          <tr>
                            <td valign="top"> <p><span class="header1">Search
                                Results: Sites &amp; By Keywords </span><br>
                                <span class="header2"><%=(Request.QueryString("Keywords"))%></span></p>
                              <% If rsSearchAll.EOF And rsSearchAll.BOF Then %>
                              <p align="center"><br>
                                <span class="header2">No matching records found<br>
                                </span><a href="http://www.virginia.edu/vfh/aahv/data.html"><br>
                                Search again</a><br>
                                <br>
                                &nbsp; <br>
                                &nbsp; <br>
                                &nbsp; <br>
                                &nbsp; <br>
                                &nbsp; <br>
                                &nbsp; <br>
                                &nbsp; <br>
                              </p>
                              <% End If ' end rsSearchAll.EOF And rsSearchAll.BOF %>
                              <% If Not rsSearchAll.EOF Or Not rsSearchAll.BOF Then %>
                              <p><b>Displaying Records <%=(rsSearchAll_first)%>
                                to <%=(rsSearchAll_last)%> of <%=(rsSearchAll_total)%></b>
                                <br>
                              <table width="98%" border="1" cellspacing="0" cellpadding="6">
                                <tr bgcolor="#990000">
                                  <td width="45%" height="14" class="table_caption">Keywords</td>
                                  <td width="30%" height="14" class="table_caption">Site</td>
                                  <td height="14" width="25%" class="table_caption">City</td>
                                </tr>
                                <%
While ((Repeat1__numRows <> 0) AND (NOT rsSearchAll.EOF))
%>
                                <tr valign="top">
                                  <td width="45%" height="14"><%=results = NewKeywordArray%>
                                  </td>
                                  <td><b><a href="sites.asp?<%= MM_keepNone & MM_joinChar(MM_keepNone) & "MailingListID=" & rsSearchAll.Fields.Item("MailingListID").Value %>"><%=(rsSearchAll.Fields.Item("OrganizationName").Value)%></a></td>
                                                  <td><%=(rsSearchAll.Fields.Item("City").Value)%></table></td>
                                                  <td>
                            <td height="14" width="15%">&nbsp;</td>
                          </tr>
                          <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsSearchAll.MoveNext()
Wend
%>
                        </table>
                        <br> <table border="0" width="50%" align="center">
                          <tr>
                            <td width="23%" align="center">
                              <% If MM_offset <> 0 Then %>
                              <a href="<%=MM_moveFirst%>">First</a>
                              <% End If ' end MM_offset <> 0 %>
                            </td>
                            <td width="31%" align="center">
                              <% If MM_offset <> 0 Then %>
                              <a href="<%=MM_movePrev%>">Previous</a>
                              <% End If ' end MM_offset <> 0 %>
                            </td>
                            <td width="23%" align="center">
                              <% If Not MM_atTotal Then %>
                              <a href="<%=MM_moveNext%>">Next</a>
                              <% End If ' end Not MM_atTotal %>
                            </td>
                            <td width="23%" align="center">
                              <% If Not MM_atTotal Then %>
                              <a href="<%=MM_moveLast%>">Last</a>
                              <% End If ' end Not MM_atTotal %>
                            </td>
                          </tr>
                        </table>
                        <% End If ' end Not rsSearchAll.EOF Or NOT rsSearchAll.BOF %>
                      </td>
                    </tr>
                  </table></td>
              </tr>
            </table></td>
        </tr>
      </table></td>
  </tr>
  <tr height="20">
    <td width="100%" valign="bottom" align="center" height="20"> <table border="0" cellpadding="0" cellspacing="0">
        <tr height="20">
          <td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/about.html">About
            the Program</a></td>
          <td height="20"><img src="images/spacer_dot.gif" width="19" height="12" border="0"></td>
          <td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/data.html">Sites
            Database</a></td>
          <td height="20"><img src="images/spacer_dot.gif" width="19" height="12" border="0"></td>
          <td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/grants.html">Mini-Grants</a></td>
          <td height="20"><img src="images/spacer_dot.gif" width="19" height="12" border="0"></td>
          <td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/exhibit.html">Exhibit</a></td>
          <td height="20"><img src="images/spacer_dot.gif" width="19" height="12" border="0"></td>
          <td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/trails.html">Heritage
            Trails</a></td>
        </tr>
      </table></td>
  </tr>
  <tr height="5">
    <td colspan="2" height="5" background="images/chrome_footer.gif"><img src="images/chrome_footer.gif" width="1" height="5" border="0"></td>
  </tr>
  <tr>
    <td colspan="2" bgcolor="#663300"> <table border="0" cellpadding="6" cellspacing="0" width="100%">
        <tr>
          <td width="50%"><span class="small_light">African American Heritage
            in Virginia<br>
            Virginia Foundation for the Humanities<br>
            145 Ednam Drive, Charlottesville, VA 22903-4629</span></td>
          <td align="right"><a href="mailto:aahv@virginia.edu"><span class="small_light">aahv@virginia.edu</span></a><span class="small_light"><br>
            804.924.3296&nbsp;&nbsp;&#149;&nbsp;&nbsp;fax 804.296.4714<br>
            Last modified August 2, 2001&nbsp;&nbsp;&#149;&nbsp;&nbsp;</span><a href="mailto:gwb3m@virginia.edu"><span class="small_light">webmaster</span></a></td>
        </tr>
      </table></td>
  </tr>
</table>


</body>

</html>

Second, the class file:

<%
'#################### START CLASS DEFINITION ########################
Class DBSearchEngine
      'private variables
      Private hTmpCount, bSearchTitles, bSearchDescriptions

      'public variables become properties of the class
      Public DBConnectionString
      Public DBConnectionAccount
      Public DBConnectionAcctPassword
      Public bNoFieldsToSearch
      Public SearchType

      Public Property Get Version
            Version = "2.0"
      End Property


      'public methods
      Public Sub SearchTitles
            bSearchTitles = true
      End Sub

      Public Sub SearchDescriptions
            bSearchDescriptions = true
      End Sub

      Public Function Search(ByVal sKeyword)
            ' ado constant declaration. do not modify next line.
            Const adOpenStatic = 3, adLockReadOnly = 1, adCmdText = &H0001
            Dim strSQL, objConn, objRs, strTemp

            strTemp = ""

            'this is a nested call to 2 functions... if you start in the
            'middle, first the MakeKeywordArray function is called
            'and then the sqlString function is called using the returned
            'values from MakeKeywordArray...
            strSQL = sqlString( MakeKeywordArray( sKeyword ) )

            'open db
            Set objConn = Server.CreateObject("ADODB.Connection")
            objConn.Open DBConnectionString
            Set objRs = Server.CreateObject("ADODB.Recordset")
            objRs_numRows=0
            objRs.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText

            'retrieve recordcount
            hTmpCount = objRs.RecordCount
            if objRs.BOF then
                  strTemp = "<h3>No Matching Records</h3>"
            else
                  'start at first record...
                  objRs.MoveFirst

                  'loop through all records
                  Do While NOT objRs.BOF AND NOT objRs.EOF
                         ' call the function HTMLdisplay to properly
                         ' format a valid entry for the results

                        'build return string, with nicely
                        'formatted html (not)
                        'build return string, with nicely
                        'formatted html (not)
                        strTemp = strTemp & HTMLdisplay(objRs)
                        
                        objRs.MoveNext
                  Loop
            end if

            'close em up...
            objRs.Close
            objConn.Close
            Set objRs = Nothing
            Set objConn = Nothing

            'return the built string of results...
            Search = strTemp
      End Function

      Public Function Count
            Count = hTmpCount
      End Function



      'private class routines
      Private Sub Class_Initialize()
            'class_initialize event is best used to set
            'up default values for class scoped variables
            bSearchTitles = false
            bSearchDescriptions = false
      End Sub

      Private Function EscapeApostrophe(ByVal toEscape)
            'make "my dog's fleas" become "my dog''s fleas",
            'escaping the apostrophe character because
            'it's meaning is special in TSQL...
            EscapeApostrophe = replace(toEscape, "'", "''")
      End Function

      Private Function sqlString(ByVal keywords)
             ' this function creates an sql string based on various criteria
            Dim i

            'the base string
            sqlString = "SELECT * FROM Sites_Orgs "

            'check for any keywords entered...
            if IsArray(keywords) then
                  if NOT bSearchTitles AND NOT bSearchDescriptions then

                        ' repair the sql string if a client forgets to
                        ' select a search field
                        sqlString = TRIM(sqlString) & ";"
                        bNoFieldsToSearch = True

                        'leave
                        Exit Function
                  end if

                   ' don't forget the where if we're still here
                  sqlString = sqlString & "WHERE "

                   ' make the criteria part of the statement based on the
                   ' array of keywords and the chosen search fields
                  for i = 0 to UBOUND(keywords)
                        if bSearchTitles then sqlString = sqlString & _
                              "OrganizationName LIKE '%" &EscapeApostrophe(keywords(i)) & _
                              "%' " &SearchType & " "
                  next
                  if bSearchTitles AND bSearchDescriptions then

                        'need to clean the sql string and remove
                        'the last AND or OR to avoid an error...
                        sqlString = Left(sqlString, Len(sqlString)-4)
                        sqlString = TRIM(sqlString)
                        sqlString = sqlString & " OR ("
                  end if
                  for i = 0 to UBOUND(keywords)
                        if bSearchDescriptions then sqlString = sqlString & _
                              "HistoricalSignificance LIKE '%" &EscapeApostrophe(keywords(i)) & _
                               "%' " &SearchType & " "
                  next
                  sqlString = Left(sqlString, Len(sqlString)-4)

                  if SearchType = "OR" then sqlString = sqlString & " "
                  if bSearchTitles AND bSearchDescriptions then sqlString = sqlString & ") "
            end if

             ' let's make the order by : most recent examples on top
            sqlString = sqlString & "ORDER BY OrganizationName DESC;"
                  End Function
                  
                  Private Function HTMLdisplay(ByRef objRs)
            'the byreference argument above, passes a good-to-go record object
            'so in here is where you would display field values from the db.
            'for example: objRs.Fields(0).value

             ' creates the interface that displays linked results
             ' for examples that fit criteria

            'i'm not big on html display, instead my focus is strictly
            'functionality so the html leaves much to be desired but
            'you can spruse it up....

            HTMLdisplay = "<P>"
            if objRs("IsNew") then
                  HTMLdisplay = HTMLdisplay & "NEW&nbsp;"
            elseif objRs("IsUpdated") then
                  HTMLdisplay = HTMLdisplay & "UPDATED&nbsp;"
            end if
            if LCase(objRs("ExampleLanguage")) = "vbscript" then
                  HTMLdisplay = HTMLdisplay & "<A HREF=""/aspEmporium/examples/"
            elseif LCase(objRs("ExampleLanguage")) = "jscript" then
                  HTMLdisplay = HTMLdisplay & "<A HREF=""/aspEmporium/examples/jscript/"
            elseif LCase(objRs("ExampleLanguage")) = "perlscript" then
                  HTMLdisplay = HTMLdisplay & "<A HREF=""/aspEmporium/examples/perlscript/"
            end if
            HTMLdisplay = HTMLdisplay & objRs("MailingListID") & ".asp""><FONT COLOR=""#60786B""><BIG><B>"
            HTMLdisplay = HTMLdisplay & objRs("HistoricalSignificance") & "</B></BIG></FONT></A>"
            HTMLdisplay = HTMLdisplay & "<BR>" & objRs("HistoricalSignificance") & "</P>"
      End Function

                  
Private Function MakeKeywordArray(ByVal sWordVarTmp)
            'makes an array of any entered keywords
            Dim sDelim, x

            sDelim = ","
            if TRIM(sWordVarTmp) = "" then Exit Function
            if not instr(sWordVarTmp, sDelim) then sDelim = " "
            x = Split(sWordVarTmp, sDelim)
            MakeKeywordArray = x
      End Function
      End Class
'#################### END CLASS DEFINITION ########################
%>
0
Comment
Question by:htillberg
  • 4
  • 2
  • 2
10 Comments
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11740085
Heres some code I use daily for database searches:

<TABLE id="Table1" cellSpacing="0" cellPadding="5" width="550" border="0">
                                          <TR>
                                                <TD class="PageHeader" style="HEIGHT: 24px" colSpan="2">Search Products</TD>
                                          </TR>
                                          <TR>
                                                <TD width="192">Query</TD>
                                                <TD width="338"><asp:textbox id="SearchQuery" runat="server" CssClass="ThinInput" Columns="15" MaxLength="50"></asp:textbox>&nbsp;&nbsp;
                                                      <asp:requiredfieldvalidator id="QueryValidator" runat="server" Enabled="true" EnableClientScript="False" ControlToValidate="SearchQuery"
                                                            ErrorMessage="A query is required."></asp:requiredfieldvalidator></TD>
                                          </TR>
                                          <TR>
                                                <TD width="192">Match</TD>
                                                <TD width="338"><asp:dropdownlist id="FieldsToMatch" runat="server">
                                                            <asp:ListItem Value="ProductName" Selected="True">Product Name</asp:ListItem>
                                                            <asp:ListItem Value="Description">Description</asp:ListItem>
                                                            <asp:ListItem Value="Either">Either</asp:ListItem>
                                                      </asp:dropdownlist></TD>
                                          </TR>
                                          <TR>
                                                <TD align="right" colSpan="2"><INPUT id="FullQuery" type="hidden" name="FullQuery" runat="server"><asp:button id="SearchProducts" runat="server" CssClass="ThinButton" Width="64px" CausesValidation="False"
                                                            Text="Search"></asp:button></TD>
                                          </TR>
                                    </TABLE>
                                    <BR>
                                    <asp:datagrid id="Products" runat="server" Width="550px" BorderColor="DarkGray" AutoGenerateColumns="False"
                                          CellPadding="5" GridLines="Horizontal" AllowPaging="True">
                                          <AlternatingItemStyle BackColor="White"></AlternatingItemStyle>
                                          <ItemStyle BackColor="WhiteSmoke"></ItemStyle>
                                          <HeaderStyle Font-Bold="True"></HeaderStyle>
                                          <Columns>
                                                <asp:TemplateColumn HeaderText="Product Name">
                                                      <HeaderStyle Width="440px"></HeaderStyle>
                                                      <ItemTemplate>
                                                            <asp:Label id="ProductInfo" runat="server"></asp:Label>
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
                                                <asp:TemplateColumn>
                                                      <ItemStyle HorizontalAlign="Right"></ItemStyle>
                                                      <ItemTemplate>
                                                            <asp:LinkButton id="EditItem" runat="server" CommandName="Edit">Edit</asp:LinkButton>&nbsp;&nbsp;&nbsp;
                                                            <asp:LinkButton id="DeleteItem" runat="server" CommandName="Delete">Delete</asp:LinkButton><INPUT id="ItemImageSrc" type="hidden" size="1" runat="server">&nbsp;
                                                      </ItemTemplate>
                                                </asp:TemplateColumn>
                                          </Columns>
                                          <PagerStyle PageButtonCount="20" Mode="NumericPages"></PagerStyle>
                                    </asp:datagrid></TD>
                        </TR>
                  </TABLE>

 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Query As String

        If Not IsPostBack Then LoadProductData()
    End Sub

Region " Products DataGrid "
    Private Sub Products_ItemDataBound(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles Products.ItemCreated
        Dim DeleteItem As LinkButton
        Dim ProductName As Label
        Dim ImageSrc As HtmlInputHidden
        If e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then

            DeleteItem = DirectCast(e.Item.FindControl("DeleteItem"), LinkButton)
            DeleteItem.Attributes.Add("onclick", "return confirm('Are you sure you want to delete this " & _
                "product?');")

            ProductName = DirectCast(e.Item.FindControl("ProductInfo"), Label)
            ProductName.Text = CStr(DataBinder.Eval(e.Item.DataItem, "ProductName"))
            ProductName.Text &= "<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&#187; " & _
                CStr(DataBinder.Eval(e.Item.DataItem, "Tier1Name"))
            If Not DataBinder.Eval(e.Item.DataItem, "Tier2Name") Is DBNull.Value Then
                ProductName.Text &= "&nbsp;&#187;&nbsp;" & CStr(DataBinder.Eval(e.Item.DataItem, "Tier2Name"))
            End If
            If Not DataBinder.Eval(e.Item.DataItem, "Tier3Name") Is DBNull.Value Then
                ProductName.Text &= "&nbsp;&#187;&nbsp;" & CStr(DataBinder.Eval(e.Item.DataItem, "Tier3Name"))
            End If

            ImageSrc = DirectCast(e.Item.FindControl("ItemImageSrc"), HtmlInputHidden)
            ImageSrc.Value = CStr(DataBinder.Eval(e.Item.DataItem, "ImageSrc"))
        End If
    End Sub
    Private Sub Products_ItemCommand(ByVal source As System.Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles Products.ItemCommand
        If IsRefresh Then Return

        If e.CommandName = "Delete" Then
            Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
            Dim cmd As New OleDb.OleDbCommand
            Dim ImageSrc As String = DirectCast(e.Item.FindControl("ItemImageSrc"), HtmlInputHidden).Value
            Dim ProductImage As IO.FileInfo

            cmd.CommandType = CommandType.Text
            cmd.CommandText = "DELETE FROM Products WHERE ProductID = " & _
                CStr(Products.DataKeys(e.Item.ItemIndex))
            cmd.Connection = cnn

            cnn.Open()
            cmd.ExecuteNonQuery()
            cnn.Close()

            cmd.Dispose()
            cnn.Dispose()

            Try
                ProductImage = New IO.FileInfo(Server.MapPath("~/Pictures/Products/" & ImageSrc))
                If ProductImage.Exists Then ProductImage.Delete()
                ProductImage = New IO.FileInfo(Server.MapPath("~/Pictures/Products/Thumbnails/" & ImageSrc))
                If ProductImage.Exists Then ProductImage.Delete()
            Catch ex As Exception

            End Try

            LoadProductData()
        ElseIf e.CommandName = "Edit" Then
            Response.Redirect("EditProduct.aspx?pid=" & CStr(Products.DataKeys(e.Item.ItemIndex)))
        End If
    End Sub
    Private Sub Products_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles Products.PageIndexChanged
        Products.CurrentPageIndex = e.NewPageIndex
        LoadProductData()
    End Sub
    Private Sub LoadProductData(Optional ByVal Query As String = "")
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand
        Dim da As New OleDb.OleDbDataAdapter(cmd)
        Dim dt As New DataTable

        'If the query is not passed, check to see if a previous query has been executed and stored
        If Query = "" Then Query = FullQuery.Value

        'If it's still empty, use the default query
        If Query = "" Then
            Query = "SELECT Tier1.Name AS Tier1Name, Tier2.Name AS Tier2Name, Tier3.Name AS Tier3Name, " & _
                "Products.Name AS ProductName, Products.ProductID, Products.ImageSrc FROM Tier3 RIGHT " & _
                "JOIN (Tier2 RIGHT JOIN (Tier1 INNER JOIN Products ON Tier1.Tier1ID = Products.Tier1ID) " & _
                "ON Tier2.Tier2ID = Products.Tier2ID) ON Tier3.Tier3ID = Products.Tier3ID ORDER BY " & _
                "Products.Name"
        End If

        cmd.CommandType = CommandType.Text
        cmd.CommandText = Query
        cmd.Connection = cnn

        da.Fill(dt)

        Products.DataKeyField = "ProductID"
        Products.DataSource = dt
        Products.DataBind()

        cmd.Dispose()
        cnn.Dispose()

        If Products.Items.Count = 0 Then
            Products.Visible = False
        Else
            Products.Visible = True
        End If
    End Sub
#End Region

#Region " SearchProducts Button "
    Private Sub SearchProducts_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchProducts.Click

        QueryValidator.Enabled = True
        Tier1Validator.Enabled = False
        ProductNameValidator.Enabled = False
        Validate()

        If Not IsValid Then Return

        Dim Query As String

        Query = "SELECT Tier1.Name, Tier2.Name, Tier3.Name, Products.Name, Products.ProductID " & _
            "FROM Tier3 RIGHT JOIN (Tier2 RIGHT JOIN (Tier1 INNER JOIN Products ON Tier1.Tier1ID = " & _
            "Products.Tier1ID) ON Tier2.Tier2ID = Products.Tier2ID) ON Tier3.Tier3ID = " & _
            "Products.Tier3ID WHERE "

        Select Case FieldsToMatch.SelectedValue
            Case "ProductName"
                Query &= "ProductName LIKE '%" & SearchQuery.Text & "%'"
            Case "Description"
                Query &= "Products.Description LIKE '%" & SearchQuery.Text & "%'"
            Case "Either"
                Query &= "Products.Description LIKE '%" & SearchQuery.Text & "%' OR ProductName LIKE '%" & _
                    SearchQuery.Text & "%'"
        End Select
        Query &= " ORDER BY Products.Name"
        LoadProductData(Query)
        FullQuery.Value = Query
    End Sub
#End Region

If you have any questions, let me know.

Regards,

Aeros
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11740642
Hi

What columns are you attempting to search? You seem to have an awful lot of code for what is usually a simple operation! I've done several search pages and they usually fall into one of two categories:

(1) You need to search on several columns of different types. In this case I build a WHERE string by having a control for each column and checking each condition.

(2) You are just looking for a string in any of several columns. Here I just concatenate the columns and look for the string, like:

    SELECT ColA + ColB + C AS Cols FROM TableName WHERE Cols LIKE '%String%'

With this approach you don't need special classes and the code is fairly short. If this is what you are looking for, ask and I'll post some sample code.
0
 

Author Comment

by:htillberg
ID: 11755250
crescendo:

if you have some simple examples of code just looking for a string of text (and just searching for the same "type" of data") --- how does one concatenate the columns?

Just using the above select statement modified to my database,  I am getting the following error:

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

Any advice would be greatly appreciated.

Thanks!
0
 

Author Comment

by:htillberg
ID: 11755489
actually, it does have to search for strings of text between two different type of data...
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Accepted Solution

by:
crescendo earned 250 total points
ID: 11756125
I didn't realise you were using Access. Access is a bit of a pain in that it uses a slightly different syntax in the Access query builder than that used when passing commands via OleDb from ASP.NET.

Access doesn't seem to support the syntax I gave you, so what I had to do was to build a query to concatenate the columns:

SELECT Table1.*, [Cola] & [Colb] & [Colc] AS Cols
FROM Table1;

and then do a select from this:

SELECT *
FROM Query2
WHERE (((Query2.Cols) Like '*your search string*'));

It's hardly any less efficient than doing it in one step. Note that when you code the second query in ASP.NET you will have to use '%' rather than '*'.

What do you mean about two different types of data?

0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11756144
he means numeric vs text
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11756148
and their respective db field type
0
 
LVL 17

Assisted Solution

by:AerosSaga
AerosSaga earned 250 total points
ID: 11756168
you could always use the isnumeric function to build your sql string dynamically based on the type of input:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp

If it is numeric strip out the like and % and just query for the number else go about it like my first lengthy  code post.

Regards,

Aeros
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now