Searched Datagrid with Paging?

[Travis]

I am new to .NET, so bear with me. I have a datagrid with default
paging of 10. I can page through it fine to the end. When I do a
search from a stored procedure the first 10 rows show but when I click
on 'Next Page' it shows as page 2 from before the search. And when I
click 'Previous Page' it goes back to page 1 (which is what you get
when the page loads). Any ideas would be great.

Thanks,
Travis

[Brock Allen]

I can't quite follow what's wrong. So you mean when you introduce searching
the results from any other page than the first are as if you didn't apply
the search parameters? So, my question would be how is your DataGrid's OnPageIndexChange
event coded? It would need to fetch the next 10 records from the search results.
Perhaps I'm missing part of your question?

-Brock
[url]http://staff.develop.com/ballen[/url]

> I am new to .NET, so bear with me. I have a datagrid with default
> paging of 10. I can page through it fine to the end. When I do a
> search from a stored procedure the first 10 rows show but when I click
> on 'Next Page' it shows as page 2 from before the search. And when I
> click 'Previous Page' it goes back to page 1 (which is what you get
> when the page loads). Any ideas would be great.
>
> Thanks,
> Travis

[Travis]

I can page fine when the page loads. After I do a search let say my
stored procedure returns 200 results the first 10 show but when I click
on next page the it seems as if my search as been reset. It doesn't
let me see the 190 that are left to page through. Here is my code, if
that helps:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Globalization;
using System.Text;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace GlossaryCreation.Forms
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
protected System.Data.SqlClient.SqlConnection sqlConnection1;
protected GlossaryCreation.Forms.DataSet3 dataSet31;
protected System.Data.DataView dataView1;
protected System.Web.UI.WebControls.Button btnSearch;
protected System.Web.UI.WebControls.DropDownList dpdlSearch;
protected System.Web.UI.WebControls.TextBox txtSearch;
protected System.Web.UI.WebControls.Label lblSearch;
protected System.Web.UI.WebControls.Button btnAdd;
protected System.Web.UI.WebControls.DataGrid DataGrid1;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
DataView Source;

Source = (DataView)Cache["MyDataView"];

if(!Page.IsPostBack)
{
Page.RegisterHiddenField("__EVENTTARGET", "btnSearch");

SqlConnection conn = new SqlConnection("workstation id='';packet
size=4096;user id=id;data source=server;persi" +
"st security info=True;initial catalog=database;password=pass");
conn.Open();

// Create sqlCommand to select username and password from users
table
SqlCommand cmd = new SqlCommand("iDBGetHAMSSearchList",conn);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataReader dr = cmd.ExecuteReader();

if(dr.HasRows)
{
this.dpdlSearch.DataSource = dr;
this.dpdlSearch.DataTextField = "column_name";
this.dpdlSearch.DataValueField = "column_name";
this.dpdlSearch.DataBind();
}

// close dr connection
dr.Close();
this.bindSearchResults();
//this.sqlDataAdapter1.Fill(this.dataSet31);
//dataView1.Sort = "Serial #";
//this.DataGrid1.DataBind();
//Cache["MyDataView"] = dataView1;
}
}
private void bindSearchResults()
{
this.dataSet31.Clear();
this.DataGrid1.DataSource = this.dataSet31;
this.sqlDataAdapter1.Fill(this.dataSet31);
this.DataGrid1.DataBind();
}

private void dataView1_ListChanged(object sender,
System.ComponentModel.ListChangedEventArgs e)
{
}
private void DataGrid1_SortCommand(object source,
DataGridSortCommandEventArgs e)
{
dataView1.Sort = e.SortExpression;
DataGrid1.DataBind();
}
public void DataGrid1_PageChanger(object sender,
System.Web.UI.WebControls.DataGridPageChangedEvent Args e)
{
this.DataGrid1.CurrentPageIndex = e.NewPageIndex;
this.bindSearchResults();
}

