首页 新闻 搜索 专区 学院

【困惑】分页存储过程+AspNetPager分页控件,只显示第一页??~~~

0
悬赏园豆:100 [已关闭问题]

存储过程:
CREATE PROCEDURE [dbo].[ThePagerIndex]
@strTable varchar(50) = '[dbo].[zp_ReleaseJob]',
@strField varchar(50) = '*',
@intTop int = 5000,
@pageSize int = 2,
@pageIndex int = 1,
@strWhere varchar(50) = '1=1',
@strSortKey varchar(50) = 'rel_ID',
@strSortField varchar(50) = 'rel_ID desc',
@strOrderBy bit = 1,
@pageCount int OUTPUT,
@RecordCount int OUTPUT
--@UsedTime int OUTPUT
AS
SET NOCOUNT ON
Declare @sqlcount INT
Declare @timediff DATETIME
select @timediff=getdate() 
Begin Tran
DECLARE @sql nvarchar(4000),@where1 varchar(300),@where2 varchar(300)
IF @strWhere is null or rtrim(@strWhere)=''
BEGIN--没有查询条件 
SET @where1=' WHERE ' 
SET @where2=' '
END
ELSE
BEGIN--有查询条件 
SET @where1=' WHERE ('+@strWhere+') AND ' --本来有条件再加上此条件 
SET @where2=' WHERE ('+@strWhere+') ' --原本没有条件而加上此条件
END
--SET @sql='SELECT @intResult=COUNT(*) FROM '+@strTable+@where2
IF @intTop<=0
BEGIN
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
END
ELSE
BEGIN
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select top '+ cast(@intTop as varchar(300)) +' '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
END
--print @sql

EXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --计算总记录数
SELECT @pageCount=CEILING((@sqlcount+0.0)/@pageSize) --计算总页数
SELECT @RecordCount = @sqlcount --设置总记录数
IF @pageIndex=1 --第一页
BEGIN
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(300))+' '+@strField+' FROM '+@strTable+ @where2+'ORDER BY '+ @strSortField 
END
Else
BEGIN
IF @strOrderBy=0 
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(300))+' '+@strField+ ' FROM '+@strTable+@where1+@strSortKey+'>(SELECT MAX('+@strSortKey+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(300))+' '+ @strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField
ELSE 
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(300))+' '+@strField+' FROM '+@strTable+@where1+@strSortKey+'<(SELECT MIN('+@strSortKey+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(300))+' '+ @strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField+''
END
EXEC(@sql)
--print @sql
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit TRAN
--set @UsedTime = datediff(ms,@timediff,getdate())
--select datediff(ms,@timediff,getdate()) as 耗时
Return @sqlcount
End
GO

cs代码:
  int currPage = 1;
  int PageSize = 2;

   
  public void Bind()
  {
  int pageCount, RecordCount;

  item.DataSource = GetPage(currPage, PageSize, out pageCount, out RecordCount);
  item.DataBind();
  anp.RecordCount = RecordCount;
  anp.CurrentPageIndex = currPage;
  anp.PageSize = PageSize;
  }

  protected void anp_PageChanged(object sender, EventArgs e)
  {
  int pageCount, RecordCount;
  item.DataSource = GetPage(anp.CurrentPageIndex, PageSize, out pageCount, out RecordCount);
  item.DataBind();
  }

  public DataSet GetPage(int pageindex, int _pageSize, out int pageCount, out int RecordCount)
  {
  pageCount = 0;
  RecordCount = 0;

  string con = GetConnectionStringDao.GetConn();
  SqlConnection conn = new SqlConnection(con);
  SqlCommand objcmd = new SqlCommand(".ThePagerIndex", conn);
  objcmd.CommandType = CommandType.StoredProcedure;
  SqlParameter[] para ={ 
  new SqlParameter("@strTable",SqlDbType.VarChar,-1),
  new SqlParameter("@strField",SqlDbType.VarChar,-1),
  new SqlParameter("@pageSize",SqlDbType.Int),
  new SqlParameter("@pageIndex",SqlDbType.Int),
  new SqlParameter("@strSortKey",SqlDbType.VarChar,-1),
  new SqlParameter("@strSortField",SqlDbType.VarChar,-1),
  new SqlParameter("@strOrderBy",SqlDbType.Bit),
  new SqlParameter("@pageCount",SqlDbType.Int),
  new SqlParameter("@RecordCount",SqlDbType.Int),
  new SqlParameter("@intTop",SqlDbType.Int,-1)
  };

  para[0].Value = "OK";
  para[1].Value = "*";
  para[2].Value = _pageSize;
  para[3].Value = pageindex;
  para[4].Value = "ID";
  para[5].Value = "ID desc";
  para[6].Value = 1;
  para[7].Value = pageCount;
  para[7].Direction = ParameterDirection.Output;
  para[8].Value = RecordCount;
  para[8].Direction = ParameterDirection.Output;
  para[9].Value = -1;

  objcmd.Parameters.AddRange(para);

  conn.Open();
  DataSet dataSet = new DataSet();
  SqlDataAdapter sqlDA = new SqlDataAdapter();
  sqlDA.SelectCommand = objcmd;
  sqlDA.Fill(dataSet,"zp_ReleaseJob");
  return dataSet;

  RecordCount = Convert.ToInt32(objcmd.Parameters["@RecordCount"].Value);
  pageCount = Convert.ToInt32(objcmd.Parameters["@pageCount"].Value);
  conn.Close();
  conn.Dispose();
  }

