前台代码没问题,结合aspnetpager写的,@nickname 是通过搜索传过来的参数,把@nickname参数去掉后可以正常显示。 用EXECUTE执行存储过程,可以显示结果,但是不正常,比如每页是3条记录有时候它只会显示一个,或者有时候根据@pageindex 的变化会不显示,琢磨了半天了,也不知道错那哪了?
各位帮帮忙啊!谢了!
ALTER procedure [dbo].[GetUsersBySearch]
(@pagesize int,
@pageindex int,
@docount bit,
@nickname varchar(255)
)
as
set nocount on
if(@docount=1)
select count(id) from users where
nickname like '%'+@nickname+'%' and body like '%'+@body+'%'
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select id from users order by regtime desc
select O.* from users O,@indextable t where O.id=t.nid
and O.nickname like '%'+@nickname+'%' //去掉查询后无任何问题,不知道此句哪里有问题?
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
set nocount off
各位帮帮忙啊!谢了!
ALTER procedure [dbo].[GetUsersBySearch]
(@pagesize int,
@pageindex int,
@docount bit,
@nickname varchar(255)
)
as
set nocount on
if(@docount=1)
select count(id) from users where
nickname like '%'+@nickname+'%' and body like '%'+@body+'%'
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select id from users order by regtime desc
select O.* from users O,@indextable t where O.id=t.nid
and O.nickname like '%'+@nickname+'%' //去掉查询后无任何问题,不知道此句哪里有问题?
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
set nocount off
解决方案 »
- 是要使用缩略图还是用.ashx直接显示。。。讨论。求助。
- 静态页面的问题
- 请教,如何取得node在Treeview的第几层。(aps.net c#)
- 做ASP.NET生成dll文件的问题
- 左右两个框架,如何点击左边的连接会在右边的框架打开页面。
- 现在买asp.net的书有点犹豫,因为2.0出来了
- 使用datagrid显示数据库中的数据,却显示不出来!
- 高手求解
- store.exe inetinfo.exe 线程狂吃内存。 为什么???/
- 我想限制表格的宽度,但是如果我在<td></td>中输入的文字过长,却自动将表格撑长了,怎么办?????
- GAC的问题
- 点击邮件中的一个链接,要在本窗口中打开该链接,如何实现啊?
beginif @OrderType != 0begin set @strTmp = "<(select min"set @strOrder = " order by [" + @fldName +"] desc"--如果@OrderType不是0,就执行降序,这句很重要!endelsebegin set @strTmp = ">(select max" set @strOrder = " order by [" + @fldName +"] asc"end if @PageIndex = 1begin if @strWhere != '' set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder else set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName + "] "+ @strOrder--如果是第一页就执行以上代码,这样会加快执行速度endelsebegin--以下代码赋予了@strSQL以真正执行的SQL代码set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder if @strWhere != '' set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName + "] where [" + @fldName + "]" + @strTmp + "([" + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " [" + @fldName + "] from [" + @tblName + "] where " + @strWhere + " " + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrderend end exec (@strSQL)
GO
select O.* from users O,@indextable t where O.id=t.nid
and O.nickname like '%'+@nickname+'%'
改成
select O.* from users O left join @indextable t on O.id=t.nid
where O.nickname like '%'+@nickname+'%'