Link to home
Start Free TrialLog in
Avatar of Koen Van Wielink
Koen Van WielinkFlag for Netherlands

asked on

Gridview paging not loading second page

Very new to C# ASP.Net here, and trying to display a gridview populated with database data. The dataset is large, and I want to add paging to the view. Unfortunately when I follow instructions I found online, only the initial page loads. When I click on the second page, the gridview disappears.
Here's the page source:
<%@ Page Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Home.aspx.cs" Inherits="Araya_Web.Home" %>

<asp:Content ID="Content1" runat="server" contentplaceholderid="MainContent">
    <br />
    <asp:GridView ID="gridProjects" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White" Width="100%" AllowPaging="true" AllowSorting="true"
        PageSize="25" runat="server" AutoGenerateColumns="true" OnPageIndexChanging="gridProjects_PageIndexChanging" ShowHeaderWhenEmpty="true">

<HeaderStyle BackColor="#3AC0F2" ForeColor="White"></HeaderStyle>
        <PagerSettings FirstPageText="First" LastPageText="Last" Mode="NumericFirstLast" />

</asp:GridView>
    <br />
</asp:Content>

Open in new window


and here's the code-behind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Web.Configuration;

namespace Araya_Web
{
    public partial class Home : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                string strQuery = "Select p.project_id as 'ID', p.project_name as 'Name', u.full_name as 'Created by', p.create_date as 'Created on' " +
                    "From PCR_Projects p inner join UserList u on p.created_by = u.user_id order by p.project_id";
                SqlCommand cmd = new SqlCommand(strQuery);
                DataTable projects = getData(cmd);
                gridProjects.DataSource = projects;
                gridProjects.DataBind();
            }

        }

        protected DataTable getData(SqlCommand cmd)
        {
            using (SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["EWSDB"].ConnectionString))
            {

                // Create data table to hold project info
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;

                // Connect to database and fetch all project ID's and names.
                try
                {
                    con.Open();
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                    return dt;
                }
                catch (Exception ex)
                {
                    string msg = "Fetch Error:";
                    msg += ex.Message;
                    throw new Exception(msg);
                }
                finally
                {
                    con.Close();
                    sda.Dispose();
                    con.Dispose();
                }
            }
        }
        protected void gridProjects_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gridProjects.PageIndex = e.NewPageIndex;
            gridProjects.DataBind();
        }
    }
}

Open in new window


Can anyone tell me why my gridview isn't just showing the second page? Do I need to do something with the ViewState? I'd prefer not to add custom paging, as I plan to add filters to the table next which will complicate the SQL if custom paging is required. I'm using Visual Studio 2012, ASP.Net 4.5.
Avatar of Lokesh B R
Lokesh B R
Flag of India image

Hi,

Change the code as below and try

  protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                getData();
            }

        }

        protected DataTable getData()
        {
            string strQuery = "Select p.project_id as 'ID', p.project_name as 'Name', u.full_name as 'Created by', p.create_date as 'Created on' " +
                    "From PCR_Projects p inner join UserList u on p.created_by = u.user_id order by p.project_id";
            SqlCommand cmd = new SqlCommand(strQuery);

            using (SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["EWSDB"].ConnectionString))
            {

                // Create data table to hold project info
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;

                // Connect to database and fetch all project ID's and names.
                try
                {
                    con.Open();
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);

                    gridProjects.DataSource = dt;
                    gridProjects.DataBind();
                }
                catch (Exception ex)
                {
                    string msg = "Fetch Error:";
                    msg += ex.Message;
                    throw new Exception(msg);
                }
                finally
                {
                    con.Close();
                    sda.Dispose();
                    con.Dispose();
                }
            }
        }
        protected void gridProjects_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gridProjects.PageIndex = e.NewPageIndex;
            getData();
        }

Open in new window

Avatar of Koen Van Wielink

ASKER

Hi Lokesh,

I see the point of your changes, but is it not possible to do set the SQL string and do the databinding to the gridview outside the Getdata() method? I like the idea of having a single method where I can just feed in a query and it returns me the datatable I need.
ASKER CERTIFIED SOLUTION
Avatar of Lokesh B R
Lokesh B R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect! Exactly what I wanted out of it.
So if I use this method I have to reload the datatable on each page change?
Yes,

It will call the method and bind the data to gridview.