[Solved] Paging not working properly after filtering ASP.Net GridView

Last Reply 11 months ago By pandeyism

Posted 11 months ago

Hello Sir,

Im trying to apply pagination in gridview, but when click on 2nd or 3rd page its showing no record found.

below is the code which im using

Please help 

Thanks

        DateTime from_date = Convert.ToDateTime(txt_fromdate.Text);
        DateTime to_date = Convert.ToDateTime(txt_todate.Text);
        SqlCommand cmd1 = new SqlCommand("select * from table where name= '" + DropDownList1.SelectedItem.Text + "' and date BETWEEN @fromDate AND @ToDate   ORDER BY date asc", con);
            cmd1.Parameters.AddWithValue("@fromDate", from_date);
            cmd1.Parameters.AddWithValue("@ToDate", to_date);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd1))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }

 

    protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        BindGrid();
    }
    public void BindGrid()
    {
        string staff_name = DropDownList1.SelectedItem.Text;
        DateTime from_date = Convert.ToDateTime(txt_fromdate.Text);
        DateTime to_date = Convert.ToDateTime(txt_todate.Text);
        DataTable dt = new DataTable();
        dt = obj_select.select_all_staff_alignment(staff_name,from_date,to_date);
        GridView1.DataSource = dt; // give data to GridView
        GridView1.DataBind();
        con.Close();
    }  

 

Posted 11 months ago

Hello Sir,

 

I Got the solution. i have the changed the sql query in stored procedure.

Thanks


Posted 11 months ago

Hi democloud,

Refer below sample.

HTML

<asp:DropDownList runat="server" ID="DropDownList1" AutoPostBack="true" OnSelectedIndexChanged="Change">
    <asp:ListItem>Select</asp:ListItem>
    <asp:ListItem>USA</asp:ListItem>
    <asp:ListItem>Austria</asp:ListItem>
    <asp:ListItem>Brazil</asp:ListItem>
</asp:DropDownList>
<br />
<br />
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" AllowPaging="true"
    PageSize="5" OnPageIndexChanging="gvCustomers_onpageindexchanging">
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="CustomerId" />
        <asp:BoundField DataField="CompanyName" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", con))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    gvCustomers.DataSource = dt;
                    gvCustomers.DataBind();
                }
            }
        }
    }
}

protected void gvCustomers_onpageindexchanging(object sender, GridViewPageEventArgs e)
{
    gvCustomers.PageIndex = e.NewPageIndex;
    BindGrid();
}

protected void Change(object sender, EventArgs e)
{
    BindGrid();
}

public void BindGrid()
{
    string country = DropDownList1.SelectedItem.Text;
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers WHERE Country = @country", con))
        {
            cmd.Parameters.AddWithValue("@country", country);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                gvCustomers.DataSource = dt;
                gvCustomers.DataBind();
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr)
            Using cmd As SqlCommand = New SqlCommand("SELECT * FROM Customers", con)
                Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable()
                    da.Fill(dt)
                    gvCustomers.DataSource = dt
                    gvCustomers.DataBind()
                End Using
            End Using
        End Using
    End If
End Sub

Protected Sub gvCustomers_onpageindexchanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    gvCustomers.PageIndex = e.NewPageIndex
    BindGrid()
End Sub

Protected Sub Change(ByVal sender As Object, ByVal e As EventArgs)
    BindGrid()
End Sub

Public Sub BindGrid()
    Dim country As String = DropDownList1.SelectedItem.Text
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT * FROM Customers WHERE Country = @country", con)
            cmd.Parameters.AddWithValue("@country", country)
            Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                da.Fill(dt)
                gvCustomers.DataSource = dt
                gvCustomers.DataBind()
            End Using
        End Using
    End Using
End Sub

Screenshot