Searched Datagrid with Paging?

T

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
 
B

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?
 
T

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.DataGridPageChangedEventArgs 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,50);
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();
}
 
J

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
http://geekswithblogs.net/jbentley
 
T

Travis

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

Travis
 
J

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
http://geekswithblogs.net/jbentley
 
T

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
 
J

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
http://geekswithblogs.net/jbentley
 
T

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.
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,995
Messages
2,570,230
Members
46,818
Latest member
Brigette36

Latest Threads

Top