Question

asp搜索数据库关键字

<!-- 搜索块-->
<%
searchname=request.Form("searchname") '得到用户填写的字符串
sql="select * from products_biao where name like '%"&searchname&"%'" '写sql语句，查找出结果
setrs=server.CreateObject("adodb.recordset") '设置一个结果集对象
rs.open sql,conn,3,1
while not rs.eof '循环输出商品的相关信息
%>
<table style="float:left">
<tr>
<td width="103"rowspan="3"><a href="product.asp?id=<%=rs("id")%>"><img src="images/<%=rs("pic")%>"width="100"height="100"border="0"></a></td>
<td width="107">名称：
<%=rs("name")%></td>
</tr>
<tr>
<td>价格：<%=rs("benjiage")%></td>
</tr>
</table>
<%
rs.movenext '指向下一个记录
wend
rs.close
setrs=nothing
conn.close
setconn=nothing
%>
<!-- 搜索块-->

各位大侠帮帮忙，帮我看下为什么我这段代码在搜索数据库时是出错的，不能显示
<form id="form4" name="form4" method="post" action="search/search.asp">
<input name="searchname" type="text" id="searchname" style="height:16px; width:220px;" value="请输入要查询商品" maxlength="30" />
</form>
后面的是查询页传过来的东西

Answer 1

你的提交按钮呢
改一下,如下:
<form id="form4" name="form4" method="post" action="search/search.asp">
<input name="searchname" type="text" id="searchname" style="height:16px; width:220px;" value="请输入要查询商品名称" maxlength="30" />
<input name="" id="" type="submit" value="提交">
</form>

search/search.asp 改动如下：
<%
searchname = trim(request("searchname"))
'这里要判断你输出的搜索词语是否为空 isnumeric是判断输入的值是否是数字
if searchname ="" or isnumeric(searchname ) then
strWhere = "" ‘值为空或是数字 查询条件将为空
else
strWhere = " where name like '%"&searchname &"%' " '值为字符串时
end if

set rs = server.CreateObject("adodb.recordset")
sql = "select * from products_biao "&strWhere
rs.open sql,conn,1,3
if not rs.eof then
while not rs.eof
%>
<table style="float:left">
<tr>
<td width="103"rowspan="3"><a href="product.asp?id=<%=rs("id")%>"><img src="images/<%=rs("pic")%>"width="100"height="100"border="0"></a></td>
<td width="107">名称：
<%=rs("name")%></td>
</tr>
<tr>
<td>价格：<%=rs("benjiage")%></td>
</tr>
</table>
<%
rs.movenext '指向下一个记录
wend
else
response.Write("你搜索的关键词暂无信息")
end if
rs.close
set rs = nothing
conn.close
set conn = nothing
%>

Answer 2

方法一
1. keyword=trim(request("keyword"))
2. strkeyword=instr(keyword," ")
3. if strkeyword=0 then'是否为+号
4. keyword1=split(keyword,"+")
5. else
6. keyword1=split(keyword," ")
7. end if
8. Arrayi=ubound(keyword1)
9. if arrayi=0 then '只有一个关键字时,就不用执行循环了.
10. sql=sql&" (I_title like '%"&keyword1(i)&"%' or I_Keyword like '%"&keyword1(i)&"%')"
11. else
12. for i=0 to Arrayi
13. if i=0 then'循环到第一个关键词时
14. sql=sql&" (I_title like '%"&keyword1(i)&"%' or I_Keyword like '%"&Keyword1(i)&"%') and"
15. else
16. if i=arrayi then '循环到最后关键词时
17. sql=sql&" (I_title like '%"&keyword1(i)&"%' or I_Keyword like '%"&Keyword1(i)&"%')"
18. else
19. sql=sql&" (I_title like '%"&keyword1(i)&"%' or I_Keyword like '%"&Keyword1(i)&"%') and"
20. end if
21. end if
22. next
23. end if

