if request.querystring("action") = "search" then
'判斷接收到的(de)action值是否爲search,如果是的(de),則開始搜索
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") '取得(de)表單中的(de)關鍵字
keywords=Split(strkeyword,"|") '開始提取并鍵字
i=0
strsql="select * from 這(zhè)裏填寫字段名 where"
for each key in keywords
strsql=strsql+" or title like '%"&keywords(i)&"%'"
i=i+1
next
strsql=Replace(strsql, "where or", "where") '把SQL語句裏的(de)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("字段名") &"
" '這(zhè)裏要改成想要顯示的(de)字段
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 '隻有一個(gè)關鍵字時(shí),就不用(yòng)執行循環了(le).
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'循環到第一個(gè)關鍵詞時(shí)
14. sql=sql&" (I_title like '%"&keyword1(i)&"%' or I_Keyword like '%"&Keyword1(i)&"%') and"
15. else
16. if i=arrayi then '循環到最後關鍵詞時(shí)
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
方法二：（用(yòng)類實現，不是很靈活）
1. <%
2. Class Search
3. Private objRequest
4. Private objRs
5. Private objConn
6. Private bolExactitude
7.
8.
9. '*********************************************************
10. ' 初始化(huà)/終止程序
11. '*********************************************************
12. Private Sub Class_Initialize()
13. Dim DBPath
14. '确定使用(yòng)何種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語句： 從外部讀取數據庫連接對(duì)象、查詢條件
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. '依次讀取輸入的(de)多(duō)關鍵字
53. For Each strItem in objRequest
54. strName = objRequest(strItem)
55. Next
56. strName = Rtrim(Ltrim(strName)) '去掉首尾空格
57. strNametmp = split(strName, " ") '将多(duō)關鍵字載入臨時(shí)數組
58. strNamemax = Ubound(strNametmp) '獲得(de)臨時(shí)數組的(de)最大(dà)下(xià)标
59.
60. 'SQL多(duō)關鍵字查詢核心
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. '多(duō)關鍵字
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 "目前通(tōng)訊錄中沒有記錄"
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 &"電話(huà)："& str1 &"學校："& str2 &"
"
108. objRs.MoveNext
109. Loop
110. End If
111. End Function
112.
113.
114. '*********************************************************
115. ' 公有方法： 由外部調用(yòng)輸出結果
116. '*********************************************************
117. Public Function SearchOut()
118. SearchSql
119. End Function
120. End Class
121. %>
122.
123. 調用(yòng)類處理(lǐ)
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. '向類中傳遞數據庫連接對(duì)象、查詢條件
134. Set objFormSearch.Connection = objConn
135. objFormSearch.Exactitude = Request("Exactitude")
136.
137. '調用(yòng)内部方法輸出查詢結果
138. Response.Write objFormSearch.SearchOut()
139.
140. Response.Write objFormSearch.Out()
141. %>
表單
1. <%@ CODEPAGE = "936" %>
2.
方法三
查詢時(shí)可(kě)以輸入多(duō)字符串,中間用(yòng)空格隔開實現模糊查詢,查詢結果會将關鍵字用(yòng)紅色突出顯示.
1. function seachKey(otypestr,keystr) '構造模糊查詢語句，otypestr：查詢字段，keystr：查詢關鍵字
2. dim tmpstr,MyArray,I
3. MyArray = Split(keystr) '默認以空格分(fēn)組
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)'用(yòng)紅粗突出顯示查詢結果
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
用(yòng)法：
1. dim strWhere=seachKey(otypestr,keystr)
2. sql="select * from NEWS where "&strWhere&" order by id desc"
3. 輸入：當我們輸入的(de)keystr爲“我們 函數 數組”時(shí)構造的(de)sql語句如下(xià)面這(zhè)樣
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) '默認以空格分(fēn)組
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)'用(yòng)紅粗突出顯示查詢結果
Dim MyArray
MyArray = Split(keyStr)
For I = Lbound(MyArray) to Ubound(MyArray)
contentStr=replace(contentStr,MyArray(I),""&MyArray(I)&"")
next
seachResult=contentStr
end function
用(yòng)法：
dim strWhere=seachKey(otypestr,keystr)
sql="select * from NEWS where "&strWhere&" order by id desc"
輸入：當我們輸入的(de)keystr爲“我們 函數 數組”時(shí)構造的(de)sql語句如下(xià)面這(zhè)樣
select * from NEWS where content like '%我們%' and content like '%函數%' and content like '%數組%' order by id desc
