asked on

Keeping track of values in datatable while paging gridview

I have a page and when it loads, a gridview is populated with 1000 records. I have two text boxes which searches first name and last name.
Anytime i type in "be" for the lastname to search, I only see a page of data. If i click on page 2 i start seeing other data not related to "lastname = be" . I thought the value of "be" is not in view state and i put it in view state but still getting the same problem. Page 1 works well but other pages do not. Pls assist.

/// <summary>
    /// 
    /// </summary>
    private string GridViewSortDirection
    {
        get { return ViewState["SortDirection"] as string ?? "ASC"; }
        set { ViewState["SortDirection"] = value; }
    }
 
 
    /// <summary>
    /// 
    /// </summary>
    private string GridViewSortExpression
    {
        get { return ViewState["SortExpression"] as string ?? string.Empty; }
        set { ViewState["SortExpression"] = value; }
    }
 
 
    /// <summary>
    /// 
    /// </summary>
    /// <returns></returns>
    private string GetSortDirection()
    {
        try
        {
            switch (GridViewSortDirection)
            {
                case "ASC":
                    GridViewSortDirection = "DESC";
                    break;
                case "DESC":
                    GridViewSortDirection = "ASC";
                    break;
            }      
        }        
        catch(Exception ex)
        {
            Response.Redirect("Error.aspx"); 
        }
        return GridViewSortDirection;
    }
 
    /// <summary>
    /// 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void sdgridview_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            sdgridview.DataSource = SortDataTable(sdgridview.DataSource as DataTable, true);
            sdgridview.PageIndex = e.NewPageIndex;
            //dots
            sdgridview.DataBind();
            
        }
        catch (Exception ex)
        {
            Response.Redirect("Error.aspx"); 
        }
    }
        
    /// <summary>
    /// 
    /// </summary>
    /// <param name="dataTable"></param>
    /// <param name="isPageIndexChanging"></param>
    /// <returns></returns>
    protected DataView SortDataTable(DataTable dataTable, bool isPageIndexChanging)
    {
        try
        {
            if (dataTable != null)
            {
                DataView dataView = new DataView(dataTable);
                if (GridViewSortExpression != string.Empty)
                {
                    if (isPageIndexChanging)
                    {
                        dataView.Sort = string.Format("{0} {1}", GridViewSortExpression, GridViewSortDirection);
                    }
                    else
                    {
                        dataView.Sort = string.Format("{0} {1}", GridViewSortExpression, GetSortDirection());
                    }
                }
                return dataView;
            }
            //else
            //{
                //return new DataView();
            //}
        }
        catch (Exception ex)
        {
            Response.Redirect("Error.aspx"); 
        }
        return new DataView();
    }
 
    /// <summary>
    /// 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void sdgridview_Sorting(object sender, GridViewSortEventArgs e)
    {
        try
        {
            GridViewSortExpression = e.SortExpression;
            int pageIndex = sdgridview.PageIndex;
            sdgridview.DataSource = SortDataTable(sdgridview.DataSource as DataTable, false);
            //dots here
            sdgridview.DataBind();
            sdgridview.PageIndex = pageIndex;
        }
        catch (Exception ex)
        {
            Response.Redirect("Error.aspx"); 
        }
    }
 
 
 
protected void btnsearch_Click(object sender, EventArgs e)
    {
        try
        {
            if (txtfirstname.Text == "" & txtlastname.Text == "")
            {
                lblresult.Visible = true;
                lblresult.Text = "You Cannot search for blank values. Enter a value for last name or first name";
                sdgridview.Visible = false;
            }
            else
            {
                lblresult.Visible = false;
                sdgridview.Visible = true;
                PopulateGridByCriteria();
            }
        }
        catch (Exception ex)
        {
            Response.Redirect("Error.aspx"); 
        }
    }
 
 
private void PopulateGridByCriteria()
    {
        try
        {
            string connectionString = AccessConnectionString();
            cn = new SqlConnection(connectionString);
            da = new SqlDataAdapter("DisplaystudentByCriteria", cn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.Add(new SqlParameter("@surname",SqlDbType.VarChar,30));
            //da.SelectCommand.Parameters["@surname"].Value = txtlastname.Text;
            CurrentLastNameSearch = txtlastname.Text;
            da.SelectCommand.Parameters["@surname"].Value = CurrentLastNameSearch;
 
            da.SelectCommand.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.VarChar, 30));
            da.SelectCommand.Parameters["@Firstname"].Value = txtfirstname.Text;
            studDT = new DataTable();
            da.Fill(studDT);
            int dataTableRowCountByCriteria = studDT.Rows.Count;
            if (dataTableRowCountByCriteria > 0)
            {
                sdgridview.Visible = true;
                //lblresult.Visible = false;
                sdgridview.DataSource = studDT;
                sdgridview.DataBind();
                sdgridview.Columns[0].Visible = false;
            }
            else
            {
                lblresult.Visible = true;
                sdgridview.Visible = false;
                lblresult.Text = "No record found";
            }
        }
        catch(Exception ex)
        {
            Response.Redirect("Error.aspx"); 
        }
    }
 
 
 
public string CurrentLastNameSearch
    {
        get
        {
            return (string)ViewState["Current"];
        }
        set
        {
            ViewState["Current"] = value;
        }
 
    }

Open in new window

scgstuff

Try:

protected void sdgridview_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
try
{
PopulateGridByCriteria();
sdgridview.PageIndex = e.NewPageIndex;
//dots
sdgridview.DataBind();

}
catch (Exception ex)
{
Response.Redirect("Error.aspx");
}
}

Sirdots

ASKER

Thanks for your assistance scqstuff. Your code works but has broken my initial data that i load on page load. Presently, once my page loads, it is only the first page that is sorted in ascending order. once i click on the second page, no more ascending order. I will start seeing last name starting with V

protected void Page_Load(object sender, EventArgs e)
{
try
{
//if (!this.IsPostBack)
//{
// PopulateStudentDirecGrid();
//}

PopulateStudentDirecGrid();

}
catch(Exception ex)
{
Response.Redirect("Error.aspx");
}
}

private void PopulateStudentDirecGrid()
{
try
{

//DataSet ds = new DataSet();
string connectionString = AccessConnectionString();
sdcmd = new SqlCommand();
sdcmd.CommandText = "DisplayAllStudentDirectory";
cn = new SqlConnection(connectionString);
cn.Open();
sdcmd.Connection = cn;
sdcmd.CommandType = CommandType.StoredProcedure;
da = new SqlDataAdapter(sdcmd);

DataTable studentDT = new DataTable("StudentD");
da.Fill(studentDT);
int dataTableRowCount = studentDT.Rows.Count;

if (dataTableRowCount > 0)
{
sdgridview.Visible = true;
lblresult.Visible = false;
sdgridview.DataSource = studentDT;
sdgridview.DataBind();
sdgridview.Columns[0].Visible = false;
}
}
catch(Exception ex)
{
//Session["Exception"] = ex;
Response.Redirect("Error.aspx");
}

}

scgstuff

You may want to join the PopulateStudentDataGrid and PopulateGridByCriteria into one sub and then do an if txtLastname not null then commandtext = DisplayStudentByCriteria else DisplayAllStudentDirectory. I think that would allow you to add the data needed to the grid and select an index page before databinding.

Shawn

Sirdots

ASKER

Thanks for your comment. The reason why i seperated them is that I have a button link on the page which calls the PopulateStudentGrid which displays all record and not specific record. do you know how I can incorporate this within the same method?

ASKER CERTIFIED SOLUTION

scgstuff

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer