亲，您未登录哦！ 登录 | 注册
首页  |  社区  |  工具箱  |  代码库  |  博客  |  招聘  |  文章  |  新闻  |  下载  |  读书
回 帖 发 新 帖 刷新版面

主题：联合查询＋分页

绝对零度 [专家分：820] 发布于 2003-12-18 13:42:00

<%@language=VBScript%>
<%Option Explicit%>
<!--#include file="../Lib/Config.asp"-->
<!--#include file="../Lib/FormElement.asp"-->
<!--#include file="../Lib/StringFunctions.asp"-->
<%
    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>&nbsp;</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>
本帖地址：  http://bbs.pfan.cn/post/25263.html

回复列表 （共59个回复）

31 楼

maizi007 [专家分：120] 发布于 2006-03-10 00:33:00

一楼的其中一条语句我改了一下
if rs.eof and rs.bof then //应将and 改为Or
               response.write "<p align='center'> 还 没 有 任 何 文 章 </p>"

32 楼

kysy2004 [专家分：110] 发布于 2006-03-31 15:08:00

写成这样吧！exec="select * from 详细信息 where 歌手=‘"&request.querystring("geshou")&"'"

33 楼

雨下的时候 [专家分：440] 发布于 2006-06-16 02:39:00

[quote]简单的联合查询并不难做，但是多关键字的查询怎么实现，不知道各位有什么高见，如有不妨贴出来大家分享一下。[/quote]

我是这样做的：
(1)把<form>里面的各个元素（各列表框），让其直接传递一个“and 字段名=值”的字符串，例如有两列表框a和b，对应数据库表的字段为da和db，就让其在表单提交时，直接传递“and da=值”和“and db=值”这两个字符串，再在传递的页面的SQL语句这样写：sql="select * from 表名 where 1=1 "&request("a")&" "&request("b")，这样就实现了联合查询。

（2）让各列表框直接传值，然后再提交的页面中，这样写：
if request("a")="" then
   pa=""
else
   pa="and "&request("a")
end if 
同样SQL语句这样写：sql="select * from 表名 where 1=1 "&pa&" "&pb

34 楼

yijianqgm [专家分：0] 发布于 2006-06-23 17:49:00

哈哈。。还是有跳转的这个好

35 楼

arong606 [专家分：0] 发布于 2006-06-25 10:27:00

好好长的代码哟！！，我看得好费劲的，不过还是可看懂的，多谢各位，你们辛苦了！！能与大家在这里交流是一种快乐，更是一种享受。

36 楼

bxjbxj [专家分：350] 发布于 2006-07-19 16:19:00

不知道现在的asp.net怎么样为什么不用呢？

37 楼

joken185 [专家分：0] 发布于 2006-07-20 20:37:00

<html><head><title>用户身份验证</title></head><body>
<form action="check.asp" method="post">
<table width=30% align="center">
<tr><td width=100% align="center" bgcolor="#c0c0c0" colspan=2>用户登录 新用户注册</td></tr><tr><td width=25% bgcolor="#c0c0c0">用户名</td><td width=75% bgcolor="#c0c0c0"><input type="text" maxlength=10 size=10 id="name" name="name" value="<%=name%>"></td></tr>
<tr><td width=25% bgcolor="#c0c0c0">密码</td><td width=75% bgcolor="#c0c0c0"><input type="password" maxlength=10 size=20 id="passwd" name="passwd" value="<%=passwd%>"></td></tr>
<tr><td width=25%><input type="submit" name="ok" value="登录"></td>
<td width=75%><input type="submit" name="ok" value="注册"></td></tr>
</table></form></body></html>

38 楼

zilong6391 [专家分：160] 发布于 2006-09-10 16:16:00

哇哈哈哈~~~~~`

39 楼

纵横风云 [专家分：10] 发布于 2006-09-29 17:43:00

谢谢各位大侠。都是好东东！只是今天看太多了，头胀。改天一定好好看。

40 楼

DXYDXYDXY [专家分：0] 发布于 2006-11-18 17:30:00


我来回复

您尚未登录，请登录后再回复。点此登录或注册

程序员工具箱 new

  • 更多工具

    • 代码片段

  • 模拟键盘（不可按）
  • 碰撞的唐三
  • 精彩奥运热点赛事的实时采集
  • 获取天气动态信息
  • 文件查找
  • python大数据抓取京东电脑销量数据
  • 爬虫技术不仅仅支持数据采集
  • 抓取脉脉APP上的职业信息
  • 爬虫中多线程的运用
  • 四则运算以及一元二次方程
  • 欢迎来共享你的NB代码

    • 本版新帖

  • 送段代码给大家
  • 禁用浏览器后退按钮
  • 各位？在ASP中什么样编写上传图形的程序？
  • 一些网页代码奉献
  • 服务器完全安装手册［绝对经典］
  • 小问题，只要2分钟就可。请教数据库排序的三种方式哪种好。
  • [原创]如何在自己的电脑上建立Web服务器。
  • 总结对于主页调用TOP10的一些经验.
  • 用ADODB.Stream做上传下载
  • [转载]ASP技巧28则(权威版)
  • [转载]数据库查询语言
  • 和大家一起研究探讨一个问题！！ 
  • 请问Replace函数怎么用啊？各位高手，可否举例说明？
  • 圪圪请进，有事请教～希望您能帮忙
  • 给大家收集了一些很实用的参考手册下载！（新）