473,836 Members | 1,530 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Multiple selection form

17 New Member
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 1951
DrBunchman
979 Recognized Expert Contributor
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
gblack301
17 New Member
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.FirmCer tificationNumbe r = Supervisors.Fir mCertificationN umber) AND (Firms.Chapter = Chapters.ID)AND (Firms.FirmStat us = 'CERTIFIED') AND (Firms.Air = True Firms.Hydronics = True)'.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine
Sep 23 '08 #3
DrBunchman
979 Recognized Expert Contributor
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
gblack301
17 New Member
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

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

Similar topics

3
5214
by: svdh2 | last post by:
I have been looking at a problem the entire week in access, I have still not been able to find a solution. Hope that you could maybe tell where to look Concerns the link between Access and Word. I can not transfer a report to word without losing out on the lay-out (RTF format). I understand that there is no way out ok, mail merge I thought. But here I have the problem that I need to merge multiple tables and that I can just include...
0
3520
by: starace | last post by:
I have designed a form that has 5 different list boxes where the selections within each are used as criteria in building a dynamic query. Some boxes are set for multiple selections but these list boxes do not necessarily need to have a selection made to be used in the dynamic query. In essence the form can have selections made in all or none of its list boxes to form the dynamic query I am looking to get some feedback in reference to...
2
2278
by: Peter | last post by:
Hello! Please, could anyone tell, is it possible to set multiple items to be selected in list control in the code? For example when the web form is loaded three items of 5 are selected in list control already? Now I manage to set only one item to be selected during page load, but there is need to multiple items could be selected for the user. I appreciate very much your help! Thanks.
2
3668
by: areef.islam | last post by:
Hi, I am kinda new to javascript and I am having this problem with selecting multiple options from a select tag. Hope someone can help me out here. here is my code /////////////////////////////////////////////////////////////////////////////////////// <form action="whatever.php" method="post"> <select name="zip_code" onchange="makeRequest('getCity.php?state='+this.form.zip_code.options.value)" multiple="multiple" size="20">
1
3057
by: Shizbart | last post by:
MS Access 97 Beginner/Moderate Level User I am trying to create a Database to track Workouts in MS Access 97. I have one Table named Workouts that contains the following Fields: Workout Code (e.g: U100), WorkoutActivity (e.g.: Bench Press: Flat Bench Straightbar) (note: this is the Primary Key of this Table), and Target Area (e.g.: Upper Body / Chest). A second Table named Workout_Log contains the following Fields: Date,...
1
2116
by: jjuan | last post by:
I have a multiple dropdown which have multiple selection. If I select multiple value on my multiple dropdown list and submit it,how can i write it on the tblotherlang sample code <select size="10" name="ddmultiplelang" multiple> <%
1
11510
by: kirkus84 | last post by:
I am currently trying to do a multiple record mail merge through a query via a command button on a form. The query basically displays customers who have said yes to privacy. The user inputs a date into txtDate on frmDate and the clicks "View Queried Privacy Records" Once they click this it opens the query form frmPrivacy which then has a command button cmdMailmerge to perform the mail merge and print the records that result from that query. (I...
2
1821
by: permander kumar | last post by:
hi plz help me, i have a code in which two multiple selection dropdown list. in first list if I am select single value the data are fetch in second table on button click. but the problem is in first dropdown list the second field is also select after the page submit. the code is <?php include("connect.php"); ?> <html><body>
92
4790
by: bonneylake | last post by:
Hey Everyone, Well i was hoping someone could explain the best way i could go about this. i have a few ideas on how i could go about this but i am just not sure if it would work. Right now i have a form where you can add and delete multiple serial information. This works wonderful. But now for every serial information i add i need to be able to add and remove multiple parts to that serial. heres an example of what i mean serial...
58
8156
by: bonneylake | last post by:
Hey Everyone, Well recently i been inserting multiple fields for a section in my form called "serial". Well now i am trying to insert multiple fields for the not only the serial section but also the parts section an i seem to be having trouble. When i try to insert into the parts section i get the error Invalid character value for cast specification. But not sure what i am doing wrong. Here is what i am using to insert. All the sections...
0
10860
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10560
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10604
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9389
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6984
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5659
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5831
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4466
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.