|
程序?qū)崿F(xiàn)功能：兩個數(shù)據(jù)表的數(shù)據(jù)分頁顯示 最近我遇到一個比較少見的需求,不知大家是否也有遇到過,說出來與大家分享. 客戶需求:兩部分?jǐn)?shù)據(jù),分別來自不同位置的兩個數(shù)據(jù)庫中的兩個表,定義為數(shù)據(jù)庫DB1和DB2,表Table1和Tabel2,要將這兩個表數(shù)據(jù)分別在頁面的上半部分和下半部分顯示,并且用同一個分頁碼,如第一部分?jǐn)?shù)據(jù)不夠,將用第二部分?jǐn)?shù)據(jù)填補(bǔ) '/*代碼*/ <% Set objConn1=Server.CreateObject("ADODB.Recordset")
Set objConn2=Server.CreateObject("ADODB.Recordset")
objConn1.Open "PROVIDER=SQLOLEDB;DATA SOURCE=localhost;UID=sa;PWD=123;DATABASE=DB1"
objConn2.Open "PROVIDER=SQLOLEDB;DATA SOURCE=localhost;UID=sa;PWD=123;DATABASE=DB2" Page=CLng(Request.QueryString("Page"))
if page="" then
page=1
end if
StrSQL1="select * from Table1 order by ID desc"
StrSQL2="select * from Table2 order by ID desc" record_count1=Clng(objConn1.execute(StrSQL1)(0)) '得到第一個表的記錄數(shù)
record_count2=Clng(objConn2.execute(StrSQL2)(0))'得到第二個表的記錄數(shù)
record_count=record_count1+record_count2 '得到兩個表的記錄數(shù)和 If record_count>0 Then page_size1=5 '默認(rèn)第一個表部分?jǐn)?shù)據(jù)的每頁顯示數(shù)
page_size2=5 '默認(rèn)第二個表部分?jǐn)?shù)據(jù)的每頁顯示數(shù) page_count1=CInt(record_count1/page_size1)
If (record_count1 Mod page_size1)<(page_size1/2) Then page_count1=page_count1+1 '得到第一部分?jǐn)?shù)據(jù)的頁數(shù)
page_count2=CInt(record_count2/page_size2)
If (record_count2 Mod page_size2)<(page_size2/2) Then page_count2=page_count2+1'得到第二部分?jǐn)?shù)據(jù)的頁數(shù) if Cint(page_count2)=cint(page) then '假設(shè)第二部分?jǐn)?shù)據(jù)一定少,這里斷定是否當(dāng)前頁是少的那分?jǐn)?shù)據(jù)的最后一頁 thepageRecordcount=record_count2-(page-1)*5 '第二部分?jǐn)?shù)據(jù)的最后一頁的數(shù)據(jù)顯示條數(shù)
page_size1=10-cint(thepageRecordcount) '第一部分?jǐn)?shù)據(jù)在第二部分最后頁的顯示條數(shù) '
page_size1=cint(thepageRecordcount) '得到第一部分在本頁的顯示條數(shù)
elseif cint(page)>cint(page_count2) then '這之后將全是第一部分?jǐn)?shù)據(jù) page_size1=10
page_size2=0 end if page_count=CInt(record_count/(page_size1+page_size2) '全部兩類一起的分頁數(shù)
If (record_count Mod (page_size1+page_size2))<((page_size1+page_size2)/2) Then page_count=page_count+1 If Page < 1 Then
Page = 1
End If
If Page > page_count Then
Page = page_count
End If Dim Pagefrom,Pageto
Pagefrom=page-10
Pageto=page+10 if Pagefrom<1 then
Pagefrom=1
end if
if Pageto>record_count then
Pageto=page_count
end if
End If
If Page<=1 Then '第一頁顯示查詢
StrSQL1="Select Top "&page_size1&" * From Table1 "
StrSQL1=StrSQL1&" Order By ID desc"
StrSQl2="Select Top "&page_size1&" * From Table2 "
StrSQL2=StrSQL2&" order by id desc"
Else '第N頁顯示查詢
StrSQL1="Select Top "&(Page-1)*page_size1&" ID From Table1 "
StrSQL1=StrSQL1&" Order By id Desc"
StrSQL1="SELECT Top 1 ID From ("&StrSQL1&") DERIVEDTBL Order By id "
'Response.Write(SQL)
id =Trim(objConn1.execute(StrSQL1)(0))
StrSQL1="Select Top "&page_size1&" * From Table1"
StrSQL1=StrSQL1&" where id<'"&id&"'"
StrSQL1=StrSQL1&" Order By id Desc"
StrSQL2="Select Top "&(Page-1)*page_size2&" id From Table2 "
StrSQL2=StrSQL2&" Order By id Desc"
StrSQL2="SELECT Top 1 id From ("&StrSQL2&") DERIVEDTBL Order By id "
id =Trim(objconn2.execute(StrSQL2)(0))
StrSQL2="Select Top "&page_size2&" * From Table2 "
StrSQL2=StrSQL2&" where id<'"&id &"'"
StrSQL2=StrSQL2&" Order By id Desc" End If %> <%
If record_count>0 Then %>
<%
Set Rs1=Server.CreateObject("Adodb.Recordset")
rs1.open StrSQL1,objconn1
For n = 1 To page_size1
If rs1.Eof Then Exit For
%>
<!--顯示的第一部分內(nèi)容循環(huán)-->
<%
rs1.MoveNext
If rs1.EOF Then Exit For
Next
rs1.close
set rs1=nothing
%>
<%
Set Rs2=Server.CreateObject("Adodb.Recordset")
rs2.open StrSQL2,objconn2
For n = 1 To page_size2
If rs2.Eof Then Exit For
%>
<!--顯示的第二部分內(nèi)容循環(huán)-->
<%
rs2.MoveNext
If rs2.EOF Then Exit For
Next
rs2.close
set rs2=nothing
%> <!--結(jié)果頁碼-->
<%if page<>"1" then%><a href=?page=<%=Cint(page-1)%>>上一頁</a> <%end if%>
<% '用for循環(huán)來寫出頁數(shù)連接
For i=Pagefrom to Pageto
if i=0 then
i=1
end if if i<>Cint(page) then
strurl="<a href=?page="&i&"><font color=#000000>"&i&"</font></a>" else
strurl="<b><font color=#ce0031>"&i&"</font></b>"
end if response.write strurl
response.write " "
next
%>
<%if page_count =1 or (page_count-Cint(page))=0 then
response.Write("")
else
response.Write "<a href=?page="&Cint(page+1)&">下一頁</a>"
end if
%>
<!--結(jié)果頁碼--> '/*代碼結(jié)束*/ 本人水平有限,程序粗糙,可能有不足之處~望批評指正~
| 
