User-1355475649 posted
Hello. I`m making board with ASP.NET GridView.
I made stored procedure about showing to GridViwe(contents of board) and Custom paging to Repeater.
And I tried to show text search result in grid view with custom paging, It works, but quite different what I want.
The first page of the result page, it's okay, but when I clicked page button,
all page is showing in GridView...I don`t know how to fix it
Here is my Stored prodecure(MSSQL) and the Custom Paging example link(I made my custom paging like that)
Please somebody help me.
Custom Paging in ASP.NET GridView <- This is Link
Stored Prodecure
ALTER PROCEDURE [dbo].[TestCustomPagingAfterSearch]
@BoardID int --FK(PK in tb_BoardMaster, I have 3 Boards)
, @PageIndex int
, @PageSize int
, @RecordCount int output
, @SearchText nvarchar(50)
AS
BEGIN
SET @SearchText = '%' + @SearchText + '%'
;with cte as (
SELECT
ROW_NUMBER() OVER (ORDER BY I.BoardNotice desc, I.BoardItemID desc) AS indexNO
, I.BoardItemID -- PK
, I.BoardTitle
, I.UserName
, I.BoardRegDate
, I.BoardHit
, I.BoardNotice -- To verify notice or not(default value is N(not notice))
, (SELECT COUNT(FileID) FROM tb_AttachedFiles A WHERE A.BoardItemID = I.BoardItemID) AS FileCount
FROM tb_BoardItem I
WHERE BoardID = @BoardID
AND
(BoardTitle like @SearchText
OR
UserName like @SearchText
OR
BoardContents like @SearchText)
)
SELECT * FROM cte
WHERE
indexNO BETWEEN (@PageIndex -1) * @PageSize +1 AND (((@PageIndex -1) * @PageSize +1) + @PageSize) -1
--ORDER BY
--BoardNotice DESC
SET @RecordCount = (
SELECT COUNT(*) FROM tb_BoardItem
WHERE BoardID=@BoardID
AND
(BoardTitle like @SearchText
OR
UserName like @SearchText
OR
BoardContents like @SearchText))
END
Code Behind
protected void searchBtn_Click(object sender, ImageClickEventArgs e)
{
string searchText = txtSearch.Text;
searchText = searchText.Trim();
int boardID = int.Parse(trvBoardMenu.SelectedValue);
int pageIndex = 1;
SqlConnection conn = DBConn.Conn();
SqlCommand cmd = new SqlCommand("TestCustomPagingAfterSearch", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@BoardID", boardID);
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.AddWithValue("@SearchText", searchText);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
try
{
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
grvList.DataSource = dr;
grvList.DataBind();
}
catch (Exception ex)
{
ExceptionLogging.SendExcepToDB(ex);
}
finally
{
conn.Close();
}
int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
this.PopulatePager(recordCount, pageIndex);
}
private void PopulatePager(int recordCount, int currentPage)
{
List<ListItem> pages = new List<ListItem>();
int startIndex, endIndex;
int pagerSpan = 5;
//Calculate Start, End
double dblPageCount = (double)((decimal)recordCount / Convert.ToDecimal(PageSize));
int pageCount = (int)Math.Ceiling(dblPageCount);
startIndex = currentPage > 1 && currentPage + pagerSpan - 1 < pagerSpan ? currentPage : 1;
endIndex = pageCount > pagerSpan ? pagerSpan : pageCount;
if(currentPage>pagerSpan%2)
{
if(currentPage==2)
{
endIndex = 5;
}
else
{
endIndex = currentPage + 2;
}
}
else
{
endIndex = (pagerSpan - currentPage) + 1;
}
if(endIndex-(pagerSpan-1)>startIndex)
{
startIndex = endIndex - (pagerSpan - 1);
}
if(endIndex>pageCount)
{
endIndex = pageCount;
startIndex = ((endIndex - pagerSpan) + 1) > 0 ? (endIndex - pagerSpan) + 1 : 1;
}
// Add the <<(First Button)
if(currentPage>1)
{
pages.Add(new ListItem("<<", "1"));
}
//Add the < (Previous button)
if(currentPage>1)
{
pages.Add(new ListItem("<", (currentPage - 1).ToString()));
}
for (int i = startIndex; i <= endIndex; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
//Add the > (Next button)
if (currentPage < pageCount)
{
pages.Add(new ListItem(">", (currentPage + 1).ToString()));
}
//Add the >> (Last button)
if (currentPage != pageCount)
{
pages.Add(new ListItem(">>", pageCount.ToString()));
}
rptPager.DataSource = pages;
rptPager.DataBind();
}
protected void Page_Changed(object sender, EventArgs e)
{
int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
this.GetCustomPaging(pageIndex);
}
