联合查询＋分页 — 编程爱好者社区

<%@language=VBScript%>
<%Option Explicit%>



<%
    Dim conMIS
    Set conMIS = Server.CreateObject("ADODB.Connection")
    conMIS.Open g_strDbConnection

    Dim rsList, sqlSn, PageNo, intPageNo, intPageSize, i, strProxyName, strSchoolName, strCourseName, strTeacherName, strClientContact, strSchoolProvince, dtReceiveMoneyTime, lngUserId

    Dim ProxyName, SchoolName, CourseName, TeacherName, ClientContact, SchoolProvince, ReceiveMoneyTime, UserId

        intPageNo = Request.QueryString ("PageNo")
        strProxyName = Request.QueryString("ProxyName")
        strSchoolName = Request.QueryString ("SchoolName")
        strCourseName = Request.QueryString ("CourseName")
        strTeacherName = Request.QueryString ("TeacherName")
        strClientContact = Request.QueryString ("ClientContact")
        strSchoolProvince = Request.QueryString ("SchoolProvince")
        dtReceiveMoneyTime = Request.QueryString ("ReceiveMoneyTime")
        lngUserId = Clng(Request.QueryString ("UserId"))

    Set rsList=server.CreateObject("ADODB.Recordset")

    sqlSn = "SELECT s.*, p.id AS ProvinceId, p.Name AS ProvinceName, l.id AS LogonId, l.UserName AS LogonUserName"
'    sqlSn = sqlSn & " FROM (Logon INNER JOIN (CodeProvince INNER JOIN Sn ON CodeProvince.id=Sn.SchoolProvince) ON Logon.id=Sn.Userid)"
    sqlSn = sqlSn & " FROM ((Sn s INNER JOIN Logon l ON s.UserId = l.Id)"'Sn为主表联合查询表CodeProvince和表Logon
    sqlSn = sqlSn & "              INNER JOIN CodeProvince p ON s.SchoolProvince = p.Id)"
    sqlSn = sqlSn & " WHERE 1 = 1"

    If ( strProxyName <> "" ) Then sqlSn = sqlSn & " AND (s.ProxyName LIKE '%" & strProxyName & "%')"'如果strProxyName值不为空则执行strProxyName值的查询
    If ( strSchoolName <> "" ) Then sqlSn = sqlSn & " AND (s.SchoolName LIKE '%" & strSchoolName & "%')"
    If ( strCourseName <> "" ) Then sqlSn = sqlSn & " AND (s.CourseName LIKE '%" & strCourseName & "%')"
    If ( strTeacherName <> "" ) Then sqlSn = sqlSn & " AND (s.TeacherName LIKE '%" & strTeacherName & "%')"
    If ( strSchoolProvince > 0 ) Then sqlSn = sqlSn & " AND (s.SchoolProvince =" & strSchoolProvince & ")"
    If ( dtReceiveMoneyTime <> "" ) Then sqlSn = sqlSn & " AND (DateDiff('d', s.ReceiveMoneyTime, '" & CDate(dtReceiveMoneyTime) & "') = 0)"
    If ( lngUserId > 0 ) Then sqlSn = sqlSn & " AND (l.Id = " & lngUserId & ")"

    sqlSn = sqlSn & " ORDER BY s.Id DESC"

    If intPageNo = "" Then'如果当前页数为空的话
        PageNo=1'则当前页数为1
    Else
        PageNo=intPageNo'否则为Request.QueryString ("PageNo")
    End If
    rsList.Open sqlSn, conMIS, 3, 1

    intPageSize = PAGE_SIZE'一页显示10条记录，常数

%>
<html>
<head>
    <link href="../Css/css.css" rel="stylesheet" type="text/css">
    <meta http-equiv="Content-Type" content="text/html; charset=gb2312">
