<%
'Platform: Win2000+IIS5.0+Sql2000
'PS: The last version was upgraded. The last version was the SQL version and no stored procedures were used.
'Previous version: [Original] Excellent million-level data paging code - (busy for a long time) (http://bbs.bccn.net/thread-175543-1-1.html)
'---------------------------------------------The following code is the web page code
Response.Buffer = True
Dim SqlLocalName,SqlUsername,SqlPassword,SqlDatabaseName
Dim ConnStr,Conn
''''''''''''''''''''''''''' SQL database''''''''''''''' '''''''''''''''''''''''''''''''''''''''
SqlLocalName =(local)'Connection IP [local (local) foreign IP]
SqlUsername=sa 'Database username
SqlPassword=XXXXXX 'User password
SqlDatabaseName=NewsTable 'Database name
ConnStr = Provider=Sqloledb;User ID= & SqlUsername & ; Password= & SqlPassword & ; Initial Catalog = & SqlDatabaseName & ; Data Source= & SqlLocalName & ;
'''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''' '
On Error Resume Next
Set Conn=Server.CreateObject(ADODB.Connection)
Conn.open ConnStr
If Err Then
err.Clear
SetConn=Nothing
Response.Write <div style='font-size:14px;color=#ff0000' align='center'>Database connection error, please check the database connection string</div>
Response.End
End If
'This class performs instantiation code without taking into account all possible conditions for paging. This category is for reference only
'------------------------------------------------ --------------------------------------------------
'PageNo-current page;PageSizeX-page size;PageCountX-total number of pages;FieldCount-total number of records;StrWhere-query conditions
Dim PageNo,PageSizeX,PageCountX,FieldCount,StrWhere
'Perform assignment
PageNo=trim(Request.Querystring(PageNo))
PageSizeX=30' defines the paging size
if isnumeric(PageNo) or PageNo= then'If there is no Page value, initialize the value
PageNo=abs(PageNo)
if PageNo=0 then PageNo=1
else
PageNo=1
end if
StrWhere=(ClassCode=0101 and Hits>20)'Note, pay attention to the length of the characters here
set recom = server.createobject(adodb.command)
recom.activeconnection = Conn
recom.commandtype = 4
recom.commandtext = News_Class
recom.Parameters.Appendrecom.CreateParameter(@StrWhere,202,1,100,StrWhere)
recom.Parameters.Appendrecom.CreateParameter(@PageSize,3,1,,PageSizeX)
recom.Parameters.Appendrecom.CreateParameter(@PageIndex,3,1,,PageNo)
set rs = recom.execute()
if rs.eof then
set rs=nothing : set recom=nothing: Conn.Close : set Conn=nothing
response.write <script LANGUAGE='javascript'>alert('No record found currently, please go back and try again!');history.go(-1);</script>
response.end
end if
'Display data
do while not rs.eof
response.write&rs(Title)& Add time:&rs(AddTime)&<br/>
rs.movenext
loop
'Get the total number of records and calculate the number of pages
set rs = rs.NextRecordset
if rs(countx) > 0 then
FieldCount=rs(countx)
else
FieldCount=0
end if
if (FieldCount mod PageSizeX)>0 then
PageCountX=((FieldCount - (FieldCount mod PageSizeX))/ PageSizeX)+1
else
PageCountX= (FieldCount / PageSizeX)
end if
'Close and release related resources
set rs=nothing : set recom=nothing: Conn.Close : set Conn=nothing
'Show paging
if PageNo<=1 then
Response.Write <font color='#FF0000'>[Home] [Previous Page]</font>
else
Response.Write[<a href='?PageNo=1' target='_self' title='Homepage'><font color='#FF0000'>Homepage</font></a>]
Response.Write[<a href='?PageNo=&(PageNo-1)&' target='_self' title='Previous page'><font color='#FF0000'>Previous page</font>< /a>]
end if
if PageNo>=PageCountX then
Response.Write <font color='#FF0000'>[next page] [last page]</font>
else
Response.Write[<a href='?PageNo=&(PageNo+1)&'target='_self' title='Next page'><font color='#FF0000'>Next page</font>< /a>]
Response.Write[<a href='?PageNo=&PageCountX&' target='_self' title='Last page'><font color='#FF0000'>Last page</font></a>]
end if
Response.Write[Page <font color=red>&PageNo&</font>/total <font color=red> & PageCountX &</font>]
Response.Write [Total<font color=red>&FieldCount&</font> items<font color=red>& PageSizeX & </font> items/page]
Response.Write Go to & <input id=zhuanpage name=zhuanpage size=2 value=&PageNo&> & page <input type=submit value=Go onclick=gotoPage()>
Response.Write</td></tr></table>
Response.Write<script>function gotoPage(){var pg=document.getElementById('zhuanpage').value;if(isNaN(pg)||pg>&PageCountX&||pg<0){window.alert('Please be correct Enter the page number, which can only be a positive integer! And it cannot be greater than &PageCountX&');return false;}else{window.location.href='?PageNo='+document.getElementById('zhuanpage').value+'';return true;}}</script>
%>
'------------------------------------------------ -------The following code is the stored procedure code
CREATE procedureNews_Class
--Information news million-level paging
(
@StrWhere varchar(100),
@PageSize int,
@PageIndexint
)
AS
declare@strSQLvarchar(2000) -- main statement
declare@strCountSQL varchar(2000) -- total record main statement
declare@strTmpvarchar(1000) -- temporary variable
Set @strTmp = Select top + str(@PageSize) + Title,AddTime from Tb_News --Note here that several fields are needed to read several fields
if @StrWhere<>''
Begin
Set @strSQL=@strTmp + where ID < (select min(ID) from (select top + str((@PageIndex-1)*@PageSize)+ ID from Tb_News Where +@StrWhere+order by ID desc)as tblTmp ) and +@StrWhere+ order by ID desc
set @strCountSQL=select count(ID) as countx from Tb_News Where +@StrWhere+
End
else
Begin
Set @strSQL=@strTmp + where ID < (select min(ID) from (select top + str((@PageIndex-1)*@PageSize)+ ID from Tb_News order by ID desc)as tblTmp ) order by ID desc
set @strCountSQL=select count(ID) as countx from Tb_News
End
if @PageIndex = 1
if @StrWhere<>''
Begin
Set @strSQL=@strTmp + Where +@StrWhere+ order by ID desc
End
else
Begin
Set @strSQL=@strTmp + order by ID desc
End
exec(@strSQL)
exec(@strCountSQL)
GO