如何使用ASP搜索多个字段
当我第一次开始用VB Script编写时，对我来说，一个主要的问题是构造一种功能，该功能可以使用表单中的多个字段输入来搜索表，并根据用户提供的输入来动态构建sql语句。 我已经在这里描述了数百次快速且自适应的方法。 我通常使用框架页面进行搜索，这样，搜索将保留在上部窗格中，而结果显示在下部。 然后可以根据需要调整搜索，而无需继续返回并从头开始输入搜索。 这适用于Access，SQL Server或任何数据库。 以下代码将搜索一个地址，例如：[/ font]
此解决方案有四个页面，应将它们全部放在同一目录中，以使引用正常工作：
========================================
第1页-主框架页
========================================
<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> 第2页-搜索表单页面
========================================
<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> 第3页-保留页，用于通知用户该怎么做。
========================================
<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> 第4页-结果页。
========================================
<!-- #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
%> From: https://bytes.com/topic/asp-classic/insights/705685-how-dynamically-search-database-table
扫一扫
1751
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