</head>
    <body bgcolor="#e1e1e1" background="../Images/Background.gif" topmargin="0">
        <form action="SnList.asp" name="list" method="get">
            <div align="center">
                <br>
                <table rules="rows" width="100%" border="1" align="center" cellpadding="3" cellspacing="0" bordercolor="#cccccc" bordercolorlight="#666666" bordercolordark="#ffffff">
                    <tr class="text">
                        <td rowspan="3"><div align="center">选择搜索内容
                            </div>
                        </td>
                        <td><div align="left">
                                产品版本 <input name="ProxyName" type="text" class="input" id="ProxyName">
                            </div>
                        </td>
                        <td><div align="left">教师名称 <input name="TeacherName" type="text" class="input" id="TeacherName">
                            </div>
                        </td>
                        <td><div align="left">收到汇款的时间 <input name="ReceiveMoneyTime" type="text" class="input" id="ReceiveMoneyTime3">
                            </div>
                        </td>
                    </tr>
                    <tr class="text">
                        <td><div align="left">
                                学校名称 <input name="SchoolName" type="text" class="input" id="SchoolName">
                            </div>
                        </td>
                        <td><div align="left">
                            </div>
                            <div align="left">
                            </div>
                            <div align="left">
                            </div>
                            <div align="left">
                            </div>
                            <div align="left">经手人
                                <%=g_htmlTableToSelect(conMIS, "SELECT Id, UserName FROM LogOn WHERE [Level] = 1 ORDER BY UserName ASC", "UserId", -1, "请选择经手人...")%>
                            </div>
                        </td>
                        <td> </td>
                    </tr>
                    <tr class="text">
                        <td>
                            课程名称 <input name="CourseName" type="text" class="input" id="CourseName"></td>
                        <td>省份
                            <%=g_htmlTableToSelect(conMIS, "SELECT Id, Name FROM CodeProvince ORDER BY Id ASC", "SchoolProvince", -1, "请选择省份...")%>
                        </td>
                        <td><div align="center">
                                <input type="image" src="../Images/Search.gif" name="Submit">
                            </div>
                        </td>
                    </tr>
                </table>
                <br>
                <%
    If (Not rsList.EOF) Then
        rsList.PageSize = intPageSize'规定一页显示10条记录
        rsList.CacheSize = intPageSize
        rsList.AbsolutePage = PageNo'显示当前第几页
%>

    <table width="100%" frame=box border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#cccccc" bordercolorlight="#666666" bordercolordark="#ffffff">
      <tr bgcolor="#cccccc" class="text">
        <td> <div align="center">产品版本</div></td>
        <td> <div align="center">学校名称</div></td>
        <td> <div align="center">课程名称</div></td>
        <td> <div align="center">教师名称</div></td>
        <td> <div align="center">联系人</div></td>
        <td> <div align="center">省份</div></td>
        <td> <div align="center">收到汇款的时间</div></td>
        <td> <div align="center">经手人</div></td>
        <td><div align="center">详情</div></td>
      </tr>
            <%
            i = 0
            DO while(Not rsList.EOF) and ( i < intPageSize)
            %>
      <tr class="text">
        <td height="20"> <div align="center">
            <% Call ShowListColor(strProxyName, "ProxyName") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor(strSchoolName, "SchoolName") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor(strCourseName, "CourseName") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor(strTeacherName, "TeacherName") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor(strClientContact, "ClientContact") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor2(strSchoolProvince, "ProvinceName") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor(dtReceiveMoneyTime, "ReceiveMoneyTime") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor2(lngUserId, "LogonUserName") %>
          </div></td>
        <td><div align="center"><a href =ShowSn.asp?ShowSnid=<% =rsList("id") %>>查看</a></div></td>
      </tr>
      <%
            i = i + 1
            rsList.MoveNext
        Loop
    Else
        Response.Redirect("searcherror.asp")
    End IF


%>
    </table>
            </div>
        </form>
        <table width="100%" border="0">
            <tr>
                <td align="right">
                    <font class="text">首页
                        <%