方法二：（用类实现，不是很灵活）
1. <%
2. Class Search
3. Private objRequest
4. Private objRs
5. Private objConn
6. Private bolExactitude
7.
8.
9. '*********************************************************
10. ' 初始化/终止程序
11. '*********************************************************
12. Private Sub Class_Initialize()
13. Dim DBPath
14. '确定使用何种Request集合
15. If Ucase(Request("Collection")) = "QUERYSTRING" Then
16. Set objRequest = Request.QueryString
17. Else
18. Set objRequest = Request.Form
19. End If
20.
21. Set objRs = Server.CreateObject("ADODB.Recordset")
22. End Sub
23.
24. Private Sub Class_Terminate()
25. Set objRequest = Nothing
26. Set objRs = Nothing
27. Set objConn = Nothing
28. End Sub
29.
30.
31. '*********************************************************
32. ' Set语句： 从外部读取数据库连接对象、查询条件
33. '*********************************************************
34. Public Property Let Exactitude(strExactitude)
35. bolExactitude = strExactitude
36. End Property
37.
38. Public Property Set Connection(objConnection)
39. Set objConn = objConnection
40. End Property
41.
42.
43. '*********************************************************
44. ' 私有方法： 模糊查询并“输出结果”
45. '*********************************************************
46. Private Function SearchSql()
47. Dim strItem, strName, strNametmp, strNamemax, Item
48. Dim sqlF1, sqlF2, sqlF3, sqlSearch
49. sqlF1 = ""
50. sqlF2 = ""
51. sqlF3 = ""
52. '依次读取输入的多关键字
53. For Each strItem in objRequest
54. strName = objRequest(strItem)
55. Next
56. strName = Rtrim(Ltrim(strName)) '去掉首尾空格
57. strNametmp = split(strName, " ") '将多关键字载入临时数组
58. strNamemax = Ubound(strNametmp) '获得临时数组的最大下标
59.
60. 'SQL多关键字查询核心
61. '单关键字
62. If bolExactitude = "" Then
63. If strNamemax = 0 Then
64. sqlF1 = sqlF1 & " Name LIKE '%" & strName & "%'"
65. sqlF2 = sqlF2 & " Tel LIKE '%" & strName & "%'"
66. sqlF3 = sqlF3 & " School LIKE '%" & strName & "%'"
67. Else
68. '多关键字
69. For Item = 0 to strNamemax
70. If Item = 0 then
71. sqlF1 = sqlF1 & " (Name LIKE '%" & strNametmp(Item) & "%' OR "
72. sqlF2 = sqlF2 & " (Tel LIKE '%" & strNametmp(Item) & "%' OR "
73. sqlF3 = sqlF3 & " (School LIKE '%" & strNametmp(Item) & "%' OR "
74. Else
75. If Item = strNamemax then
76. sqlF1 = sqlF1 & " Name LIKE '%" & strNametmp(Item) & "%') "
77. sqlF2 = sqlF2 & " Tel LIKE '%" & strNametmp(Item) & "%') "
78. sqlF3 = sqlF3 & " School LIKE '%" & strNametmp(Item) & "%') "
79. Else
80. sqlF1 = sqlF1 & " Name LIKE '%" & strNametmp(Item) & "%' OR "
81. sqlF2 = sqlF2 & " Tel LIKE '%" & strNametmp(Item) & "%' OR "
82. sqlF3 = sqlF3 & " School LIKE '%" & strNametmp(Item) & "%' OR "
83. End If
84. End If
85. Next
86. End If
87. Else
88. If strNamemax = 0 Then
89. sqlF1 = sqlF1 & " [Name] = '"&strName&"'"
90. sqlF2 = sqlF2 & " [Tel] = '"&strName&"'"
91. sqlF3 = sqlF3 & " [School] = '"&strName&"'"
92. End If
93. End If
94. sqlSearch = "SELECT * FROM [data] WHERE "&sqlF1&" OR "&sqlF2&" OR "&sqlF3
95. objRs.Open sqlSearch,objConn,1,1
96.
97. '输出查询结果
98. Dim str, str1, str2
99. If objRs.EOF And objRs.BOF Then
100. Response.Write "目前通讯录中没有记录"
101. Else
102. Do While Not objRs.EOF
103. '将关键字(单)变成红色
104. str = Replace(objRs("Name"), strName, "<b style='color:#FF6347'>" & strName & "</b>")
105. str1 = Replace(objRs("Tel"), strName, "<b style='color:#FF6347'>" & strName & "</b>")
106. str2 = Replace(objRs("School"),trim(strName),"<b style='color:#FF6347'>" & trim(strName) & "</b>")
107. Response.Write "姓名："& str &"电话："& str1 &"学校："& str2 &"<br>"
108. objRs.MoveNext
109. Loop
110. End If
111. End Function
112.
113.
114. '*********************************************************
115. ' 公有方法： 由外部调用输出结果
116. '*********************************************************
117. Public Function SearchOut()
118. SearchSql
119. End Function
120. End Class
121. %>
122.
123. 调用类处理
124. <!-- #include file="searchclass.asp" -->
125. <%
126. Dim objFormSearch
127. Set objFormSearch = New Search
128.
129. Set objConn = Server.CreateObject("ADODB.Connection")
130. DBPath = Server.MapPath("search.mdb")
131. objConn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & DBPath
132.
133. '向类中传递数据库连接对象、查询条件
134. Set objFormSearch.Connection = objConn
135. objFormSearch.Exactitude = Request("Exactitude")
136.
137. '调用内部方法输出查询结果
138. Response.Write objFormSearch.SearchOut()
139.
140. Response.Write objFormSearch.Out()
141. %>
表单
1. <%@ CODEPAGE = "936" %>
2. <form method="post" action="sfc.asp">
3. <input type="hidden" name="Collection" value="Form">
4. <input type="radio" name="Exactitude" value="True">
5. name:<input type="text" name="name!^d{3}-\d{3}--\d{4}$">
6. <input type="submit" value="Go">
7. </form>