private void btnSearch_Click(object sender, System.EventArgs e)
{
this.DataGrid1.CurrentPageIndex = 0;

DataView Source;

Source = (DataView)Cache["MyDataView"];
if(Page.IsPostBack)
{
// Create and open connection to database containing the usernames
and password
SqlConnection conn1 = new SqlConnection("workstation id='';packet
size=4096;user id=id;data source=server;persi" +
"st security info=True;initial catalog=database;password=pass");
conn1.Open();

// Create sqlCommand to select username and password from users
table
SqlCommand cmd = conn1.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "iDBHAMSSearchAsset";

cmd.Parameters.Add("@ciSearch",SqlDbType.VarChar,5 0);
cmd.Parameters["@ciSearch"].Value = txtSearch.Text;
cmd.Parameters.Add("@ciDropList",SqlDbType.VarChar ,15,"column_name");
cmd.Parameters["@ciDropList"].Value =
this.dpdlSearch.SelectedValue.ToString();

if (conn1.State == ConnectionState.Closed)
conn1.Open();

this.sqlDataAdapter1.SelectCommand = cmd;

sqlDataAdapter1.Fill(this.dataSet31);
this.DataGrid1.DataSource = this.dataSet31;
DataGrid1.DataBind();


//close sql conneciton
conn1.Close();
}

this.bindSearchResults();
}

[Jason Bentley]

Travis, you will have to devise a way to maintain the state of your
datasource. You could create the SqlCommand object and store it in the
viewstate or use some other mechanism. When the CurrentPageIndex is
changed, you can pull the SqlCommand from ViewState and load the data
back into the datagrid.

Jason Bentley
[url]http://geekswithblogs.net/jbentley[/url]

[Travis]

Ok, I guess my next question is, how exactly do I store the Sqlcommand
object in a viewstate?

Travis

[Jason Bentley]

Here is an example to add the SqlCommand to Session object. The
ViewState is still an option but you will have to serialize the object
first. I should have thought of that before I suggested it. Anyway,
here is enough to get you started.

To Add The SqlCommand object to the Session.
Session.Add("SearchCommand", cmd);

To Retrieve The SqlCommand object from the Session.
SqlCommand cmd = (SqlCommand)Session["SearchCommand"];

Jason Bentley
[url]http://geekswithblogs.net/jbentley[/url]

[Travis]

Jason-

I was able to add and retrieve the SqlCommand from Session. Thank you.
I have hopefully one last question, how can I page when the datagrid
first loads and if I do a search on the same datagrid? Can I do some
if statement in the pager to check for a search?

Travis

[Jason Bentley]

Travis, you CAN easily add the current page index to the ViewState and
retrieve it. When the page loads:

YourDataGrid.DataSource = ds;
if(ViewState["CurrentPageIndex"] != null)
{
YourDataGrid.PageIndex =
Convert.ToInt32(ViewState["CurrentPageIndex"].ToString());
}
YourDataGrid.DataBind();

To save the current page index to the ViewState:
ViewState.Add("CurrentPageIndex", YourDataGrid.CurrentPageIndex);


You could also add another variable to the viewstate when the Search
button is clicked. When you page loads, if the value of the variable is
null, you know there was no search.

Jason Bentley
[url]http://geekswithblogs.net/jbentley[/url]

[Travis]

Jason-

Sorry about not replying back right away. I was out sick yesterday.
Anyway, I got it to work. Thanks for your help. Now I have to make it
stop on the last page without giving the error: Invalid
CurrentPageIndex value. It must be >= 0 and < the PageCount.

[Jason Bentley]

Travis, you should reset the CurrentPageIndex to 0 every time a new
search is performed. The grid should not be displaying page numbers for
pages that do not exist.

Jason Bentley
[url]http://geekswithblogs.net/jbenley[/url]

[Travis]

Thanks Jason for all your help. Just wanted to get your thoughts on
one thing. What books do you recommend for learning .NET?

Travis

[Jason Bentley]

[url]http://www.amazon.com/exec/obidos/tg/detail/-/0735614229/qid=1111698294/sr=8-5/ref=pd_csp_5/103-7047272-6136636?v=glance&s=books&n=507846[/url]
[url]http://www.amazon.com/exec/obidos/tg/detail/-/1861007094/qid=1111698389/sr=8-5/ref=pd_csp_5/103-7047272-6136636?v=glance&s=books&n=507846[/url]
[url]http://www.amazon.com/exec/obidos/tg/detail/-/0596003021/qid=1111698389/sr=8-6/ref=pd_csp_6/103-7047272-6136636?v=glance&s=books&n=507846[/url]

Those three books should be on every one's bookshelves. Good luck.

Jason Bentley
[url]http://geekswithblogs.net/jbentley[/url]