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

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. — Hanlet Escaño, Apr 12 '13 at 21:39
Wow, that was easy. I just had to set it before the databind(). Thanks! — Kevin, Apr 12 '13 at 21:41
Oh what's the difference between OnPageIndexChanging and OnPageIndexChanged? — Kevin, Apr 12 '13 at 21:47
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. http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.pageindexchanging.aspx and http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.pageindexchanged.aspx — Hanlet Escaño, Apr 12 '13 at 21:49

Hanlet Escaño · Accepted Answer · 2013-04-12T21:55:53.480

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();
}

edited Apr 12 '13 at 21:55

answered Apr 12 '13 at 21:46

Hanlet Escaño

17,114
8
52
75

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 – Kevin Apr 12 '13 at 21:54
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 – Hanlet Escaño Apr 12 '13 at 21:55
Ah ok great thanks! Out of curiosity, what does this do? Because it seems to work without the !IsPostBack in the pageload? – Kevin Apr 12 '13 at 21:58
1

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` – Praveen Nambiar Apr 12 '13 at 22:05

score 3 · Answer 2 · answered Apr 12 '13 at 21:48

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(); 
}

answered Apr 12 '13 at 21:48

Praveen Nambiar

4,852
1
22
31

Using SqlDataAdapter to page a SqlDataReader source

2 Answers2