Response.Write("|")
    For i=1 To rsList.PageCount
            If strSchoolProvince > 0 Then
                Response.Write("<a href=SnList.asp?PageNo=" & i & "&ProxyName="&strProxyName&"&SchoolName="&strSchoolName&"&CourseName="&strCourseName&"&TeacherName="&strTeacherName&"&ClientContact="&strClientContact&"&SchoolProvince="&strSchoolProvince&">"&cstr(i)&"</a>")
            ElseIf dtReceiveMoneyTime <> "" Then
                Response.Write("<a href=SnList.asp?PageNo=" & i & "&ProxyName="&strProxyName&"&SchoolName="&strSchoolName&"&CourseName="&strCourseName&"&TeacherName="&strTeacherName&"&ClientContact="&strClientContact&"&ReceiveMoneyTime="&dtReceiveMoneyTime&">"&cstr(i)&"</a>")
            ElseIf lngUserId > 0 Then
                Response.Write("<a href=SnList.asp?PageNo=" & i & "&ProxyName="&strProxyName&"&SchoolName="&strSchoolName&"&CourseName="&strCourseName&"&TeacherName="&strTeacherName&"&ClientContact="&strClientContact&"&UserId="&lngUserId&">"&cstr(i)&"</a>")
            Else
                Response.Write("<a href=SnList.asp?PageNo=" & i & "&ProxyName=" & strProxyName & "&SchoolName="&strSchoolName&"&CourseName="&strCourseName&"&TeacherName="&strTeacherName&"&ClientContact="&strClientContact&">"&cstr(i)&"</a>")
            End If
        If i<>rsList.PageCount Then
            Response.Write("|")
        End If
    Next
Response.Write("|")
%>
                        尾页</font>
                </td>
            </tr>
        </table>
    </body>
</html>

我也来贴个!
'分类检索-----------------------------------------------
sub search_all(fileds,value)
if instr(value,"%20")>0 then
value=replace(value,"%20"," ")
end if
sql="select * from sou where "&fileds&"='"&value&"' ORDER BY SourceID DESC"
page=midfun(value)
response.write "共有<font color=red>"&rs.recordcount&"</font>条记录,分<font color=red>"&rs.pagecount&"</font>页,每页有<font color=red>"&rs.pagesize&"</font>条记录,当前在第<font color=red>"&page&"</font>页!"
response.write " <a href='search.asp?"&big&"="&valu&"&page=1'>第一页</a> <a href='search.asp?"&big&"="&valu&"&page="&page-1&"'>上一页</a> <a href='search.asp?"&big&"="&valu&"&page="&page+1&"'>下一页</a> <a href='search.asp?"&big&"="&valu&"&page="&rs.pagecount&"'>末页</a>"
response.write "  </td></tr>"
response.write "</table>"
rs.close
set rs=nothing
end sub
'------------------------------共用函数
function midfun(key)
dim page
page=0
set rs=server.createobject("adodb.recordset")
rs.open sql,conn,1,1
if rs.eof and rs.bof then
response.write "<center><img src=""img/err404.gif"">在本系列中未搜索到信息！</center>"
else
rs.PageSize=10
if cint(request.querystring("page"))>0 and cint(request.querystring("page"))<=rs.pagecount then
page=request.querystring("page")
else
page=1
end if
rs.AbsolutePage=page
response.write ">> 关键字""<font color=red>"&key&"</font>""的搜索结果 <<"
response.write "<table id=optable cellpadding=0 cellspacing=0 border=1 style='border-collapse: collapse'>"
do while j<rs.PageSize and not rs.eof
if len(rs(1))>37 then
titl=left(rs(1),37)&"..."
else
titl=rs(1)
end if
titl=replace(titl,trim(request("keyword")),"<font color=red>"&request("keyword")&"</font>")
dim c_class
c_class=rs(4)&" "&rs(5)&" "&rs(6)&" "&rs(7)&" "&rs(8)&" "&rs(9)
response.write "<tr><td class=tt><img border=0 src='img/li.gif'>"&vbcrlf
response.write "<a href='showfiles.asp?SourceId="&rs(0)&"' target='_blank' onmouseover=""showpic('e','"&rs(10)&"');"" onmouseout=""closepic();"">"&titl&"</a>"&vbcrlf
response.write "<br>   <font color=gray>   "&c_class&"</font></td></tr>"&vbcrlf
j=j+1
rs.movenext
loop
response.write "</table>"
end if
response.write "<br><table align=center cellpadding=0 cellspacing=0 border=0 style='border-collapse: collapse'>"
response.write "<tr align=center><td class=tt align=right>"
midfun=page
end function

这是我程序进行搜索后,显示结果的分页代码!效果
http://www.oucbio.cn/search.asp?big=脊索动物&page=1
是自己写的协会网站的

search_all(fileds,value)函数传递的是querystring中的big和脊索动物,例如
search_all("big","脊索动物"),选择性搜索用的

主题：联合查询＋分页

回复列表（共59个回复）

我来回复

程序员工具箱 new

代码片段

本版新帖