方法三
查询时可以输入多字符串,中间用空格隔开实现模糊查询,查询结果会将关键字用红色突出显示.
1. function seachKey(otypestr,keystr) '构造模糊查询语句，otypestr：查询字段，keystr：查询关键字
2. dim tmpstr,MyArray,I
3. MyArray = Split(keystr) '默认以空格分组
4. For I = Lbound(MyArray) to Ubound(MyArray)
5. if I=0 then
6. tmpstr=otypestr & " like '%"&MyArray(I)&"%'"
7. else
8. tmpstr=tmpstr & " and " & otypestr & " like '%"&MyArray(I)&"%'"
9. end if
10. Next
11. seachKey=tmpstr
12. end function
13.
14. function seachResult(contentStr,keyStr)'用红粗突出显示查询结果
15. Dim MyArray
16. MyArray = Split(keyStr)
17. For I = Lbound(MyArray) to Ubound(MyArray)
18. contentStr=replace(contentStr,MyArray(I),"<font color=red><strong>"&MyArray(I)&"</strong></font>")
19. next
20. seachResult=contentStr
21. end function
用法：
1. dim strWhere=seachKey(otypestr,keystr)
2. sql="select * from NEWS where "&strWhere&" order by id desc"
3. 输入：当我们输入的keystr为“我们 函数 数组”时构造的sql语句如下面这样
4. select * from NEWS where content like '%我们%' and content like '%函数%' and content like '%数组%' order by id desc
============================================================
function seachKey(otypestr,keystr) '构造模糊查询语句，otypestr：查询字段，keystr：查询关键字
dim tmpstr,MyArray,I
MyArray = Split(keystr) '默认以空格分组
For I = Lbound(MyArray) to Ubound(MyArray)
if I=0 then
tmpstr=otypestr & " like '%"&MyArray(I)&"%'"
else
tmpstr=tmpstr & " and " & otypestr & " like '%"&MyArray(I)&"%'"
end if
Next
seachKey=tmpstr
end function

function seachResult(contentStr,keyStr)'用红粗突出显示查询结果
Dim MyArray
MyArray = Split(keyStr)
For I = Lbound(MyArray) to Ubound(MyArray)
contentStr=replace(contentStr,MyArray(I),"<font color=red><strong>"&MyArray(I)&"</strong></font>")
next
seachResult=contentStr
end function
用法：
dim strWhere=seachKey(otypestr,keystr)
sql="select * from NEWS where "&strWhere&" order by id desc"
输入：当我们输入的keystr为“我们 函数 数组”时构造的sql语句如下面这样
select * from NEWS where content like '%我们%' and content like '%函数%' and content like '%数组%' order by id desc
===================================================

Answer 3

1,没有submit 2,rs.open sql,conn,1,1 3,把search.asp拿出来放在根目录下 4,调整数据库连接文件