gridview asp.net在sp sql server 2008中进行pagging和排序 [英] gridview asp.net pagging and sort in sp sql server 2008
本文介绍了gridview asp.net在sp sql server 2008中进行pagging和排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
事件1非常好:找到所有记录而不搜索(66条记录5 pagecount = 5 pagesize = 15)
事件2:错误:查找字段中包含的所有记录nombreunit lether M(27条记录,pagecont = 2 pagesize = 1)
程序sqlserver显示行两页7行屏幕
这个错误:显示两个页面,一个有15个,一个有12个记录
错误当存储过程发生时,会显示两个页面,每个页面有6条记录
显示两页,一个有15个,一个有12个记录
创建 PROCEDURE [dbo]。[SearchUnidades]
(
@ Codigo varchar ( 6 ),
@ N ombre varchar ( 50 ),
@PageIndex int ,
@ PageSize int ,
@ swQuery bit ,
@ RowsTotal int 输出
)
AS
BEGIN
DECLARE @ query NVARCHAR ( 1000 )= ' '
DECLARE @ PageFirst int
DECLARE @ PageLast int
SET @ PageFirst =
( @PageIndex * @ PageSize )+ 1 ; SET @ PageLast =( @ PageIndex * @ PageSize )+ @ PageSize
SET @ query = ' SELECT * FROM(SELECT ROW_NUMBER()OVER(ORDER BY codigo_undmedida)AS RowNumber,
codigo_undmedida,descripcion_undmedida FROM unidadmedida)AS TBL' +
' WHERE RowNumber BETWEEN' + CONVERT ( varchar ( 10 ), @ PageFirst )+ ' AND' +
CONVERT ( varchar ( 10 ), @ PageLast )
IF ( @ Codigo != ' ' 和 @ swQuery = 1 ) SET @ query = @ query + ' AND codigo_undmedida LIKE''' + @ Codigo + ' %'''
IF ( @ Nombre != ' ' 和 @ swQuery = 1 ) SET @query = @ query + ' AND descripcion_undmedida LIKE''%' + @ Nombre + ' %'''
EXEC ( @ query )
SELECT @ RowsTotal = COUNT(codigo_undmedida) FROM unidadmedida WHERE descripcion_undmedida LIKE ' %' + @ Nombre + ' %'
RETURN
END
解决方案
请更改存储过程中的以下行:
SET @ PageFirst =( @ PageIndex * @PageSize )+ 1
SET @ PageLast =( @ PageIndex * @ PageSize )+ @ PageSize
到
SET @ PageFirst =(( @ PageIndex - 1 )* @ PageSize)+ 1
SET @ PageLast = @ PageIndex * @ PageSize
希望我理解你的问题是正确的方式。
i have a problem. the error a search by column name nombreUnit
event 1 very good: find all record whithout search (66 records 5 pagecount=5 pagesize = 15 )
event 2: error : find all record contain in field nombreunit lether M (27 records, pagecont=2 pagesize=1)
the procedure sqlserver show the rows in two pages and 7 rows by screen
this error:to display two pages, one with 15 and one with 12 records
the error happens when
the stored procedure, then display two pages with 6 records each
to display two pages, one with 15 and one with 12 records
CREATE PROCEDURE [dbo].[SearchUnidades]
(
@Codigo varchar(6),
@Nombre varchar(50),
@PageIndex int,
@PageSize int,
@swQuery bit,
@RowsTotal int output
)
AS
BEGIN
DECLARE @query NVARCHAR(1000) =''
DECLARE @PageFirst int
DECLARE @PageLast int
SET @PageFirst =
(@PageIndex * @PageSize) + 1 ; SET @PageLast = (@PageIndex * @PageSize) + @PageSize
SET @query = ' SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY codigo_undmedida) AS RowNumber,
codigo_undmedida, descripcion_undmedida FROM unidadmedida ) AS TBL' +
' WHERE RowNumber BETWEEN ' + CONVERT(varchar(10),@PageFirst) + ' AND ' +
CONVERT(varchar(10),@PageLast)
IF (@Codigo != '' and @swQuery = 1 ) SET @query = @query + ' AND codigo_undmedida LIKE ''' + @Codigo + '%'''
IF (@Nombre != '' and @swQuery = 1) SET @query = @query + ' AND descripcion_undmedida LIKE ''%' + @Nombre + '%'''
EXEC (@query)
SELECT @RowsTotal = COUNT(codigo_undmedida) FROM unidadmedida WHERE descripcion_undmedida LIKE '%' + @Nombre + '%'
RETURN
END
解决方案
Kindly change the following lines in your stored procedure from
SET @PageFirst = (@PageIndex * @PageSize) + 1 SET @PageLast = (@PageIndex * @PageSize) + @PageSize
To
SET @PageFirst = ((@PageIndex - 1)*@PageSize) + 1 SET @PageLast = @PageIndex * @PageSize
Hope I have understood your problem correct manner.
这篇关于gridview asp.net在sp sql server 2008中进行pagging和排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文