111,094
社区成员
发帖
与我相关
我的任务
分享.net多表查询分页存储过程，第一页正常，第二页..都出错
后台调用分页代码：
DAL层方法调用存储过程代码
执行结果：第一页显示
正常；第二页和其他页的错误直接上图：
跪求大神解决啊，这个问题已经困扰我两天了，上百万条数据必须得用啊，谢谢各位了
int currPage = 1;
int PageSize =40;
string tabeName = "TableOrders as a left join Customer as b on a.CustomerID=b.CustomerID left join UseCompanyIp as c on a.OrderID=c.OrderID left join Staff as e on a.StaffID=e.StaffID";
string strFide = "(a.RenewalFee+a.ModificationCost) AS moneycount,a.PaymentCycle,a.ModificationCost,a.SerialNumber,a.state,a.Technology,a.RenewalStatus,a.RenewalFee,a.OldExpirationDate,a.RenewalDate,a.OrderID,a.OrderDate,a.ExpirationDate,a.Remarks,a.StaffID,a.CustomerID,a.PackageID,a.ProductID,b.CustomerName,b.Taofc,c.IpAddress,e.StaffName";
string strSortKey = "a.OrderID";
string strSortField = "a.ExpirationDate ";
public void Bind()
{
int pageCount;
int RecordCount;
Repeater1.DataSource = tableorder.GetPagetable(tabeName, strFide, PageSize,currPage, out pageCount, out RecordCount, strSortField, strSortKey);
Repeater1.DataBind();
this.AspNetPager1.RecordCount = RecordCount;
this.AspNetPager1.CurrentPageIndex = currPage;
this.AspNetPager1.PageSize = PageSize;
}DAL层方法调用存储过程代码
public DataTable GetPagetable(string TableNames, string Fields, int PageSize, int page, out int pageCount, out int RecordCount, string fldSort, string ID)
{
pageCount = 0;
RecordCount = 0;
DataTable datatable = new DataTable();
using (SqlConnection conn = new SqlConnection(SQLHelper.scon))
{
SqlCommand objcmd = new SqlCommand("proc_ListPage", conn);
objcmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] para ={
new SqlParameter("@tblName",SqlDbType.VarChar,-1),
new SqlParameter("@fldName",SqlDbType.VarChar,-1),
new SqlParameter("@pageSize",SqlDbType.Int),
new SqlParameter("@page",SqlDbType.Int),
new SqlParameter("@pageCount",SqlDbType.Int),
new SqlParameter("@Counts",SqlDbType.Int),
new SqlParameter("@fldSort",SqlDbType.VarChar,-1),
new SqlParameter("@Sort",SqlDbType.Bit),
new SqlParameter("@ID",SqlDbType.VarChar,-1),
};
para[0].Value = TableNames;
para[1].Value = Fields;
para[2].Value = PageSize;
para[3].Value = page;
para[4].Value = pageCount;
para[4].Direction = ParameterDirection.Output;
para[5].Value = RecordCount;
para[5].Direction = ParameterDirection.Output;
para[6].Value = fldSort;
para[7].Value = 1;
para[8].Value = ID;
objcmd.Parameters.AddRange(para);
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(objcmd);
adapter.Fill(datatable);
RecordCount = Convert.ToInt32(objcmd.Parameters["@Counts"].Value);
pageCount = Convert.ToInt32(objcmd.Parameters["@pageCount"].Value);
conn.Close();
conn.Dispose();
}
return datatable;
}执行结果：第一页显示
正常；第二页和其他页的错误直接上图：跪求大神解决啊，这个问题已经困扰我两天了，上百万条数据必须得用啊，谢谢各位了
...全文
请发表友善的回复…
发表回复
Trace__ 2013-11-16
- 打赏
- 举报
看得晕啊。。。
Trace__ 2013-11-16
- 打赏
- 举报
找伟哥看看！！！！！
Andy__Huang 2013-11-14
- 打赏
- 举报
你写出那么麻烦的sql语句,在执行前你调试一下得到什么结果?
这个错误提示很简单,应该是你的sql语句拼写有问题.
帅得_被人砍 2013-11-14
- 打赏
- 举报
已经解决，不过还是得谢谢你，其实多表链接查询的分页存储，注意的就这几点：1、多表中的主键字段不能为相同的名称。2、多表中不能允许具有相同名称的字段，如果存在相同名称的字段你可以使用AS重命名
帅得_被人砍 2013-11-14
- 打赏
- 举报
已经解决，不过还是得谢谢你们，其实多表链接查询的分页存储，注意的就这几点：1、多表中的主键字段不能为相同的名称。2、多表中不能允许具有相同名称的字段，如果存在相同名称的字段你可以使用AS重命名谢谢大神
谢谢大神帅得_被人砍 2013-11-14
- 打赏
- 举报
select * from (select top 40 (a.RenewalFee+a.ModificationCost) AS moneycount,a.PaymentCycle,a.ModificationCost,a.SerialNumber,a.state,a.Technology,a.RenewalStatus,a.RenewalFee,a.OldExpirationDate,a.RenewalDate,a.OrderID,a.OrderDate,a.ExpirationDate,a.Remarks,a.StaffID,a.CustomerID,a.PackageID,a.ProductID,b.CustomerName,b.Taofc,c.IpAddress,e.StaffName from TableOrders as a left join Customer as b on a.CustomerID=b.CustomerID left join UseCompanyIp as c on a.OrderID=c.OrderID left join Staff as e on a.StaffID=e.StaffID where a.OrderID <(select min(a.OrderID) from(select top 0 a.OrderID from TableOrders as a left join Customer as b on a.CustomerID=b.CustomerID left join UseCompanyIp as c on a.OrderID=c.OrderID left join Staff as e on a.StaffID=e.StaffID order by a.OrderID ASC ) AS TBMaxID) order by a.OrderID ASC ) AS TempTB order by a.OrderID DESC 
全栈极简 2013-11-13
- 打赏
- 举报
在sql存储过程最后exec sp_executesql @strTmp 执行之前，加个print @strTmp,在SQL Server Management Studio里面看看打印出来的sql语句是什么。根据错误提示是sql语句语法错误。
帅得_被人砍 2013-11-13
- 打赏
- 举报
ALTER PROCEDURE proc_ListPage
(
@tblName nvarchar(200), ----要显示的表或多个表的连接
@fldName nvarchar(500) = '*', ----要显示的字段列表
@pageSize int = 10, ----每页显示的记录个数
@page int = 1, ----要显示那一页的记录
@fldSort nvarchar(200) = null, ----排序字段列表或条件
@Sort bit = 0, ----排序方法，1为升序，0为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
@strCondition nvarchar(1000) = null, ----查询条件,不需where
@ID nvarchar(150), ----主表的主键
@Dist bit = 0, ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
@pageCount int = 1 output, ----查询结果分页后的总页数
@Counts int = 1 output ----查询到的记录数
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句
Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句
Declare @strSortType nvarchar(10) ----数据排序规则A
Declare @strFSortType nvarchar(10) ----数据排序规则B
Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造
Declare @npage int
if @Dist = 0
begin
set @SqlSelect = 'select '
set @SqlCounts = 'Count(0)'
end
else
begin
set @SqlSelect = 'select distinct '
set @SqlCounts = 'Count(DISTINCT '+@ID+')'
end
if @Sort=0
begin
set @strFSortType=' DESC '
set @strSortType=' ASC '
end
else
begin
set @strFSortType=' ASC '
set @strSortType=' DESC '
end
if(@fldSort is not null and @fldSort<>'')
begin
set @fldSort=','+@fldSort
end
else
begin
set @fldSort=' '
end
--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
set @sqlTmp = @fldName + ' From ' + @tblName
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
set @strID = ' From ' + @tblName
end
else
begin
set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
end
----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
set @tmpCounts = 1
else
set @tmpCounts = @Counts
--取得分页总数
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
/**//**当前页大于总页数 取最后一页**/
if @page>@pageCount
set @page=@pageCount
--/*-----数据分页2分处理-------*/
declare @pageIndex int --总数/页大小
declare @lastcount int --总数%页大小
set @pageIndex = @tmpCounts/@pageSize
set @lastcount = @tmpCounts%@pageSize
if @lastcount > 0
set @pageIndex = @pageIndex + 1
else
set @lastcount = @pagesize
--为配合显示
--set nocount off
--select @page curpage,@pageSize pagesize,@pageCount countpage,@tmpCounts [Rowcount]
--set nocount on
--//***显示分页
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
if @page=1
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName
+' order by '+ @ID+' '+ @strFSortType+@fldSort
else
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName
+' where '+@ID
if @Sort=0
set @strTmp = @strTmp + '<(select min('
else
set @strTmp = @strTmp + '>(select max('
set @strTmp = @strTmp + @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @ID+' '+ @strFSortType+@fldSort+') AS TBMinID)'
+' order by '+ @ID+' '+ @strFSortType+@fldSort
end
end
else
begin
set @npage = @page
set @page = @pageIndex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(40))+' '+ @fldName+' from '+@tblName
+' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort
else
begin
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName
+' where '+@ID
if @Sort=0
set @strTmp=@strTmp+' <(select min('
else
set @strTmp=@strTmp+' >(select max('
set @strTmp=@strTmp+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@npage-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TBMaxID)'
+' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort
end
end
end
else --有查询条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
if @page=1
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName
+' where 1=1 ' + @strCondition + ' order by '+ @ID+' '+ @strFSortType+@fldSort
else
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName
+' where '+@ID
if @Sort=0
set @strTmp = @strTmp + '>(select max('
else
set @strTmp = @strTmp + '<(select min('
set @strTmp = @strTmp + @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) ' + @strCondition +' order by '+ @ID+' '+ @strFSortType+@fldSort+') AS TBMinID)'
+' '+ @strCondition +' order by '+ @ID+' '+ @strFSortType+@fldSort
end
end
else
begin
set @npage = @page
set @page = @pageIndex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(40))+' '+ @fldName+' from '+@tblName
+' where (1=1) '+ @strCondition +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort
else
begin
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(40))+' '+ @fldName+' from '+@tblName
+' where '+@ID
if @Sort=0
set @strTmp = @strTmp + '<(select min('
else
set @strTmp = @strTmp + '>(select max('
set @strTmp = @strTmp + @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@npage-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) '+ @strCondition +' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TBMaxID)'
+' '+ @strCondition+' order by '+ @ID +' '+ @strSortType+@fldSort+') AS TempTB'+' order by '+ @ID+' '+ @strFSortType+@fldSort
end
end
end
------返回查询结果-----
SET NOCOUNT off
exec sp_executesql @strTmp
print @strTmp 
