gridview asp.net pagging and sort in sp sql server 2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

SQL

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

Posted 5-Sep-14 19:30pm

Carlos j. Ramirez

Updated 5-Sep-14 20:27pm

Dilan Shaminda

v2

Add a Solution

Comments

Carlos j. Ramirez 6-Sep-14 19:43pm

I explain again.

there are 66 records in the table unidad_medida

pagesise = 15
if I search all records, running the stored procedure

private void LLenar_Grilla(int PageIndex, int PageSize, bool swQuery)
{

string connectionString = ConfigurationManager.ConnectionStrings["dbConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionString);

SqlCommand mycmd = new SqlCommand();
try
{
mycmd.Connection = conn;
mycmd.CommandText = "SearchUnidades";
mycmd.CommandType = CommandType.StoredProcedure;
mycmd.Parameters.Add("@Codigo", System.Data.SqlDbType.NVarChar, 6).Value = Convert.ToString(txtCodigo.Text);
mycmd.Parameters.Add("@Nombre", System.Data.SqlDbType.NVarChar, 50).Value = Convert.ToString(txtDescripcion.Text);
mycmd.Parameters.Add("@PageIndex", System.Data.SqlDbType.Int, 10).Value = Convert.ToInt16(PageIndex);
mycmd.Parameters.Add("@PageSize", System.Data.SqlDbType.Int, 10).Value = Convert.ToInt16(PageSize);
mycmd.Parameters.Add("@swQuery", System.Data.SqlDbType.Bit).Value = Convert.ToBoolean(swQuery);
mycmd.Parameters.Add("@RowsTotal", SqlDbType.Int, 10);
mycmd.Parameters["@RowsTotal"].Direction = ParameterDirection.Output;
//conn.Open();
//mycmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(mycmd);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.VirtualItemCount = Convert.ToInt16(mycmd.Parameters["@RowsTotal"].Value.ToString());
GridView1.DataBind();
TotalRowsLabel.Text = "Regitros : VirtualCount=" + GridView1.VirtualItemCount.ToString() + " PageCount=" +
GridView1.PageCount.ToString() + " PageIndex=" + GridView1.PageIndex.ToString() + " Pagesize=" + GridView1.PageSize.ToString();
}
finally
{
//transaccion.Rollback()
if (conn != null)
{
conn.Close();
}
}
}

labelText Result:
Regitros : VirtualCount=66 PageCount=5 PageIndex=1 Pagesize=15

to make a search through the countryside drive_name
containing the letter m
27 records found
shows me 2 pages and each with 7 records, the other does not show

page 1 should show 2 records with 15 and second with 12 records.

Carlos j. Ramirez 6-Sep-14 19:45pm

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
LLenar_Grilla(GridView1.PageIndex , GridView1.PageSize, false);
}
}

Carlos j. Ramirez 6-Sep-14 19:48pm

protected void OnFind(object sender, ImageClickEventArgs e)
{
GridView1.PageIndex = 0;
LLenar_Grilla(GridView1.PageIndex, GridView1.PageSize, true);
}

Carlos j. Ramirez 6-Sep-14 19:50pm

Records: VirtualCount=27 PageCount=2 PageIndex=0 Pagesize=15

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ChauhanAjay · Accepted Answer · 2014-09-05T20:30:00

Solution 1

Kindly change the following lines in your stored procedure from

SQL

SET @PageFirst = (@PageIndex * @PageSize) + 1 
SET @PageLast = (@PageIndex * @PageSize) + @PageSize

To

SQL

SET @PageFirst = ((@PageIndex - 1)*@PageSize) + 1
SET @PageLast = @PageIndex * @PageSize

Hope I have understood your problem correct manner.

Posted 5-Sep-14 20:30pm

ChauhanAjay

Comments

Carlos j. Ramirez 6-Sep-14 19:44pm

did not work

ChauhanAjay 6-Sep-14 23:41pm

Is the data not being display 15 records at a time instead of random records.

Carlos j. Ramirez 7-Sep-14 21:29pm

but only shows 14 records
must show 27 records