两个不同数据库表的分页显示解决方案

 1 
 2
 3Set objConn1=Server.CreateObject("ADODB.Recordset")   
 4Set objConn2=Server.CreateObject("ADODB.Recordset")   
 5objConn1.Open "PROVIDER=SQLOLEDB;DATA SOURCE=localhost;UID=sa;PWD=123;DATABASE=DB1"   
 6objConn2.Open "PROVIDER=SQLOLEDB;DATA SOURCE=localhost;UID=sa;PWD=123;DATABASE=DB2" 
 7
 8Page=CLng(Request.QueryString("Page"))   
 9if page="" then   
10page=1   
11end if   
12StrSQL1="select * from Table1 order by ID desc"   
13StrSQL2="select * from Table2 order by ID desc" 
14
15record_count1=Clng(objConn1.execute(StrSQL1)(0)) '得到第一个表的记录数   
16record_count2=Clng(objConn2.execute(StrSQL2)(0))'得到第二个表的记录数   
17record_count=record_count1+record_count2 '得到两个表的记录数和 
18
19If record_count>0 Then 
20
21page_size1=5 '默认第一个表部分数据的每页显示数   
22page_size2=5 '默认第二个表部分数据的每页显示数 
23
24page_count1=CInt(record_count1/page_size1)   
25If (record_count1 Mod page_size1)<(page_size1/2) Then page_count1=page_count1+1 '得到第一部分数据的页数   
26page_count2=CInt(record_count2/page_size2)   
27If (record_count2 Mod page_size2)<(page_size2/2) Then page_count2=page_count2+1'得到第二部分数据的页数 
28
29if Cint(page_count2)=cint(page) then '假设第二部分数据一定少,这里断定是否当前页是少的那分数据的最后一页 
30
31thepageRecordcount=record_count2-(page-1)*5 '第二部分数据的最后一页的数据显示条数   
32page_size1=10-cint(thepageRecordcount) '第一部分数据在第二部分最后页的显示条数 '   
33page_size1=cint(thepageRecordcount) '得到第一部分在本页的显示条数   
34elseif cint(page)>cint(page_count2) then '这之后将全是第一部分数据 
35
36page_size1=10   
37page_size2=0 
38
39end if 
40
41page_count=CInt(record_count/(page_size1+page_size2) '全部两类一起的分页数   
42If (record_count Mod (page_size1+page_size2))<((page_size1+page_size2)/2) Then page_count=page_count+1 
43
44If Page < 1 Then   
45Page = 1   
46End If   
47If Page > page_count Then   
48Page = page_count   
49End If 
50
51Dim Pagefrom,Pageto   
52Pagefrom=page-10   
53Pageto=page+10 
54
55if Pagefrom<1 then   
56Pagefrom=1   
57end if   
58if Pageto>record_count then   
59Pageto=page_count   
60end if 
61
62  
63End If 
64
65  
66If Page<=1 Then '第一页显示查询   
67StrSQL1="Select Top "&page_size1&" * From Table1 "   
68StrSQL1=StrSQL1&" Order By ID desc"   
69StrSQl2="Select Top "&page_size1&" * From Table2 "   
70StrSQL2=StrSQL2&" order by id desc" 
71
72Else '第N页显示查询   
73StrSQL1="Select Top "&(Page-1)*page_size1&" ID From Table1 "   
74StrSQL1=StrSQL1&" Order By id Desc"   
75StrSQL1="SELECT Top 1 ID From ("&StrSQL1&") DERIVEDTBL Order By id "   
76'Response.Write(SQL)   
77id =Trim(objConn1.execute(StrSQL1)(0))   
78StrSQL1="Select Top "&page_size1&" * From Table1"   
79StrSQL1=StrSQL1&" where id<'"&id&"'"   
80StrSQL1=StrSQL1&" Order By id Desc" 
81
82  
83StrSQL2="Select Top "&(Page-1)*page_size2&" id From Table2 "   
84StrSQL2=StrSQL2&" Order By id Desc"   
85StrSQL2="SELECT Top 1 id From ("&StrSQL2&") DERIVEDTBL Order By id "   
86id =Trim(objconn2.execute(StrSQL2)(0)) 
87
88StrSQL2="Select Top "&page_size2&" * From Table2 "   
89StrSQL2=StrSQL2&" where id<'"&id &"'"   
90StrSQL2=StrSQL2&" Order By id Desc" 
91
92End If 

1   
2If record_count>0 Then 

1   
2Set Rs1=Server.CreateObject("Adodb.Recordset")   
3rs1.open StrSQL1,objconn1   
4For n = 1 To page_size1   
5If rs1.Eof Then Exit For   

1   
2rs1.MoveNext   
3If rs1.EOF Then Exit For   
4Next   
5rs1.close   
6set rs1=nothing   

1   
2Set Rs2=Server.CreateObject("Adodb.Recordset")   
3rs2.open StrSQL2,objconn2   
4For n = 1 To page_size2   
5If rs2.Eof Then Exit For   

1   
2rs2.MoveNext   
3If rs2.EOF Then Exit For   
4Next   
5rs2.close   
6set rs2=nothing   

1if page<>"1" then

1<a =cint(page-1)="" ```="" href="?page=```">上一页</a>

1end if

1 '用for循环来写出页数连接   
2For i=Pagefrom to Pageto   
3if i=0 then   
4i=1   
5end if 
6
7if i<>Cint(page) then   
8strurl="

1" 
2
3else   
4strurl="

1"   
2end if 
3
4response.write strurl   
5response.write " "   
6next   

1if page_count =1 or (page_count-Cint(page))=0 then   
2response.Write("")   
3else   
4response.Write "

1"   
2end if