Multiple selection form
Question posted by: gblack301
(Newbie)
on
September 22nd, 2008 10:35 PM
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
-
%>
4
Answers Posted
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
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
It looks like you're not putting the WHERE clause on the front of the conditions - check your logic there.
Dr B
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
|
|
|
What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 197,024 network members.
|
