Bullschmidt.com


     Home
     Web Database Concept
     How We Work Together
     Bio
     Web Database Sample
     Classic ASP Design Tips
     Bar Chart Tool
     Web Design Resources
     Access Database Sample

Classic ASP Design Tips
Search For Keywords on Multiple Fields
These are opinions of J. Paul Schmidt, MBA.
No warranties are either expressed or implied.
To Use Paul's Services: Paul@Bullschmidt.com

o Example of searching for keywords on multiple fields.

For example with Keywords of Go Back Now one gets the SQL Where clause of ((Title Like '%Go Back Now%') OR (Category Like '%Go Back Now%') OR (Descr Like '%Go Back Now%')):

'Keywords = Request.Form("Keywords")
Keywords = "Go Back Now"

' Set var.
KeywordsFixedForSQL = Keywords

' Remove any ".
KeywordsFixedForSQL = Replace(KeywordsFixedForSQL, Chr(34), "")

' Double up any single quotes.
KeywordsFixedForSQL = Replace(KeywordsFixedForSQL, "'", "''")

If KeywordsFixedForSQL <> "" Then
  strSQLWhere = strSQLWhere & "("
    strSQLWhere = strSQLWhere & "(Title Like " & Chr(39) & "%" & KeywordsFixedForSQL & "%" & Chr(39) & ")"
    strSQLWhere = strSQLWhere & " OR (Category Like " & Chr(39) & "%" & KeywordsFixedForSQL & "%" & Chr(39) & ")"
    strSQLWhere = strSQLWhere & " OR (Descr Like " & Chr(39) & "%" & KeywordsFixedForSQL & "%" & Chr(39) & ")"
  strSQLWhere = strSQLWhere & ")"
End If

Response.Write "strSQLWhere: " & strSQLWhere

o Example of searching for keywords separately for each word on multiple fields.

For example with Keywords of Go Back Now one gets the SQL Where clause of ((Title Like '%Go%') OR (Category Like '%Go%') OR (Descr Like '%Go%')) OR ((Title Like '%Back%') OR (Category Like '%Back%') OR (Descr Like '%Back%')) OR ((Title Like '%Now%') OR (Category Like '%Now%') OR (Descr Like '%Now%')):

'Keywords = Request.Form("Keywords")
Keywords = "Go Back Now"

' Set var.
KeywordsFixedForSQL = Keywords

' Remove any ".
KeywordsFixedForSQL = Replace(KeywordsFixedForSQL, Chr(34), "")

' Double up any single quotes.
KeywordsFixedForSQL = Replace(KeywordsFixedForSQL, "'", "''")

If KeywordsFixedForSQL <> "" Then
  ' Set var (array of words).
  strarrayKeywordsFixedForSQL = Split(KeywordsFixedForSQL, " ")

  ' Loop thru items (words).
  For intItemNum = 0 To UBound(strarrayKeywordsFixedForSQL)
    strSQLWhere = strSQLWhere & " OR ("
      strSQLWhere = strSQLWhere & "(Title Like " & Chr(39) & "%" & strarrayKeywordsFixedForSQL(intItemNum) & "%" & Chr(39) & ")"
      strSQLWhere = strSQLWhere & " OR (Category Like " & Chr(39) & "%" & strarrayKeywordsFixedForSQL(intItemNum) & "%" & Chr(39) & ")"
      strSQLWhere = strSQLWhere & " OR (Descr Like " & Chr(39) & "%" & strarrayKeywordsFixedForSQL(intItemNum) & "%" & Chr(39) & ")"
    strSQLWhere = strSQLWhere & ")"
  Next ' Next intItemNum.

  ' Remove initial " OR ".
  strSQLWhere = Right(strSQLWhere, Len(strSQLWhere) - 4)
End If

Response.Write "strSQLWhere: " & strSQLWhere





To Use Paul's Services:
Paul@Bullschmidt.com

Let's put some data on the Web!
Copyright © 2000-2018
J. Paul Schmidt, MBA
Freelance Web and Database Developer
All Rights Reserved

Privacy Policy