ASP搜索查询
html code:
1 <form name="frm_Search" method="get" action="Search.asp"> 2 请输入关键字： 3 <input type="text" name="key" size="10"> 4 <input type="submit" value="搜索"> 5 </form>
asp.net code
1 <!--搜索开始--> 2 <% 3 Dim strProvider,CNN 4 'strProvider="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 5 'strProvider=strProvider & Server.MapPath("") & "datadb_Sample.mdb" '假设数据库存放在主页根目录下的data目录下 6 'Set CNN = Server.CreateObject("ADODB.connection") 7 'CNN.Open strProvider '打开数据库连接 8 9 10 set CNN = server.CreateObject("ADODB.Connection") 11 strProvider = "Provider=SQLOLEDB;Server=localhost;uid=sa;pwd=1qaz2wsx;database=zgweb" 12 CNN.Open constr 13 'sql="select * from TB_USERINFO" 14 'set CNN = conn.execute(sql) 15 %> 16 17 <% 18 '接下来，判断 ASP页所接收到的数据，并在数据库中进行搜索。 19 Dim S_Key,RST,StrSQL 20 S_Key = Trim(Request("key")) '得到搜索关键字的值 21 If S_Key <>"" then 22 Set RST=Server.CreateObject("ADODB.RecordSet") 23 StrSQL=AutoKey(S_Key) '此处使用自定义函数 AutoKey()，该函数为实现智能搜索的核心 24 RST.Open StrSQL,CNN,3,2 '得到搜索后的记录 25 26 If RST.BOF And RST.EOF Then 27 %> 28 <font color="#FF0000">未找到任何结果！！！</font> 29 <% 30 Else 31 %> 32 搜索名称为“<font color="#FF0000"><%= S_Key %></font>”的项，共找到 <font color="#FF0000"><%= RST.RecordCount %></font> 项：<p> 33 <% 34 While Not RST.EOF '遍历整个记录集，显示搜索到的信息并设置链接 35 %> 36 <!-- 此处可设为你所需要的链接目标 --> 37 <font style="font: 12pt 宋体"><a href="info.aspID=<%= RST("source") %>" target="_blank"><%= RST("usersex") %></a></font><br> 38 <!-- 显示部分详细内容 --> 39 <font style="font: 9pt 宋体"><%= Left(RST("usercompany"),150) %></font><p> 40 <% 41 RST.MoveNext 42 Wend 43 RST.Close 44 Set RST=Nothing 45 End If 46 End If 47 %> 48 <% 49 Function AutoKey(strKey) 50 CONST lngSubKey=2 51 Dim lngLenKey, strNew1, strNew2, i, strSubKey 52 53 '检测字符串的合法性，若不合法则转到出错页。出错页你可以根据需要进行设定。 54 'if InStr(strKey,"=")<>0 or InStr(strKey,"`")<>0 or InStr(strKey,"'")<>0 or InStr(strKey," ")<>0 or InStr(strKey," ")<>0 or InStr(strKey,"'")<>0 or InStr(strKey,chr(34))<>0 or InStr(strKey,"")<>0 or InStr(strKey,",")<>0 or InStr(strKey,"<")<>0 or InStr(strKey,">")<>0 then 55 'Response.Redirect "error.htm" 56 57 'End If 58 59 lngLenKey=Len(strKey) 60 61 Select Case lngLenKey 62 Case 0 '若为空串，转到出错页 63 Response.Redirect "error.htm" 64 65 Case 1 '若长度为1，则不设任何值 66 strNew1="" 67 strNew2="" 68 Case Else '若长度大于1，则从字符串首字符开始，循环取长度为2的子字符串作为查询条件 69 For i=1 To lngLenKey-(lngSubKey-1) 70 strSubKey=Mid(strKey,i,lngSubKey) 71 strNew1=strNew1 & " or usersex like '%" & strSubKey & "%'" 72 strNew2=strNew2 & " or usercompany like '%" & strSubKey & "%'" 73 74 Next 75 End Select 76 77 '得到完整的SQL语句 78 AutoKey="Select * from TB_USERINFO where usersex like '%" & strKey & "%' or usercompany like '%" & strKey & "%'" & strNew1 & strNew2 79 'response.Write AutoKey 80 ' response.end() 81 82 End Function 83 %> 84 <% 85 cnn.Close 86 Set cnn=Nothing 87 %> 88 89 <!--搜索结束-->
effect：
分页方法
1 '======================== 2 'getNewsList 3 '新闻列表+分页 4 ' 调用GotTopic，获取数据 5 '========================= 6 Function getNewsListPage(sql,titleNumber,widthNumber,page) 7 'set rs = conn.execute(sql) 8 set rs=server.createobject("adodb.recordset") 9 rs.open sql,conn,1,1 10 11 If rs.eof Then 12 Else 13 rs.pagesize=20 14 page=request.querystring("page") 15 If Not Isnumeric(page) or page="" Then 16 page=1 17 else 18 page=cint(page) 19 End if 20 if page<1 then page=1 21 if page>rs.pagecount then page=rs.pagecount 22 rs.AbsolutePage = page 23 for i=1 to rs.pagesize 24 25 response.Write "<li class=""""><a href=""shownews.asp?news_id="& rs("news_id") &""" title="&rs("news_title") &"target=""_blank""><span class=""yuandian"">●</span> "& gotTopic(rs("news_title"),titleNumber)&"</a><label style=""text-align:right;"">"& jdate(rs("news_date")) &"</label></li>" 26 27 28 rs.movenext 29 if rs.eof then 30 Exit For 31 End if 32 next 33 end if 34 35 response.Write "<div class=""pageStyle"">" 36 response.Write "<ul class=""kkpage"">" 37 if rs.pagecount>1 then '--判断页码是否大于1，大于不显示分页，反之显示分页 38 39 if page>1 then 40 41 response.Write "<li id=""starpage""><a Href=""aload.asp?cat_id="&request("cat_id")&"&Page = 1"">首页</a> </li> " 42 response.Write "<li id='PreviousPage'><a Href=""aload.asp?cat_id="&request("cat_id")&"&Page=page -1 "">上一页</a></li>" 43 44 else 45 46 response.Write " <li id=""currentpage"" style=""background:#dedede;""><a>首页</a></li> " 47 48 response.Write "<li style=""background:#dedede;"" id='currentpage'><a>上一页</a></li>" 49 50 end if 51 52 53 if page>3 then StartPage=page-3 else StartPage=1 54 55 56 if page<rs.pagecount-3 then EndPage=page+3 else EndPage=rs.pagecount 57 58 'end if 59 60 if page<rs.pagecount-3 then EndPage=page+3 else EndPage=rs.pagecount 61 62 for j=StartPage to EndPage 63 if j=page then 64 response.write "<li id='AbsPage'><a href=aload.asp?cat_id="&request("cat_id")&"&page="&j&">"&j&"</a></li>" 65 else 66 response.write "<li><a href=aload.asp?cat_id="&request("cat_id")&"&page="&j&">"&j&"</a></li>" 67 end if 68 next 69 if page<>rs.pagecount then 70 response.write "<li id='NextPage'><a Href='aload.asp?cat_id="&request("cat_id")&"&Page="&(page+1)&"'>下一页</a> <span></span></li>" 71 72 response.write "<li id='endpage'><a Href=""aload.asp?cat_id="&request("cat_id")&"&Page= rs.movenext.PageCount"">尾页</a> </li>" 73 74 else 75 76 response.write "<li style=""background:#dedede;"" id='currentpage'><a>下一页</a></li>" 77 response.write "<li id=""currentpage"" style=""background:#dedede;""><a>尾页</a></li>" 78 end if 79 80 end if 81 82 83 response.write "</ul>" 84 response.write "</div>" 85 'set rs.movenext=nothing 86 87 'rs.movenext 88 89 End function
既然选择了远方，便只顾风雨兼程。
