<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>ataoPage Class For SQL Server 2005 Row_Number() 分页类</title>
</head>
<body>
<%
""""""调试前请自行添加数据库连接...
Class atao_Page
Private atao_Page
Private tempPage,strWhere,TableName,FieldName,OrderFieldName,CurrentPage,rsTotal,TotalPages,PageSizeNum,key
Private Sub Class_Initialize
atao_Page="This is ataoPage."
tempPage = "" : strWhere = "" : TableName = "" : FieldName = "" : OrderFieldName = ""
CurrentPage = 1 : rsTotal = 0 : TotalPages = 0 : PageSizeNum = 10
End Sub
Public Property Get Author()
Response.Write("atao www.531.hk")
Response.Write("程序在 WIN2008R2 x64 + SQL 2005 环境下调试运行完整无错...")
End Property
Public Property Get Create()
Response.Write("SQL Server 2005 ROW_NUMBER() 分页类 Create : 2010-04-28 15:58 www.531.hk ")
End Property
Public Property Let Table_Name(ByVal Table__Name)
TableName = Table__Name
End Property
Public Property Let Field_Name(ByVal Field__Name)
FieldName = Field__Name
End Property
Public Property Let Search_Where(ByVal str__Where)
tempPage = ""
If Len(Trim(str__Where)) > 1 Then tempPage = " WHERE " & str__Where
strWhere = tempPage
End Property
Public Property Let Order_Field_Name(ByVal Order__FieldName)
OrderFieldName = Order__FieldName
End Property
Public Property Let Page_Size_Num(ByVal Page__SizeNum)
PageSizeNum = Page__SizeNum
End Property
Public Property Get PageSQL()
rsTotal = Cint(Conn.Execute("SELECT COUNT(0) FROM [" & TableName & "] " & strWhere)(0))
TotalPages = Cint((rsTotal + PageSizeNum - 1) \ PageSizeNum)
tempPage = Trim(Request.QueryString("page"))
If isNumeric(tempPage) = False Then tempPage = 1 : If Cint(tempPage) < 2 Then tempPage = 1
CurrentPage = Cint(tempPage)
PageSQL = "SELECT TOP " & PageSizeNum & " * FROM (SELECT ROW_NUMBER() OVER(ORDER BY " & OrderFieldName & ") AS [RowNum]," & FieldName & " FROM [" & TableName & "]" & strWhere & ") AS [TempPageTable] WHERE [TempPageTable].[RowNum] > " & CurrentPage - 1 & " * " & PageSizeNum
End Property
Private Function PageUrl()
key = "page"
strNewUrl = Request.ServerVariables("URL") & "?"
For Each s in Request.QueryString
If s <> key Then strNewUrl = strNewUrl & s & "=" & Request.QueryString(s) & "&"
Next
PageUrl = strNewUrl & key & "="
End Function
Public Sub ShowPages(ShowListOrder)
If rsTotal <= PageSizeNum Then
Response.Write(" ")
Exit Sub
End If
Response.Write(Chr(13) & "<style type=""text/css"">")
Response.Write(".t_page {margin-top:2px; margin-bottom:2px; width:100%; text-align:center; line-height: 30px; }")
Response.Write(".t_page a { text-decoration:none; }")
Response.Write("a.pnum, .pnum { border:1px solid #CCC; background:#fff; margin-right:4px; height:18px; line-height:18px; padding:2px 8px 3px;font-family:Arial, Helvetica, sans-serif; }")
Response.Write("a.page, .page { border:1px solid #577da8; background:#4598d2; color:#FFF; margin-left:4px; margin-right:4px; height:18px; line-height:18px; padding:2px 8px 3px; text-decoration:none;font-family:Arial, Helvetica, sans-serif; }")
Response.Write("a.pnext,.pnext { border:1px solid #CCC; background:#fff; margin-right:4px; height:14px; line-height:14px; padding:3px 5px 2px; width: 50px;font-family:Arial, Helvetica, sans-serif; }")
Response.Write("a.pnum:hover { color:#FFF; border:1px solid #1d619c; background:#1972bd; margin-right:4px; height:18px; line-height:18px; padding:2px 8px 3px; text-decoration:none;font-family:Arial, Helvetica, sans-serif;}")
Response.Write("a.pnext:hover { color:#FFF; border:1px solid #1d619c; background:#1972bd; margin-right:4px; height:14px; line-height:14px; padding:3px 5px; text-decoration:none; font-family:Arial, Helvetica, sans-serif;}")
Response.Write("</style>")
StrURL = PageURL()
If CurrentPage = 1 Then
PreviousPage = ""
Else
PreviousPage = "<a href="" & StrURL & CurrentPage - 1 & "" class="pnext">上一页</a>"
End If
If CurrentPage = TotalPages Then
NextPage = ""
Else
NextPage = "<a href="" & StrURL & CurrentPage + 1 & "" class="pnext">下一页</a>"
End If
ShowPageNum = ""
If ShowListOrder = 0 Then
If CurrentPage > 5 Then
xx = CurrentPage - 5 : yy = CurrentPage + 5
Else
xx = 1 : yy = 10
End If
For ii = xx TO yy
If ii > TotalPages Then Exit For
If ii = CurrentPage Then
ShowPageNum = ShowPageNum & "<a class="page">"
Else
ShowPageNum = ShowPageNum & " <a href="" & StrURL & ii & "" class="pnum">"
End If
ShowPageNum = ShowPageNum & ii & "</a>"
Next
End If
ShowTotal = ""
If ShowListOrder = 1 Then
ShowTotal = "<span class="pnum">共<font color="#FF0000"><b>" & rsTotal & "</b></font>条记录<font color="#FF0000"><b>"
ShowTotal = ShowTotal & CurrentPage & "/"
ShowTotal = ShowTotal & TotalPages & "</b></font>页</span>"
End If
ShowTable = Chr(13) & "<div class=""t_page"">"
ShowTable = ShowTable & PreviousPage & " " & ShowPageNum & " " & NextPage & " " & ShowTotal & ""
ShowTable = ShowTable & "</div>" & Chr(13)
Response.Write ShowTable
End Sub
Private Sub Class_Terminate()
atao_Page = ""
End Sub
End Class
%>
<%
"""""""""" SQL 2005 ROW_NUMBER PAGE """"""""""
strWhere = "[id] > 0"
SET ataoPage = New atao_Page
ataoPage.Table_Name = "yw_xl"
ataoPage.Field_Name = " [NewsTitle] "
ataoPage.Search_Where = strWhere
ataoPage.Order_Field_Name = "[id] DESC, [NewsTitle] "
ataoPage.Page_Size_Num = 3
strSQL = ataoPage.PageSQL()
"""""""""" SQL 2005 ROW_NUMBER PAGE END """"""""""
SET rs = Conn.Execute(strSQL)
Do While Not rs.EOF
Response.Write(rs(0) & " - " & rs(1) & "<hr />" & Chr(13))
rs.MoveNext
Loop
rs.Close() : SET rs = Nothing : Conn.Close() : SET Conn = Nothing
ataoPage.ShowPages(0) : SET ataoPage = Nothing
%>
</body>
</html>