利用COM组件对于B/S中分页的实现

最新推荐文章于 2024-09-29 21:50:07 发布

ice241018

最新推荐文章于 2024-09-29 21:50:07 发布

阅读量843

点赞数

分类专栏： com+ 文章标签： cmd sql output function asp database

本文链接：https://blog.csdn.net/ice241018/article/details/4031163

版权

com+ 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.运行环境：vb6.0+ASP3.0

2.准备条件:需要一个分页存储过程

存储过程代码如下:

CREATE PROCEDURE sp_newpage
@tb varchar(50), --表名
@col varchar(50), --按该列来进行分页
@coltype int, --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800),--查询条件
@RecordCount Int Output,
@pages int OUTPUT --总页数
AS
/*
功能描述：对指定表中满足条件的记录按指定列进行分页查询，分页可以顺序、倒序
查询可以指定页大小、指定查询任意页、指定输出字段列表，返回总页数
作者：pbsql
版本：1.10
最后修改：2004-11-29
*/
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数

SET @sql='SELECT @RecordCount=COUNT(*) FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT--计算总记录

IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)

3.编写COM组件

Dim MyResponse As response
Dim MyRequest As Request
Dim myApplication As Application
Dim myServer As Server
Dim mySession As Session

Public Sub OnStartPage(myScriptingContent As ScriptingContext)
   Set MyResponse = myScriptingContent.response
   Set MyRequest = myScriptingContent.Request
   Set myServer = myScriptingContent.Server
   Set myApplication = myScriptingContent.Application
   Set mySession = myScriptingContent.Session
End Sub

Public Sub OnEndPage()
     Set MyResponse = Nothing
     Set MyRequest = Nothing
     Set myServer = Nothing
     Set myApplication = Nothing
     Set mySession = Nothing
End Sub

Public Function showtable()

    Page = MyRequest("page")
    If Not IsNumeric(Page) Then
       Page = 1
    End If

    If Page < 1 Then
       Page = 1
    End If

    Dim conn As New ADODB.Connection
    conn.open "Provider=SQLOLEDB;User ID=sa;Password=123;database=jsjdjks;server=127.0.0.1"

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "sp_newpage"

    cmd.Parameters.Append cmd.CreateParameter("tb", adVarChar, adParamInput, 50)
    cmd.Parameters.Append cmd.CreateParameter("col", adVarChar, adParamInput, 50)
    cmd.Parameters.Append cmd.CreateParameter("coltype", adInteger, adParamInput)
    cmd.Parameters.Append cmd.CreateParameter("orderby", adInteger, adParamInput)
    cmd.Parameters.Append cmd.CreateParameter("collist", adVarChar, adParamInput, 800)
    cmd.Parameters.Append cmd.CreateParameter("pagesize", adInteger, adParamInput)
    cmd.Parameters.Append cmd.CreateParameter("page", adInteger, adParamInput)
    cmd.Parameters.Append cmd.CreateParameter("condition", adVarChar, adParamInput, 800)
    cmd.Parameters.Append cmd.CreateParameter("RecordCount", adInteger, adParamOutput)
    cmd.Parameters.Append cmd.CreateParameter("pages", adInteger, adParamOutput)
    cmd("tb") = "userinfo"
    cmd("col") = "id"
    cmd("coltype") = 0
    cmd("orderby") = 0
    cmd("collist") = "*"
    cmd("pagesize") = 1
    cmd("page") = Page
    cmd("condition") = ""
   Set rs = cmd.Execute

   If rs.state = 0 Then '未取到数据，rs关闭
      RecordCount = -1
   Else

   rs.Close '只有关闭才能取出返回值
   RecordPage = cmd.Parameters("pages")
   RecordCount = cmd.Parameters("recordcount")
   MyResponse.write "总记页数=" & cmd("pages")
   MyResponse.write " 总记录数=" & cmd.Parameters("recordcount")
   rs.open '要取出记录集，则要再打开
   If CInt(Page) > CInt(RecordPage) Then
      Page = RecordPage
   End If

   MyResponse.write "<TABLE width=100% bordercolorlight=#C0C0C0 cellspacing=1 bordercolor=#C0C0C0 bordercolordark=#FFFFFF border=1>"
   MyResponse.write "<TR>"
   MyResponse.write "<TD height=20 bgcolor=#C0C0C0>"
   MyResponse.write "<p align=center>用户名</TD>"


   MyResponse.write "<TD bgcolor=#C0C0C0>"
   MyResponse.write "<p align=center>密码</TD>"

   MyResponse.write "</TR>"
   'If Not rs.EOF Then
   Do While Not rs.EOF

    MyResponse.write "<TR>"
    MyResponse.write "<TD height=20>"
    If Not IsNull(rs("username")) And rs("username") <> "" Then MyResponse.write rs("username") Else
    MyResponse.write " </TD>"

    MyResponse.write "<TD>"
    If Not IsNull(rs("password")) And rs("password") <> "" Then MyResponse.write rs("password") Else
    MyResponse.write " </TD>"
    MyResponse.write "</TR>"

rs.movenext
Loop

   'Else

   'End If
   rs.Close
   Set rs = Nothing
    MyResponse.write "<TR>"
    MyResponse.write "<TD height=20 colspan=3>"
    MyResponse.write "<p align=center>总共有" & RecordCount & "条记录 [<a href=page_test.asp?page=1>首页</a>]"
    MyResponse.write "[<a href=page_test.asp?page="
    If (Page - 1) > 0 Then MyResponse.write (Page - 1) Else MyResponse.write "1"
    MyResponse.write ">上一页</a>] [<a href=page_test.asp?page="
    If CInt(Page + 1) > CInt(RecordPage) Then MyResponse.write RecordPage Else MyResponse.write (Page + 1)
    MyResponse.write ">下一页</a>] [<a href=page_test.asp?page=" & RecordPage & ">末页</a>]</TD>"
    MyResponse.write "</TR>"
    MyResponse.write "</TABLE>"
    End If
    Set cmd.ActiveConnection = Nothing
End Function