Program implementation function: paging display of data in two data tables.
Recently, I encountered a relatively rare requirement. I wonder if you have encountered it too, so I would like to share it with you.
Customer requirement: two parts of data, two from different locations. The two tables in the database are defined as databases DB1 and DB2, and tables Table1 and Tabel2. The data of these two tables should be displayed in the upper and lower parts of the page respectively, and the same paging number should be used, such as the first part of the data. If it is not enough, the second part of data will be filled in
'/*code*/
<%
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)) 'Get the number of records in the first table
record_count2=Clng(objConn2.execute(StrSQL2)(0))'Get the number of records in the second table
record_count=record_count1+record_count2 'Get the number of records in the two tables and
If record_count>0 Then
page_size1=5 'The default number of displayed parts of the data in the first table per page
page_size2=5 'Default display number of partial data in the second table per page
page_count1=CInt(record_count1/page_size1)
If (record_count1 Mod page_size1)<(page_size1/2) Then page_count1=page_count1+1 'Get the page number of the first part of the data
page_count2=CInt(record_count2/page_size2)
If (record_count2 Mod page_size2)<(page_size2/2) Then page_count2=page_count2+1'Get the page number of the second part of the data
if Cint(page_count2)=cint(page) then 'Assume that the second part of the data must be less, here determine whether The current page is the last page with less data
thepageRecordcount=record_count2-(page-1)*5 'The number of data displayed on the last page of the second part of data
page_size1=10-cint(thepageRecordcount) 'The number of items displayed on the last page of the second part of the first part of data'
page_size1=cint(thepageRecordcount) 'Get the number of items displayed in the first part on this page
elseif cint(page)>cint(page_count2) then 'After this, all the first part of data will be
page_size1=10
page_size2=0
end if
page_count=CInt(record_count/(page_size1+page_size2) 'The number of pages in both categories together
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 'Display query on the first page
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 'Display query on page 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.openStrSQL1,objconn1
For n = 1 To page_size1
If rs1.Eof Then Exit For
%>
<!--The first part of the displayed content loops-->
<%
rs1.MoveNext
If rs1.EOF Then Exit For
Next
rs1.close
set rs1=nothing
%>
<%
Set Rs2=Server.CreateObject("Adodb.Recordset")
rs2.openStrSQL2,objconn2
For n = 1 To page_size2
If rs2.Eof Then Exit For
%>
<!--The second part of the displayed content loops-->
<%
rs2.MoveNext
If rs2.EOF Then Exit For
Next
rs2.close
set rs2=nothing
%>
<!--Result page number-->
<%if page<>"1" then%><a href=?page=<%=Cint(page-1)%>>Previous page</a> <%end if%>
<% 'Use a for loop to write out the page number connection
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)&">Next page</a>"
end if
%>
<!--Result page number-->
'/*End of code*/
My level is limited, the program is rough, there may be shortcomings~I hope you can criticize and correct me~