Implement custom Paging in DataList using C# and VB.Net in ASP.Net

Last Reply 2 months ago By Andrea

Posted 2 months ago

Custom paging Procedure Not Displaying Data

SQL Proc

ALTER proc [dbo].[newSP]
    @PageIndex INT =null,
    @PageSize INT = null,
    @RecordCount INT OUTPUT,
    @SchoolMasterID bigint =null
 As
 begin
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY e.PostId Desc
      )AS RowNumber
      ,e.PostId,FORMAT(e.CreateDateTime,'hh:mm tt ddd')as CreateDateTime,e.[PostContent],e.UserId,f.StudentImage,f.StudentName,g.SchoolName  INTO #Results
      FROM 
       tblPost e 
       left join tblPostImage d on e.PostId=d.PostId
       inner join tblUser f on e.UserId=f.UserId 
       inner join tblSchoolMaster g on g.SchoolMasterID=e.SchoolMasterID
       where e.ActiveStatus=1 and e.SchoolMasterID=@SchoolMasterID
 

	SELECT @RecordCount = COUNT(*)
        FROM #Results
	WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
     
      DROP TABLE #Results;	  
 End

 

SqlCommand cmd = new SqlCommand("newSP", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SchoolMasterID", Request.QueryString["id"].ToString());
cmd.Parameters.AddWithValue("@PageIndex",1);
cmd.Parameters.AddWithValue("@PageSize",10);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);

 

Posted 2 months ago
Hi @IamAzhar,
Please try the following

Implement custom Paging in DataList without Stored procedure using C# and VB.Net in ASP.Net in ASP.Net

It might help you.

Cheers Andrea.