Using SqlDataAdapter to page a SqlDataReader source
Asked Answered
O

2

5

This question seems to be common and I went through this answer already.

Unfortunately, my page still isn't being paged. Here's what my code looks like in C#:

 SqlCommand command = new SqlCommand("(SELECT ......", Connection);
 SqlDataAdapter myAdapter = new SqlDataAdapter(command);
 DataTable dt = new DataTable();
 myAdapter.Fill(dt);

 command.Connection = connection;
 command.Connection.Open();

 GridView1.DataSource = dt;
 GridView1.DataBind();
 GridView1.AllowPaging = true;
 GridView1.PageSize = 15;

 command.Connection.Close();
 command.Connection.Dispose();

Unfortunately, when I do this, my paging doesn't show up. Am I doing something wrong?

Thanks

Overnice answered 12/4, 2013 at 21:33 Comment(4)
Are you getting over 15 records with your select for sure? Also, set all of the Paging-related properties BEFORE the Databind() method is called.Singular
Wow, that was easy. I just had to set it before the databind(). Thanks!Overnice
Oh what's the difference between OnPageIndexChanging and OnPageIndexChanged?Overnice
PageIndexChanging occurs when one of the pager buttons is clicked, but before the GridView control handles the paging operation, while PageIndexChanged occurs when one of the pager buttons is clicked, but after the GridView control handles the paging operation. msdn.microsoft.com/en-us/library/… and msdn.microsoft.com/en-us/library/…Singular
S
6

Set all of the Paging-related properties before the Databind() method is called. When you use Custom Paging you will have to handle the GridView1_PageIndexChanging event. You need to change the current PageIndex, and re-bind your GridView like this:

void bindGridview()
{
    SqlCommand command = new SqlCommand("(SELECT ......", Connection);
    SqlDataAdapter myAdapter = new SqlDataAdapter(command);
    DataTable dt = new DataTable();
    myAdapter.Fill(dt);

    command.Connection = connection;
    command.Connection.Open();
    GridView1.AllowPaging = true;
    GridView1.PageSize = 15;
    GridView1.DataSource = dt;
    GridView1.DataBind();


    command.Connection.Close();
    command.Connection.Dispose();
}

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    bindGridview();
}

If you are also binding the GridView on Page_Load, do it like this:

protected void Page_Load(object sender, EventArgs e)
{
    if(!IsPostBack)
        bindGridview();
}
Shf answered 12/4, 2013 at 21:46 Comment(4)
My first databind() was on pageLoad. However, when I add the if(!IsPostBack) into my GridView1_PageIndexChanging stuff, when I click to go to the next page of the gridview, nothing happens the first time. When I click again, then it goes to the next page. Without the if statement, it does so on the first clickOvernice
Don't add the !IsPostBack into the PageIndexChanging, just in the Page_Load as I say in the answer :) I will update it with the event to avoid confusion :PSingular
Ah ok great thanks! Out of curiosity, what does this do? Because it seems to work without the !IsPostBack in the pageload?Overnice
i doubt if the indexing will work without the !IsPostback...remove it and try it out...setting it will ensure your GridView loads only once during pageload and not all on postbacks(eg. any click event is a postback)..so when you click on the Page Numbers..its a postbackEfficient
E
3

You need to add the PageIndexChanging event of GridView to enable paging.

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    bindGridview(); 
}
Efficient answered 12/4, 2013 at 21:48 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.