[其他] 联合查询＋分页

<%@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>