Multiple selection form

[gblack301]

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

If isempty(Request.Form("submit")) then
else

 Dim strSearchType, strFName, strWhere, strOperator, strChap, strCity, strState,  strAir, strHVAC, strHydro, strPlmb, strFire, strSV, strCPT, i
 
 
 strSearchType = request.form("Search")
 strOperator = " " & request.form("Operator") & " " 
 
		i = 0

If Trim(request.Form("txtFirm")) <> "" then  
			strFName = request.Form("txtFirm")
			strFName = BadChar(strFName)
			strFName = rmvWhite(strFName)	
			
			strFName = buildString(strFName, " Firms.FirmName ALIKE ")
		
			i = i + 1
		End If
	

If Trim(request.Form("txtCity")) <> "" Then
			strCity = request.Form("txtCity")
			strCity = BadChar(strCity)
			strCity = rmvWhite(strCity)
			If i = 0 then
				strCity = " " & buildString( strCity, " Firms.City ALIKE ")
			Else
				strCity = strOperator & buildString( strCity, " Firms.City ALIKE ")
			End If
			i=i+1
		End If	

If request.form("txtState") <> "0" then
			If i = 0 then	
				strState = " Firms.State = '" & request.form("txtState") & "' "
			Else
				strState = strOperator & " Firms.State = '" & request.form("txtState") & "' "
			End If
			i=i+1
		End If

If request.form("chkA") = "True" Then
			If i = 0 then
			strAir = " " & strSearchType & ".Air = True "
			Else
				strAir = strOperator & " " & strSearchType & ".Air = True "
			End If
			i=i+1
		End If

If request.form("chkHydro") = "True" Then
			If i=0 then
		strHydro = " " & strSearchType & ".Hydronics = True "
			Else
		strHydro = strOperator & " " & strSearchType & ".Hydronics = True "
			End If
			i=i+1
		End If

If request.form("chkHVAC") = "True" Then
			If i=0 Then 	
				strHVAC = " " & strSearchType & ".[BSC HVAC] = True "
			Else
				strHVAC = strOperator & " " & strSearchType & ".[BSC HVAC] = True "
			End If
			i=i+1
		End If

If request.form("chkPlumb") = "True" Then
		If i=0 then
		strPlmb = " " & strSearchType & ".[BSC Plumbing] = True "
		Else
	strPlmb = strOperator & " " & strSearchType & ".[BSC Plumbing] = True "
		End If
		i=i+1
		End If

	
		
If strSearchType = "Supervisors" Then
	
		Dim strSFName, strSLName
		
If Trim(request.Form("txtSFName")) <> "" Then
	strSFName = request.Form("txtSFName")
	strSFName = BadChar(strSFName)
	strSFName = rmvWhite(strSFName)
		If i = 0 then

strSFName = " " & buildString( strSFName, " Supervisors.FirstName ALIKE ")
			Else
strSFName = strOperator & buildString( strSFName, " Supervisors.FirstName ALIKE ")
			End If
			i=i+1
		End If	

If Trim(request.Form("txtSLName")) <> "" Then
	strSLName = request.Form("txtSLName")
	strSLName = BadChar(strSLName)
	strSLName = rmvWhite(strSLName)
	If i = 0 then
	
strSLName = " " & buildString( strSLName, " Supervisors.LastName ALIKE ")
		Else
		strSLName = strOperator & buildString( strSLName, " Supervisors.LastName ALIKE ")
			End If
			i=i+1
		End If	
End If



	If strSearchType = "Firms" Then
		
strWhere = strFName & strChap & strCity & strState &  strAir & strHVAC & strHydro & strPlmb & strFire & strSV & strCPT
strWhere = rmvWhite(strWhere)
If strWhere <> "" Then
	strWhere = " AND (" & strWhere & ")"
		End If
		Session("Where") = strWhere
		response.redirect "Firms.asp"
		
	Else
	
strWhere = strFName & strChap & strCity & strState & strAir & strHVAC & strHydro & strPlmb & strFire & strSV & strCPT & strSFName & strSLName
		
strWhere = rmvWhite(strWhere)
	If strWhere <> "" Then
	strWhere = " AND (" & strWhere & ")"
	End If
	Session("Where") = strWhere		
	response.redirect "Supervisors.asp"

	
	End If 

End If

%>

<%

Function BadChar(bstring) 'Remove potentially dangerous charcters to prevent sql injection attack

	bstring = replace(bstring,"/","*")
	bstring = replace(bstring,"!","*")
	bstring = replace(bstring,"'","*")
	bstring = replace(bstring,"%","*")
	bstring = replace(bstring,"&","*")
	bstring = replace(bstring,"=","*")
	BadChar = bstring
End Function

Function rmvWhite(nstring) 'Remove whitespace from string

 Dim regEx
 Set regEx = New RegExp
 regEx.Global = true
 regEx.IgnoreCase = True
 
 regEx.Pattern = "\s{2,}"
 
 rmvWhite = Trim(regEx.Replace(nstring, " "))

End Function	

Function buildString(fieldString, sqlString) 'Format string for SQL Where Clause

fieldString = "'%" & replace(fieldString, " ", "%' '%") & "%'"
	
buildString = "(" & sqlString & replace(fieldString, " ", strOperator & sqlString) & ")"
	

End Function	
%>

[DrBunchman]

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

[gblack301]

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

[DrBunchman]

It looks like you're not putting the WHERE clause on the front of the conditions - check your logic there.

Dr B

[gblack301]

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