我这个存储分页不能按其他字段排序,而只能按自动增长的主键id排序,请教怎么修改后能按其他字段排序?
@strSql28是从.cs文件传来的排序字段,如:
cmd.Parameters["@sql28"].Value = " order by " + str + " desc";if @pageIndex = '1'
set @strSql='select top' + str(@pageSize) + ' ' + '* from pur where id!='''''+@strSql4
else
begin
set @strSql = 'select top' + str(@pageSize) + ' ' + '* from pur'
set @strSql = @strSql + ' ' + 'where id!='''''+@strSql4+' '+'and id <('
set @strSql = @strSql + 'select min(id) from ('
set @strSql = @strSql + 'select top '+ str((@pageIndex-1)*@pageSize) + +' id from pur where p_id!='''''+@strSql4+' '+' order by id desc) as t' +') '
end if @sql28 is null or @sql28=''
set @strSql = @strSql + ' ' + 'order by id desc'
else
begin
set @strSql = @strSql + ' '+@sql28
end
@strSql28是从.cs文件传来的排序字段,如:
cmd.Parameters["@sql28"].Value = " order by " + str + " desc";if @pageIndex = '1'
set @strSql='select top' + str(@pageSize) + ' ' + '* from pur where id!='''''+@strSql4
else
begin
set @strSql = 'select top' + str(@pageSize) + ' ' + '* from pur'
set @strSql = @strSql + ' ' + 'where id!='''''+@strSql4+' '+'and id <('
set @strSql = @strSql + 'select min(id) from ('
set @strSql = @strSql + 'select top '+ str((@pageIndex-1)*@pageSize) + +' id from pur where p_id!='''''+@strSql4+' '+' order by id desc) as t' +') '
end if @sql28 is null or @sql28=''
set @strSql = @strSql + ' ' + 'order by id desc'
else
begin
set @strSql = @strSql + ' '+@sql28
end
解决方案 »
- 有关登录程序的问题
- 网站服务器如何获取GPRS数据?
- DataList问题
- DropDownList控件联动,请求高手帮忙!感激不敬!
- VS.NET无法打开应用程序,提示说很可能是本地WEB服务器上没有安装所需的组件.
- 在db2 8.2 企业版用得很好,现在验收,客户要转到db2 8.2 工作组版连接不上????
- 页面传参:如何把控件上的值作为参数来传?
- 运行.net的软件/硬件环境的约束。。。。。。。。。
- 本人自学.Net一个礼拜了,开始还信心百倍,感觉很爽。可现在发现我的智商好象比别人低还是怎么的,一个简单的错误都捆饶了很久。为了不让
- 再提疑问,入门
- 郁闷啊!!!运行的页面如何能得到这个页面在服务器上的绝对路径?
- 大哥,大姐,请教在.cs文件里怎样写类似分页存储的功能?
set @strSql = @strSql + 'select top '+ str((@pageIndex-1)*@pageSize) + +' id from pur where p_id!='''''+@strSql4+' '+' order by id desc) as t' +') '
set @strSql = @strSql + ' ' + 'order by id desc'
else
begin
set @strSql = @strSql + ' '+@sql28
endif @sql28 is null or @sql28=''
set @strSql5 = @strSql5 + ' ' + 'order by id desc'
else
begin
set @strSql5 = @strSql5 + ' '+@sql28
end回楼上的,我当时有这么写,好像还是不能啊
http://yanlixin.cnblogs.com/archive/2005/10/17/256454.html
print @strSql然后到查询分析器里面调试调试
set @sql28 = 'order by indate desc,pono desc'
else
begin
set @strSql = @strSql + ' '+@sql28
end
declare @insertsql nvarchar(2000)
declare @indextable table(id int identity(1,1),nid int)
create table #indextable(id int identity(1,1),nid int)
set @insertsql='insert into #indextable (nid) select id from pur where p_id!='''''+@strSql4+@sql28
exec (@insertsql)
--exec sp_executesql @insertsql)-- select * from #indextabledeclare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageIndex-1)*@pageSize
set @PageUpperBound=@PageLowerBound+@pageSize
begin
set @strSql = 'select pono,vendor,part,specification,unit,qty,recqty,unprc,precdate,orderno,indate,signdate,
case status when 0 then '+''''+'新單'+''''+' when 1 then '+''''+'再協商'+''''+' when 2 then '+''''+'已簽回' +''''+' when 3 then '+''''+'已驗貨'+''''+' when 4 then '+''''+'已收貨'+''''+'
when 5 then '+''''+'已結案'+''''+' when 6 then '+''''+'已作廢'+''''+' end as status,pacpdate,cancletime,status3,status4,depno,p_id,P.id from pur P,#indextable T'
set @strSql = @strSql + ' where P.id=T.nid '+@strSql4+'and T.id between cast('+str(@PageLowerBound)+' as int)+1 and cast('+ str(@PageUpperBound)+' as int) order by T.id'
end