469,610 Members | 1,649 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

Multiple selection form

Hi,
I have a search form where the user can check a box or enter some data such as a name to quey the database. I was wondering what is the best way to enable the ability for a user data in more than one field or check muliple boxes to query the database. What I want to do is create a multiple search criteria data string. Below is the code that working with a the moment. Any input would be appreciate. Thanks

Greg

Expand|Select|Wrap|Line Numbers
  1.  
  2. If isempty(Request.Form("submit")) then
  3. else
  4.  
  5.  Dim strSearchType, strFName, strWhere, strOperator, strChap, strCity, strState,  strAir, strHVAC, strHydro, strPlmb, strFire, strSV, strCPT, i
  6.  
  7.  
  8.  strSearchType = request.form("Search")
  9.  strOperator = " " & request.form("Operator") & " " 
  10.  
  11.         i = 0
  12.  
  13. If Trim(request.Form("txtFirm")) <> "" then  
  14.             strFName = request.Form("txtFirm")
  15.             strFName = BadChar(strFName)
  16.             strFName = rmvWhite(strFName)    
  17.  
  18.             strFName = buildString(strFName, " Firms.FirmName ALIKE ")
  19.  
  20.             i = i + 1
  21.         End If
  22.  
  23.  
  24. If Trim(request.Form("txtCity")) <> "" Then
  25.             strCity = request.Form("txtCity")
  26.             strCity = BadChar(strCity)
  27.             strCity = rmvWhite(strCity)
  28.             If i = 0 then
  29.                 strCity = " " & buildString( strCity, " Firms.City ALIKE ")
  30.             Else
  31.                 strCity = strOperator & buildString( strCity, " Firms.City ALIKE ")
  32.             End If
  33.             i=i+1
  34.         End If    
  35.  
  36. If request.form("txtState") <> "0" then
  37.             If i = 0 then    
  38.                 strState = " Firms.State = '" & request.form("txtState") & "' "
  39.             Else
  40.                 strState = strOperator & " Firms.State = '" & request.form("txtState") & "' "
  41.             End If
  42.             i=i+1
  43.         End If
  44.  
  45. If request.form("chkA") = "True" Then
  46.             If i = 0 then
  47.             strAir = " " & strSearchType & ".Air = True "
  48.             Else
  49.                 strAir = strOperator & " " & strSearchType & ".Air = True "
  50.             End If
  51.             i=i+1
  52.         End If
  53.  
  54. If request.form("chkHydro") = "True" Then
  55.             If i=0 then
  56.         strHydro = " " & strSearchType & ".Hydronics = True "
  57.             Else
  58.         strHydro = strOperator & " " & strSearchType & ".Hydronics = True "
  59.             End If
  60.             i=i+1
  61.         End If
  62.  
  63. If request.form("chkHVAC") = "True" Then
  64.             If i=0 Then     
  65.                 strHVAC = " " & strSearchType & ".[BSC HVAC] = True "
  66.             Else
  67.                 strHVAC = strOperator & " " & strSearchType & ".[BSC HVAC] = True "
  68.             End If
  69.             i=i+1
  70.         End If
  71.  
  72. If request.form("chkPlumb") = "True" Then
  73.         If i=0 then
  74.         strPlmb = " " & strSearchType & ".[BSC Plumbing] = True "
  75.         Else
  76.     strPlmb = strOperator & " " & strSearchType & ".[BSC Plumbing] = True "
  77.         End If
  78.         i=i+1
  79.         End If
  80.  
  81.  
  82.  
  83. If strSearchType = "Supervisors" Then
  84.  
  85.         Dim strSFName, strSLName
  86.  
  87. If Trim(request.Form("txtSFName")) <> "" Then
  88.     strSFName = request.Form("txtSFName")
  89.     strSFName = BadChar(strSFName)
  90.     strSFName = rmvWhite(strSFName)
  91.         If i = 0 then
  92.  
  93. strSFName = " " & buildString( strSFName, " Supervisors.FirstName ALIKE ")
  94.             Else
  95. strSFName = strOperator & buildString( strSFName, " Supervisors.FirstName ALIKE ")
  96.             End If
  97.             i=i+1
  98.         End If    
  99.  
  100. If Trim(request.Form("txtSLName")) <> "" Then
  101.     strSLName = request.Form("txtSLName")
  102.     strSLName = BadChar(strSLName)
  103.     strSLName = rmvWhite(strSLName)
  104.     If i = 0 then
  105.  
  106. strSLName = " " & buildString( strSLName, " Supervisors.LastName ALIKE ")
  107.         Else
  108.         strSLName = strOperator & buildString( strSLName, " Supervisors.LastName ALIKE ")
  109.             End If
  110.             i=i+1
  111.         End If    
  112. End If
  113.  
  114.  
  115.  
  116.     If strSearchType = "Firms" Then
  117.  
  118. strWhere = strFName & strChap & strCity & strState &  strAir & strHVAC & strHydro & strPlmb & strFire & strSV & strCPT
  119. strWhere = rmvWhite(strWhere)
  120. If strWhere <> "" Then
  121.     strWhere = " AND (" & strWhere & ")"
  122.         End If
  123.         Session("Where") = strWhere
  124.         response.redirect "Firms.asp"
  125.  
  126.     Else
  127.  
  128. strWhere = strFName & strChap & strCity & strState & strAir & strHVAC & strHydro & strPlmb & strFire & strSV & strCPT & strSFName & strSLName
  129.  
  130. strWhere = rmvWhite(strWhere)
  131.     If strWhere <> "" Then
  132.     strWhere = " AND (" & strWhere & ")"
  133.     End If
  134.     Session("Where") = strWhere        
  135.     response.redirect "Supervisors.asp"
  136.  
  137.  
  138.     End If 
  139.  
  140. End If
  141.  
  142. %>
  143.  
  144. <%
  145.  
  146. Function BadChar(bstring) 'Remove potentially dangerous charcters to prevent sql injection attack
  147.  
  148.     bstring = replace(bstring,"/","*")
  149.     bstring = replace(bstring,"!","*")
  150.     bstring = replace(bstring,"'","*")
  151.     bstring = replace(bstring,"%","*")
  152.     bstring = replace(bstring,"&","*")
  153.     bstring = replace(bstring,"=","*")
  154.     BadChar = bstring
  155. End Function
  156.  
  157. Function rmvWhite(nstring) 'Remove whitespace from string
  158.  
  159.  Dim regEx
  160.  Set regEx = New RegExp
  161.  regEx.Global = true
  162.  regEx.IgnoreCase = True
  163.  
  164.  regEx.Pattern = "\s{2,}"
  165.  
  166.  rmvWhite = Trim(regEx.Replace(nstring, " "))
  167.  
  168. End Function    
  169.  
  170. Function buildString(fieldString, sqlString) 'Format string for SQL Where Clause
  171.  
  172. fieldString = "'%" & replace(fieldString, " ", "%' '%") & "%'"
  173.  
  174. buildString = "(" & sqlString & replace(fieldString, " ", strOperator & sqlString) & ")"
  175.  
  176.  
  177. End Function    
  178. %>
  179.  
Sep 22 '08 #1
4 1685
DrBunchman
979 Expert 512MB
Hi Greg,

It looks to me like your code is already doing that. You have a series of requests to retrieve the form variables that have been passed to the page and each time they add another condition to the search string.

Perhaps I have misunderstood your requirements or your code?

Dr B
Sep 23 '08 #2
Hi Dr B,
thanks for responding but I'm getting the following error when selecting two options. Any help would be appreciated it. Thanks

Database Results Error
Description: Syntax error (missing operator) in query expression '(Firms.FirmCertificationNumber = Supervisors.FirmCertificationNumber) AND (Firms.Chapter = Chapters.ID)AND(Firms.FirmStatus = 'CERTIFIED') AND (Firms.Air = True Firms.Hydronics = True)'.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine
Sep 23 '08 #3
DrBunchman
979 Expert 512MB
It looks like you're not putting the WHERE clause on the front of the conditions - check your logic there.

Dr B
Sep 23 '08 #4
Thanks, I will try to incorporate that into my where clause. I am just a little stuck on the part. Do you know of references that can get me on the right track? thanks

Greg
Sep 23 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Peter | last post: by
2 posts views Thread by areef.islam | last post: by
1 post views Thread by jjuan | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.