Using SqlDataAdapter to page a SqlDataReader source

Question

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

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

Answer 1

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

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 click – Overnice

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 :P – Singular

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 postback – Efficient

Answer 2

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