How to search multiple fields using ASP
A major issue for me when I first started writing in VB Script was constructing the ability to search a table using multiple field input from a form and having the sql statement dynamically built according to the input provided by the user. I have used the method described here hundreds of times it is quick and adaptive. I generally use a frames page for the search, in this way the search is maintained in the upper pane whilst results show in the lower. The search can then be adjusted as required without the need to keep going back and inputting the search from scratch. This works for Access, SQL server, or of course any database. The following code would search an address for instance :[/font]
There are four pages to this solution, they should all be placed in the same directory for the refernces to work properly :
=============== =============== ==========
Page 1 - The main frame page
=============== =============== ==========
=============== =============== ==========
Page 2 - The search form page
=============== =============== ==========
=============== =============== ==========
Page 3 - A holding page to inform the user what to do.
=============== =============== ==========
=============== =============== ==========
Page 4 - The results page.
=============== =============== ==========
Thats it really. I have adapted this from code used by me in various web sites. You may need to change references to suit yourself.
A major issue for me when I first started writing in VB Script was constructing the ability to search a table using multiple field input from a form and having the sql statement dynamically built according to the input provided by the user. I have used the method described here hundreds of times it is quick and adaptive. I generally use a frames page for the search, in this way the search is maintained in the upper pane whilst results show in the lower. The search can then be adjusted as required without the need to keep going back and inputting the search from scratch. This works for Access, SQL server, or of course any database. The following code would search an address for instance :[/font]
There are four pages to this solution, they should all be placed in the same directory for the refernces to work properly :
=============== =============== ==========
Page 1 - The main frame page
=============== =============== ==========
Code:
<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> <title>Address Search Main</title> </head> <frameset rows="112,*" framespacing="0" border="0" frameborder="0"> <frame name="addsrc_header" scrolling="no" noresize target="addsrc_main" src="02srctop.asp"> <frame name="addsrc_main" src="03srcbot.asp" scrolling="auto"> <noframes> <body> <p>This page uses frames, but your browser doesn't support them.</p> </body> </noframes> </frameset> </html>
Page 2 - The search form page
=============== =============== ==========
Code:
<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> <title>Address Search Top</title> <base target="add_main"> </head> <body> <table border="0" width="560" cellspacing="0" cellpadding="0"> <form method="POST" action="04srcres.asp" target="addsrc_main"> <tr> <td colspan="6" style="border-left-width: 1px; border-right-width: 1px; border-top-width: 1px; border-bottom: 1px solid #000000"> ADDRESS SEARCH -<b><font color="#000080"> </font> </b> <font color="#000080"> Use the fields to search for recorded addresses.</font></td> </tr> <tr> <td width="95"> <input type="text" name="InpHouseName" size='13' tabindex="1"></td> <td width="133"> <font color="#000080">House Name</font></td> <td width="94"> <INPUT type="text" name=InpAddArea size='13' maxlength="10" tabindex="4"></td> <td width="72"> <font color="#000080">Area</font></td> <td width="93"> <INPUT type="text" name=InpAddCounty size='13' maxlength="10" tabindex="7"></td> <td width="113"> <font color="#000080">County</font></td> </tr> <tr> <td width="95"> <input type="text" name="InpAddNo" size='13' tabindex="2"></td> <td width="133"> <font color="#000080">Number</font></td> <td width="94"> <INPUT type="text" name=InpAddTown size='13' tabindex="5"></td> <td width="72"> <font color="#000080">Town</font><font face="Arial" color="#000080"> </font> </td> <td width="93"> <INPUT type="text" name=InpAddPostCode size='13' tabindex="8"></td> <td width="113"> <font color="#000080">Post Code</font></td> </tr> <tr> <td width="95"> <INPUT type="text" name=InpAddStreet size='13' tabindex="3"></td> <td width="133"> <font color="#000080">Street</font></td> <td width="94"> <INPUT type="text" name=InpAddCity size='13' tabindex="6"></td> <td width="72"> <font color="#000080">City</font></td> <td colspan="2"> <input type="submit" value="Search" name="B1"> <input type="reset" value="Reset" name="B2"> </td> </tr> </form> </table> </body> </html>
Page 3 - A holding page to inform the user what to do.
=============== =============== ==========
Code:
<html> <head> <meta http-equiv="Content-Language" content="en-gb"> <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> <title>Address Search Bottom</title> </head> <body> <p>The Search Results Will Show Here When The Search Button Is Clicked. </p> </body> </html>
Page 4 - The results page.
=============== =============== ==========
Code:
<!-- #include file="../00incfiles/adovbs.inc" -->
<%
'Connection to the database (SQL Server in this case)
'=============CONNECTION CODE=====================
Set Conn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=SQLOLEDB;Data Source=MYSERVERNAME;Initial Catalog=MYDATABASE;"&_
"User Id=USERNAME;Password=PASSWORD;"
Conn.Open strConn
'=============CONNECTION CODE=====================
'Obtain input from the referring form (index.top.asp) and feed to variables Var...
'Note the use of the replace function below to prevent query error (removes any apostrophes input by the user)
VarHouseName = Replace(Request("InpHouseName"),"'","")
VarAddressNo = Replace(Request("InpAddressNo"),"'","")
VarAddressSt = Replace(Request("InpAddressStreet"),"'","")
VarAddressArea = Replace(Request("InpAddressArea"),"'","")
VarAddressTown = Replace(Request("InpAddressTown"),"'","")
VarAddressCity = Replace(Request("InpAddressCity"),"'","")
VarAddressCounty = Replace(Request("InpAddressCounty"),"'","")
VarAddressPostCode = Replace(Request("InpAddressPostCode"),"'","")
'Note the use of '=' and 'like' clauses in the statement below. Using a 'like' clause like that shown
'precludes the need for the user to enter his or her own wild card parantheses when searching
'Dynamically formulate the SQL statement and feed into a varaible called VarSQL as its built
If Len(VarHouseNumber) > 0 then
VarSQL = VarSQL & "HouseName like '%"&VarHouseNumber&"%' " 'Like clause used
end if
If Len(VarAddressNo) > 0 then
If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
VarSQL = VarSQL & "AddressNo = '"&VarAddressNo&"' " '= clause used
end if
If Len(VarAddressSt) > 0 then
If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
VarSQL = VarSQL & "AddressStreet like '%"&VarAddressSt&"%' "
end if
If Len(VarAddressArea) > 0 then
If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
VarSQL = VarSQL & "AddressArea like '%"&VarAddressArea&"%' "
end if
If Len(VarAddressTown) > 0 then
If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
VarSQL = VarSQL & "AddressTown like '%"&VarAddressTown&"%' "
end if
If Len(VarAddressCity) > 0 then
If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
VarSQL = VarSQL & "AddressCity like '%"&VarAddressCity&"%' "
end if
If Len(VarAddressCounty) > 0 then
If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
VarSQL = VarSQL & "AddressCounty like '%"&VarAddressCounty&"%' "
end if
If Len(VarAddressPostCode) > 0 then
If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
VarSQL = VarSQL & "AddressPostCode like '%"&VarAddressPostCode&"%' "
end if
'Create a recordset object
Set RS1 = Server.CreateObject("ADODB.RecordSet")
'Check main address table
If Len (VarSQL)<1 Then
sql1 = "SELECT * FROM tblAddress" 'If the user pushes the button without any input all records are displayed
Else
sql1 = "Select * from tblAddress where "&VarSQL&"" 'This statement is executed when there is input from the user
End If
'response.write sql1
'Fire the query at the database using the recordset object and sql statement
RS1.Open sql1, Conn, adOpenKeyset,adLockOptimistic
'Obtain the number of records your search will reveal and feed to a variable.
VarResCount = RS1.recordcount
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Address Search Results</title>
</head>
<body>
<%If RS1.eof Then%>
<table border="0" width="100%" id="table1" cellspacing="0" cellpadding="0">
<tr>
<td>SEARCH RESULTS</td>
</tr>
<tr>
<td><font color="#FF0000">No addresses were found matching your search, please modify your search parameters.</font></td>
</tr>
</table>
<%Else%>
<table border="0" width="100%" id="table2" cellspacing="0" cellpadding="0">
<tr>
<td>SEARCH RESULTS</td>
</tr>
<tr>
<td>The following <font color="#FF0000"><%=VarResCount%></font> addresses were found matching your search.</td>
</tr>
</table>
<table border="1" width="100%" id="table3" cellspacing="0" cellpadding="2">
<tr>
<td width="16" bgcolor="#F0F0F0">ID</td>
<td bgcolor="#F0F0F0">Address</td>
</tr>
<%
Do While Not RS1.eof
'Feed results into a single variable to display below, this is optional obviously
VarAddress = RS1("HouseName")&" "&RS1("AddNo")&" "&RS1("AddStreet")&" "&RS1("AddArea")&" "&RS1("AddTown")&" "&RS1("AddCity")&" "&RS1("AddCounty")&" "&RS1("AddPostCode")
%>
<tr>
<td width="16" valign="top"><%=RS1("AddID")%> </td>
<td valign="top"><%=VarAddress%> </td>
</tr>
<%
RS1.movenext
Loop
End If
%>
</table>
</body>
</html>
<%
'Clean up the connections and recordsets
RS1.Close
Conn.Close
Set Conn = Nothing
Set RS1 = Nothing
sql = sql1
%>
Comment