'/****************設置Connection對象**************************** Public Property Let Conn(ByRef ObjConn) Set Connection=ObjConn End Property Public Property Get Conn() Set Conn=Connection End Property '/****************End******************************************
'/****************設置查詢SQL語句******************************* ''查詢顯示字段 Public Property Let Fields(ByVal Value) List_Fields=Value End Property Public Property Get Fields() Fields=List_Fields End Property ''查詢表名 Public Property Let TableName(ByVal Value) Table_Name=Value End Property Public Property Get TableName() TableName=Table_Name End Property ''查詢條件 Public Property Let Condition(ByVal Value) Query_Where=Value End Property Public Property Get Condition() Condition=Query_Where End Property ''*****************排序部分******************************************** ''Value 語不用寫上Order By 。如： [object].OrderBy="ID Desc,PostTime Asc" Public Property Let OrderBy(ByVal Value) OrderBy_SQL=Value End Property Public Property Get OrderBy() OrderBy=OrderBy_SQL End Property '/****************End******************************************
'/****************返回當前查詢結果的總頁數*********************** Public Property Get PageCount() PageCount=Page_Count End Property Public Property Get RecordCount() RecordCount=Record_Count End Property Public Property Get NextPage() If Cur_PagePage_Count Then NextPage=Cur_Page+1 Else NextPage=Page_Count End If End Property Public Property Get PrePage() If Cur_Page>1 Then PrePage=Cur_Page-1 Else PrePage=Cur_Page End If End Property '/****************End******************************************
'/****************設置一頁顯示的記錄數*************************** Public Property Let PageSize(ByVal Value) If Not IsNumeric(Value) Or Value="" Then Value=10 Else Value=Cint(Value) End If If Value1 Then Value=10 Page_Size=Value End Property Public Property Get PageSize() PageSize=Page_Size End Property ''設置當前的頁碼數************************** Public Property Let Page(ByVal Value) If Not IsNumeric(Value) Or Value="" Then Value=1 Else Value=CLng(Value) End If If Value1 Then Value=1 Cur_Page=Value End Property Public Property Get Page() Page=Cur_Page End Property '/****************End******************************************
Private Sub Class_Initialize '初始化RecordSet對象 Page_Size=10 '默認一頁為10條數據 CurPage=1 '默認當前為第一頁 Record_Count=0 Page_Count=0 End Sub
Private Sub Class_Terminate Call CloseRecordSet End Sub
'/***關閉數據庫的連接******* Private Sub CloseRecordSet On Error Resume Next If IsObject(Rs) Then Rs.Close Set Rs=Nothing End If On Error Goto 0 End Sub
'/**********執行查詢返回對應頁碼的數據*********************************************** Public Function ExecuteBy(ByVal oTableName,ByVal oFields,ByVal oCondition,ByVal oOrderBy) Table_Name=oTableName List_Fields=oFields Query_Where=oCondtion OrderBy_SQL=oOrderBy Set ExecuteBy=Execute() End Function '查詢并返回當前CurPage的頁碼記錄 Public Function Execute() Call CloseRecordSet On Error Resume Next Dim TSQL,TopMod,sWhere If Not IsObject(Connection) Or Table_Name="" Or OrderBy_SQL="" Then Set Execute=Nothing Record_Count=0 Page_Count=0 Exit Function End If If Trim(Query_Where)>"" Then sWhere="Where "Query_Where Else sWhere="" End If TSQL="Select Count(*) From ["Table_Name"] "sWhere Record_Count=Connection.Execute(TSQL)(0) '獲取記錄總數 If Err Then Err.Clear Set Execute=Nothing Record_Count=0 Page_Count=0 Exit Function End If If Record_Count1 Then Set Execute=Nothing Record_Count=0 Page_Count=0 Exit Function End If '取得頁的總數 If Record_Count Mod Page_Size >0 Then TopMod=Record_Count Mod Page_Size Page_Count=Fix(Record_Count/Page_Size)+1 If Cur_PagePage_Count Then TopMod=Page_Size End If Else TopMod=Page_Size Page_Count=Fix(Record_Count/Page_Size) End If If Cur_Page>Page_Count Then Cur_Page=Page_Count If Cur_Page1 Then Cur_Page=1 If Trim(List_Fields)="" Then List_Fields="*" TSQL="Select * From (Select Top "TopMod" * From (Select Top "(Cur_Page*Page_Size)" "List_Fields" From ["Table_Name"] "sWhere" Order By "OrderBy_SQL") Order By "TransformOrder(OrderBy_SQL)")Order By "OrderBy_SQL Set Rs=Connection.Execute(TSQL) If Err Then Err.Clear Set Execute=Nothing Record_Count=0 Page_Count=0 Exit Function End If Set Execute=Rs End Function
'轉換OrderBy的順序 ASC->DESC DESC->ASC Private Function TransformOrder(ByVal Value) If Value="" Then TransformOrder="" Exit Function End If Dim OrderArray,i,Result,ByString,Fields,InPos OrderArray=Split(Value,",") '分解每個字段值 For i=0 To Ubound(OrderArray) If OrderArray(i)>"" Then InPos=InStrRev(Trim(OrderArray(i))," ") '找出排序的順序 If InPos1 Then '如果找不到則是ASC排序 ByString="ASC" Fields=OrderArray(i)+" " Else ByString=Trim(Mid(OrderArray(i),InPos+1)) Fields=Left(OrderArray(i),InPos) If ByString>"" Then ByString=UCase(ByString) Else ByString="ASC" End If End If ''轉換排序 If ByString="ASC" Then ByString="DESC" Else ByString="ASC" End If Result=Result+Fields+ByString+"," End If Next If Result>"" Then Result=Left(Result,Len(Result)-1) TransformOrder=Result End Function End Class
'示例代碼: Sub Show_List Dim Page,PageRs Page=Request("Page") Dim MyPage Set MyPage=New PageClass MyPage.Conn=Conn MyPage.PageSize=20 MyPage.Page=Page MyPage.TableName="table1" MyPage.Fields="*" MyPage.OrderBy="ID Asc" Set PageRs=MyPage.Execute 'Set PageRs=MyPage.ExecuteBy("table1","*","","ID Asc") If PageRs Is Nothing Then Exit Sub Do Until PageRs.Eof Response.Write " tr bgcolor=""#FDFDFD"" style=""cursor:hand"" onmouseover=""this.style.background='#F3F3F3'"" onmouseout=""this.style.background='#FDFDFD'"">" Response.Write " td height=""20"">div align=""center"">"PageRs("ID")"/div>/td>" Response.Write " td>"PageRs("aaaa")"/td>" Response.Write " td>a href="""PageRs("bbbb")""">font color='#000000'>"PageRs("bbbb")"/font>/a>/td>" Response.Write " td>"PageRs("cccc")"/td>" Response.Write " /tr>" PageRs.MoveNext Loop PageRs.Close PageCount=MyPage.PageCount Page=MyPage.Page '取得當前正確的頁碼數 NextPage=MyPage.NextPage PrePage=MyPage.PrePage Set PageRs=Nothing Set MyPage=Nothing End Sub Show_List %>
