深山老熊

' ============================================
' 分页sql语句生成代码
' TblName:    表名
' FldName:    索引字段名:  一般为ID
' FieldList:  需要显示的字段列表
' PageSize:   每页条数
' PageIndex:  当前页码
' OrderType:  排序方式    0: asc  1: desc
' StrWhere:   where 语句
' ============================================
 Function GetPageSql(TblName,FldName,FieldList,PageSize,PageIndex,OrderType,StrWhere)
     Dim StrTemp,StrSql,StrOrder,Strtmp
     '根据排序方式生成相关代码
     If OrderType = 0 Then
         StrTemp = "> (Select Max([" & FldName & "])"
         StrOrder = " Order By [" & FldName & "] Asc"
    Else
        StrTemp = "< (Select Min([" & FldName & "])"
        StrOrder = " Order By [" & FldName & "] Desc"
    End If
    
    '若是第1页则无须复杂的语句
    If PageIndex = 1 Then
        StrTemp = ""
        If StrWhere <> "" Then
            Strtmp = " Where " & StrWhere
        End If
        StrSql = "Select Top " & PageSize & " "&FieldList&" From [" & TblName & "]" & Strtmp & StrOrder
    Else        '若不是第1页，构造sql语句
        StrSql = "Select Top " & PageSize & " "&FieldList&" From [" & TblName & "] Where [" & FldName & "]" & StrTemp & _
        " From (Select Top " & (PageIndex-1) * PageSize & " [" & FldName & "] From [" & TblName & "]" 
        If StrWhere <> "" Then
            StrSql = StrSql & " Where " & StrWhere
        End If
        StrSql = StrSql & StrOrder & ") As Tbltemp)"
        If StrWhere <> "" Then
            StrSql = StrSql & " And " & StrWhere
        End If
        StrSql = StrSql & StrOrder
    End If
    GetPageSql = StrSql        '返回sql语句
End Function