nzq4的主页 nzq4 | 初学一级 | 园豆:100
提问于:2010-04-10 17:46
< >
分享
其他回答(3)
0

你的分页代码这么多 不应该? 用pagedataSource  可直接分页

善解人衣 | 园豆:203 (菜鸟二级) | 2010-04-10 19:16
用pagedataSource数据多了效率就太差了
支持(0) 反对(0) nzq4 | 园豆:100 (初学一级) | 2010-04-11 13:22
0

你看看anp_PageChanged事件执行没有啊?

AspNetPager分页还是回发的吗,没用过,如果是回发的那这个控件也太差劲了吧。没有回发anp_PageChanged就不会执行。

你看看是不是这个问题,其他地方没发现什么问题的,调试试试~~

LittlePeng | 园豆:3395 (老鸟四级) | 2010-04-10 20:04
只能显示一页,其它页根本不显示,也就是说根本没到调用CHANGED事件这步就出错了
支持(0) 反对(0) nzq4 | 园豆:100 (初学一级) | 2010-04-11 12:34
你可以这样: 1.到数据库里面,或者之间调用分页方法,测试一下分页是否有问题 如果没有问题,在这里提供的数据跟本看不出来的 2. anp_PageChanged为什么不执行?这个你得解决啊
支持(0) 反对(0) LittlePeng | 园豆:3395 (老鸟四级) | 2010-04-11 19:54
0
代码
protected override void Page_Load(object sender, EventArgs e)
{
base.Page_Load(sender, e);
if (!IsPostBack)
{
Bind();
}
}

int currPage = 1;
int PageSize = 10;

public void Bind()
{
int pageCount,RecordCount;
anp.Visible
= true;
Repeater1.DataSource
= bll.GetPage(currPage, PageSize, "", out pageCount, out RecordCount);
Repeater1.DataBind();
anp.RecordCount
= RecordCount;
anp.CurrentPageIndex
= currPage;
anp.PageSize
= PageSize;
}

protected void anp_PageChanged(object sender, EventArgs e)
{
int pageCount, RecordCount;
Repeater1.DataSource
= bll.GetPage(anp.CurrentPageIndex, PageSize,"", out pageCount, out RecordCount);
Repeater1.DataBind();
}
左右期限 | 园豆:269 (菜鸟二级) | 2010-04-12 06:20
bll.GetPage是指?
支持(0) 反对(0) nzq4 | 园豆:100 (初学一级) | 2010-04-12 14:50
@nzq4: 返回IList 分页数据
支持(0) 反对(0) 左右期限 | 园豆:269 (菜鸟二级) | 2010-04-13 23:09
0

那个存储过程有问题  我测试了一下 只能查出第一页,建议换个分页存储过程试试。

火花鸟 | 园豆:205 (菜鸟二级) | 2010-04-16 10:22
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册