if request.querystring("action") = "search" then
'判断接收到的action值是否为search,如果是的,则开始搜索
dim strsql
dim strconn
dim objrs
dim strkeyword
'定义数据库链接串
strconn = "provider=microsoft.jet.oledb.4.0;data
source="&server.mappath("**.mdb")
set objconn = server.createobject("adodb.connection")
objconn.open strconn
strkeyword = request.form("keyword") '取得表单中的关键字
keywords=Split(strkeyword,"|") '开始提取并键字
i=0
strsql="select * from 这里填写字段名 where"
for each key in keywords
strsql=strsql+" or title like '%"&keywords(i)&"%'"
i=i+1
next
strsql=Replace(strsql, "where or", "where") '把SQL语句里的where or 替换成where
set objrs = server.createobject("adodb.recordset")
objrs.open strsql, objconn,1,3
if not rs.eof then '判断是否有记录,如果有则循环显示
while not objrs.eof
response.write "" &objrs("字段名") &"
" '这里要改成想要显示的字段
objrs.movenext
wend
else '如果指针到末尾,则表示没有记录
response.write "没有记录"
end if
end if
%>
================================================================
方法一
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, "" & strName & "")
105. str1 = Replace(objRs("Tel"), strName, "" & strName & "")
106. str2 = Replace(objRs("School"),trim(strName),"" & trim(strName) & "")
107. Response.Write "姓名："& str &"电话："& str1 &"学校："& str2 &"
"
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.
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.
方法三
查询时可以输入多字符串,中间用空格隔开实现模糊查询,查询结果会将关键字用红色突出显示.
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),""&MyArray(I)&"")
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),""&MyArray(I)&"")
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
