sorting and paging issue

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

I have a webpage with a gridview on it that has 6 pages with 20 per page.
The issue I have is when it sorts and then try and go to another page the
sorting discontinues and the grid order goes back to the original order. I
am not getting both to work together and it is getting frustrating. I am
definitely considered a newbie at this, but I'm working to better the status.
Here is all the C# code I have concerning the gridview (no html, that is
fine).

protected void Page_Load(object sender, EventArgs e)
{
string connStr =
ConfigurationManager.ConnectionStrings["ProteusConnectionString"].ConnectionString;
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(connStr);

{
conn.Open();
string sql = "FillGridView";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
sqlDa.Fill(dt);

if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
//Load data and do nothing
}
}
{
conn.Close();
}
}

protected void GridView1_PageIndexChanging(object sender,
GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}

private string ConvertSortDirectionToSQL(SortDirection sortDirection)
{
string newSortDirection = string.Empty;
switch (sortDirection)
{
case SortDirection.Ascending:
newSortDirection = "ASC";
break;
case SortDirection.Descending:
newSortDirection = "DESC";
break;
}
return newSortDirection;
}

protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
DataTable dt = GridView1.DataSource as DataTable;

if (dt != null)
{
DataView dvw = new DataView(dt);

dvw.Sort = e.SortExpression + " " +
ConvertSortDirectionToSQL(e.SortDirection);
e.SortExpression = e.SortExpression.ToString();
GridView1.DataSource = dvw;
GridView1.DataBind();
}
}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs
e)
{
GridView1.EditIndex = e.NewEditIndex;
GridView1.DataBind();
}

protected void GridView1_RowCancelingEdit(object sender,
GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GridView1.DataBind();
}
protected void GridView1_SelectedIndexChanging(object sender,
GridViewSelectEventArgs e)
{
GridViewRow row = GridView1.Rows[e.NewSelectedIndex];
}

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
GridViewRow row = GridView1.SelectedRow;
}
}

Can anyone please review this and see what I am doing wrong or its right but
just not enough. Whatever the case, let me know.

Thanks... John
 
Without testing it...

i can see what you are trying to do, and i wouldent

You do not need to convert sortorder to sql, it has nothing to do with sql.
What you are doing is working only for that click beacuse the code you have
needs to run evey time if you are going to do it like that.

Load your data into a datatable, then when sorting you sort the datatables
default view and then databind to the grid.
you may need to take a few mintes to learn about datatables and datviews

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.sorting.aspx


JohnE said:
I have a webpage with a gridview on it that has 6 pages with 20 per page.
The issue I have is when it sorts and then try and go to another page the
sorting discontinues and the grid order goes back to the original order.
I
am not getting both to work together and it is getting frustrating. I am
definitely considered a newbie at this, but I'm working to better the
status.
Here is all the C# code I have concerning the gridview (no html, that is
fine).

protected void Page_Load(object sender, EventArgs e)
{
string connStr =
ConfigurationManager.ConnectionStrings["ProteusConnectionString"].ConnectionString;
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(connStr);

{
conn.Open();
string sql = "FillGridView";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
sqlDa.Fill(dt);

if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
//Load data and do nothing
}
}
{
conn.Close();
}
}

protected void GridView1_PageIndexChanging(object sender,
GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}

private string ConvertSortDirectionToSQL(SortDirection sortDirection)
{
string newSortDirection = string.Empty;
switch (sortDirection)
{
case SortDirection.Ascending:
newSortDirection = "ASC";
break;
case SortDirection.Descending:
newSortDirection = "DESC";
break;
}
return newSortDirection;
}

protected void GridView1_Sorting(object sender, GridViewSortEventArgs
e)
{
DataTable dt = GridView1.DataSource as DataTable;

if (dt != null)
{
DataView dvw = new DataView(dt);

dvw.Sort = e.SortExpression + " " +
ConvertSortDirectionToSQL(e.SortDirection);
e.SortExpression = e.SortExpression.ToString();
GridView1.DataSource = dvw;
GridView1.DataBind();
}
}

protected void GridView1_RowEditing(object sender,
GridViewEditEventArgs
e)
{
GridView1.EditIndex = e.NewEditIndex;
GridView1.DataBind();
}

protected void GridView1_RowCancelingEdit(object sender,
GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GridView1.DataBind();
}
protected void GridView1_SelectedIndexChanging(object sender,
GridViewSelectEventArgs e)
{
GridViewRow row = GridView1.Rows[e.NewSelectedIndex];
}

protected void GridView1_SelectedIndexChanged(object sender, EventArgs
e)
{
GridViewRow row = GridView1.SelectedRow;
}
}

Can anyone please review this and see what I am doing wrong or its right
but
just not enough. Whatever the case, let me know.

Thanks... John
Click to expand...
 
I reviewed the info at the link and was finally able to give it a try. It
keeps failing at this line in the Sorting part, which I have pasted below.
It tells me it can not find the column I am trying to sort on. But yet, when
I hover over the information in the line, it shows the correct info including
the column I'm trying to sort by.

dt.DefaultView.Sort = e.SortExpression + " " +
GetSortDirection(e.SortExpression);

One thing I am wondering about is the Session object. I might not be
listing the correct one. How can I verify the name of the session object
that is used to fill the gridview? I have the connection string info, is
that it? The original posting has the info in it. I have moved the
connection info out of the page load section.

Any further assistance is appreciated.

Thanks.
John



ThatsIT.net.au said:
Without testing it...

i can see what you are trying to do, and i wouldent

You do not need to convert sortorder to sql, it has nothing to do with sql.
What you are doing is working only for that click beacuse the code you have
needs to run evey time if you are going to do it like that.

Load your data into a datatable, then when sorting you sort the datatables
default view and then databind to the grid.
you may need to take a few mintes to learn about datatables and datviews

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.sorting.aspx


JohnE said:
I have a webpage with a gridview on it that has 6 pages with 20 per page.
The issue I have is when it sorts and then try and go to another page the
sorting discontinues and the grid order goes back to the original order.
I
am not getting both to work together and it is getting frustrating. I am
definitely considered a newbie at this, but I'm working to better the
status.
Here is all the C# code I have concerning the gridview (no html, that is
fine).

protected void Page_Load(object sender, EventArgs e)
{
string connStr =
ConfigurationManager.ConnectionStrings["ProteusConnectionString"].ConnectionString;
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(connStr);

{
conn.Open();
string sql = "FillGridView";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
sqlDa.Fill(dt);

if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
//Load data and do nothing
}
}
{
conn.Close();
}
}

protected void GridView1_PageIndexChanging(object sender,
GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}

private string ConvertSortDirectionToSQL(SortDirection sortDirection)
{
string newSortDirection = string.Empty;
switch (sortDirection)
{
case SortDirection.Ascending:
newSortDirection = "ASC";
break;
case SortDirection.Descending:
newSortDirection = "DESC";
break;
}
return newSortDirection;
}

protected void GridView1_Sorting(object sender, GridViewSortEventArgs
e)
{
DataTable dt = GridView1.DataSource as DataTable;

if (dt != null)
{
DataView dvw = new DataView(dt);

dvw.Sort = e.SortExpression + " " +
ConvertSortDirectionToSQL(e.SortDirection);
e.SortExpression = e.SortExpression.ToString();
GridView1.DataSource = dvw;
GridView1.DataBind();
}
}

protected void GridView1_RowEditing(object sender,
GridViewEditEventArgs
e)
{
GridView1.EditIndex = e.NewEditIndex;
GridView1.DataBind();
}

protected void GridView1_RowCancelingEdit(object sender,
GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GridView1.DataBind();
}
protected void GridView1_SelectedIndexChanging(object sender,
GridViewSelectEventArgs e)
{
GridViewRow row = GridView1.Rows[e.NewSelectedIndex];
}

protected void GridView1_SelectedIndexChanged(object sender, EventArgs
e)
{
GridViewRow row = GridView1.SelectedRow;
}
}

Can anyone please review this and see what I am doing wrong or its right
but
just not enough. Whatever the case, let me know.

Thanks... John
Click to expand...
Click to expand...
 
JohnE said:
I reviewed the info at the link and was finally able to give it a try. It
keeps failing at this line in the Sorting part, which I have pasted below.
It tells me it can not find the column I am trying to sort on. But yet,
when
I hover over the information in the line, it shows the correct info
including
the column I'm trying to sort by.
Click to expand...

check that the datatable has that column

dt.DefaultView.Sort = e.SortExpression + " " +
GetSortDirection(e.SortExpression);


One thing I am wondering about is the Session object. I might not be
listing the correct one. How can I verify the name of the session object
that is used to fill the gridview? I have the connection string info, is
that it? The original posting has the info in it. I have moved the
connection info out of the page load section.
Click to expand...


The datasouce is the default view.

in that example im not sure why it is using Session("TaskTable") when we
want the defaultview we just sorted
try

TaskGridView.DataSource = dt.DefaultView



Any further assistance is appreciated.

Thanks.
John



ThatsIT.net.au said:
Without testing it...

i can see what you are trying to do, and i wouldent

You do not need to convert sortorder to sql, it has nothing to do with
sql.
What you are doing is working only for that click beacuse the code you
have
needs to run evey time if you are going to do it like that.

Load your data into a datatable, then when sorting you sort the
datatables
default view and then databind to the grid.
you may need to take a few mintes to learn about datatables and datviews

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.sorting.aspx


JohnE said:
I have a webpage with a gridview on it that has 6 pages with 20 per
page.
The issue I have is when it sorts and then try and go to another page
the
sorting discontinues and the grid order goes back to the original
order.
I
am not getting both to work together and it is getting frustrating. I
am
definitely considered a newbie at this, but I'm working to better the
status.
Here is all the C# code I have concerning the gridview (no html, that
is
fine).

protected void Page_Load(object sender, EventArgs e)
{
string connStr =
ConfigurationManager.ConnectionStrings["ProteusConnectionString"].ConnectionString;
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(connStr);

{
conn.Open();
string sql = "FillGridView";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
sqlDa.Fill(dt);

if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
//Load data and do nothing
}
}
{
conn.Close();
}
}

protected void GridView1_PageIndexChanging(object sender,
GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}

private string ConvertSortDirectionToSQL(SortDirection
sortDirection)
{
string newSortDirection = string.Empty;
switch (sortDirection)
{
case SortDirection.Ascending:
newSortDirection = "ASC";
break;
case SortDirection.Descending:
newSortDirection = "DESC";
break;
}
return newSortDirection;
}

protected void GridView1_Sorting(object sender,
GridViewSortEventArgs
e)
{
DataTable dt = GridView1.DataSource as DataTable;

if (dt != null)
{
DataView dvw = new DataView(dt);

dvw.Sort = e.SortExpression + " " +
ConvertSortDirectionToSQL(e.SortDirection);
e.SortExpression = e.SortExpression.ToString();
GridView1.DataSource = dvw;
GridView1.DataBind();
}
}

protected void GridView1_RowEditing(object sender,
GridViewEditEventArgs
e)
{
GridView1.EditIndex = e.NewEditIndex;
GridView1.DataBind();
}

protected void GridView1_RowCancelingEdit(object sender,
GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GridView1.DataBind();
}
protected void GridView1_SelectedIndexChanging(object sender,
GridViewSelectEventArgs e)
{
GridViewRow row = GridView1.Rows[e.NewSelectedIndex];
}

protected void GridView1_SelectedIndexChanged(object sender,
EventArgs
e)
{
GridViewRow row = GridView1.SelectedRow;
}
}

Can anyone please review this and see what I am doing wrong or its
right
but
just not enough. Whatever the case, let me know.

Thanks... John
Click to expand...
Click to expand...
Click to expand...
 
There is a field in the table called Originator. I am providing the code so
you can see it all rather the just the line it fails on.

public void GridView1DataBind()
{
string connStr =
ConfigurationManager.ConnectionStrings["ProteusConnectionString"].ConnectionString;
string sql = "FillGridView";

SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(sql, conn);

conn.Open();

DataTable dt = new DataTable();
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);

sqlDa.Fill(dt);

GridView1.DataSource = dt;
GridView1.DataBind();

conn.Close();
}

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
DataTable taskTable = new DataTable("TaskList");

taskTable.Columns.Add("ID", typeof(int));
taskTable.Columns.Add("Descrip", typeof(string));

for (int i = 0; i < 21; i++)
{
DataRow tableRow = taskTable.NewRow();
tableRow["ID"] = i;
tableRow["Descrip"] = "Task " + (20 - i).ToString();
taskTable.Rows.Add(tableRow);
}
Session["TaskList"] = taskTable;

GridView1.DataSource = Session["TaskList"];
GridView1.DataBind();
}
GridView1DataBind();
}

protected void GridView1_PageIndexChanging(object sender,
GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}

private string GetSortDirection(string column)
{
string sortDirection = "ASC";
string sortExpression = ViewState["SortExpression"] as string;

if (sortExpression != null)
{
if (sortExpression == column)
{
string lastDirection = ViewState["SortDirection"] as string;

if ((lastDirection != null) && (lastDirection == "ASC"))
{
sortDirection = "DESC";
}
}
}
ViewState["SortDirection"] = sortDirection;
ViewState["SortExpression"] = column;

return sortDirection;
}

protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
DataTable dt = Session["TaskList"] as DataTable;

if (dt != null)
{
dt.DefaultView.Sort = e.SortExpression + " " +
GetSortDirection(e.SortExpression);
GridView1.DataSource = Session["TaskList"];
GridView1.DataBind();
}
}

One thing I did notice is that when stepping thru, the gridview loads like
it should. Then on the attempts to sort, it gets to the If in the page load
and goes straight to the fail line I mentioned.

Thanks for hangin' in there on this.

.... John




ThatsIT.net.au said:
JohnE said:
I reviewed the info at the link and was finally able to give it a try. It
keeps failing at this line in the Sorting part, which I have pasted below.
It tells me it can not find the column I am trying to sort on. But yet,
when
I hover over the information in the line, it shows the correct info
including
the column I'm trying to sort by.
Click to expand...

check that the datatable has that column

dt.DefaultView.Sort = e.SortExpression + " " +
GetSortDirection(e.SortExpression);


One thing I am wondering about is the Session object. I might not be
listing the correct one. How can I verify the name of the session object
that is used to fill the gridview? I have the connection string info, is
that it? The original posting has the info in it. I have moved the
connection info out of the page load section.
Click to expand...


The datasouce is the default view.

in that example im not sure why it is using Session("TaskTable") when we
want the defaultview we just sorted
try

TaskGridView.DataSource = dt.DefaultView



Any further assistance is appreciated.

Thanks.
John



ThatsIT.net.au said:
Without testing it...

i can see what you are trying to do, and i wouldent

You do not need to convert sortorder to sql, it has nothing to do with
sql.
What you are doing is working only for that click beacuse the code you
have
needs to run evey time if you are going to do it like that.

Load your data into a datatable, then when sorting you sort the
datatables
default view and then databind to the grid.
you may need to take a few mintes to learn about datatables and datviews

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.sorting.aspx


I have a webpage with a gridview on it that has 6 pages with 20 per
page.
The issue I have is when it sorts and then try and go to another page
the
sorting discontinues and the grid order goes back to the original
order.
I
am not getting both to work together and it is getting frustrating. I
am
definitely considered a newbie at this, but I'm working to better the
status.
Here is all the C# code I have concerning the gridview (no html, that
is
fine).

protected void Page_Load(object sender, EventArgs e)
{
string connStr =
ConfigurationManager.ConnectionStrings["ProteusConnectionString"].ConnectionString;
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(connStr);

{
conn.Open();
string sql = "FillGridView";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
sqlDa.Fill(dt);

if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
//Load data and do nothing
}
}
{
conn.Close();
}
}

protected void GridView1_PageIndexChanging(object sender,
GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}

private string ConvertSortDirectionToSQL(SortDirection
sortDirection)
{
string newSortDirection = string.Empty;
switch (sortDirection)
{
case SortDirection.Ascending:
newSortDirection = "ASC";
break;
case SortDirection.Descending:
newSortDirection = "DESC";
break;
}
return newSortDirection;
}

protected void GridView1_Sorting(object sender,
GridViewSortEventArgs
e)
{
DataTable dt = GridView1.DataSource as DataTable;

if (dt != null)
{
DataView dvw = new DataView(dt);

dvw.Sort = e.SortExpression + " " +
ConvertSortDirectionToSQL(e.SortDirection);
e.SortExpression = e.SortExpression.ToString();
GridView1.DataSource = dvw;
GridView1.DataBind();
}
}

protected void GridView1_RowEditing(object sender,
GridViewEditEventArgs
e)
{
GridView1.EditIndex = e.NewEditIndex;
GridView1.DataBind();
}

protected void GridView1_RowCancelingEdit(object sender,
GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GridView1.DataBind();
}
protected void GridView1_SelectedIndexChanging(object sender,
GridViewSelectEventArgs e)
{
GridViewRow row = GridView1.Rows[e.NewSelectedIndex];
}

protected void GridView1_SelectedIndexChanged(object sender,
EventArgs
e)
{
GridViewRow row = GridView1.SelectedRow;
}
}

Can anyone please review this and see what I am doing wrong or its
right
but
just not enough. Whatever the case, let me know.

Thanks... John
Click to expand...
Click to expand...
Click to expand...
 
JohnE said:
There is a field in the table called Originator. I am providing the code
so
you can see it all rather the just the line it fails on.

public void GridView1DataBind()
{
string connStr =
ConfigurationManager.ConnectionStrings["ProteusConnectionString"].ConnectionString;
string sql = "FillGridView";

SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(sql, conn);
Click to expand...

conn.open and close not needed when filling datatable, not do you need to
make command, addapter has its own command


all you need is (in vb )

Dim ad As New SqlDataAdapter(Sql, conn)
Dim dt As New Table
ad.Fill(dt)

now save in session if you like
Session["TaskList"] = dt

conn.Open();

DataTable dt = new DataTable();
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);

sqlDa.Fill(dt);

GridView1.DataSource = dt;
GridView1.DataBind();

conn.Close();
}

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
Click to expand...


not sure what your trying to do here, think the example may be confusing you



I have rewritten it cutting out a lot of code, see bottom of page


DataTable taskTable = new DataTable("TaskList");

taskTable.Columns.Add("ID", typeof(int));
taskTable.Columns.Add("Descrip", typeof(string));

for (int i = 0; i < 21; i++)
{
DataRow tableRow = taskTable.NewRow();
tableRow["ID"] = i;
tableRow["Descrip"] = "Task " + (20 - i).ToString();
taskTable.Rows.Add(tableRow);
}
Session["TaskList"] = taskTable;

GridView1.DataSource = Session["TaskList"];
GridView1.DataBind();
}
GridView1DataBind();
}

protected void GridView1_PageIndexChanging(object sender,
GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}

private string GetSortDirection(string column)
{
string sortDirection = "ASC";
string sortExpression = ViewState["SortExpression"] as string;

if (sortExpression != null)
{
if (sortExpression == column)
{
string lastDirection = ViewState["SortDirection"] as
string;

if ((lastDirection != null) && (lastDirection == "ASC"))
{
sortDirection = "DESC";
}
}
}
ViewState["SortDirection"] = sortDirection;
ViewState["SortExpression"] = column;

return sortDirection;
}

protected void GridView1_Sorting(object sender, GridViewSortEventArgs
e)
{
DataTable dt = Session["TaskList"] as DataTable;

if (dt != null)
{
dt.DefaultView.Sort = e.SortExpression + " " +
GetSortDirection(e.SortExpression);
GridView1.DataSource = Session["TaskList"];
GridView1.DataBind();
}
}

One thing I did notice is that when stepping thru, the gridview loads like
it should. Then on the attempts to sort, it gets to the If in the page
load
and goes straight to the fail line I mentioned.

Thanks for hangin' in there on this.

... John




ThatsIT.net.au said:
JohnE said:
I reviewed the info at the link and was finally able to give it a try.
It
keeps failing at this line in the Sorting part, which I have pasted
below.
It tells me it can not find the column I am trying to sort on. But
yet,
when
I hover over the information in the line, it shows the correct info
including
the column I'm trying to sort by.
Click to expand...

check that the datatable has that column

dt.DefaultView.Sort = e.SortExpression + " " +
GetSortDirection(e.SortExpression);


One thing I am wondering about is the Session object. I might not be
listing the correct one. How can I verify the name of the session
object
that is used to fill the gridview? I have the connection string info,
is
that it? The original posting has the info in it. I have moved the
connection info out of the page load section.
Click to expand...


The datasouce is the default view.

in that example im not sure why it is using Session("TaskTable") when we
want the defaultview we just sorted
try

TaskGridView.DataSource = dt.DefaultView



Any further assistance is appreciated.

Thanks.
John



:

Without testing it...

i can see what you are trying to do, and i wouldent

You do not need to convert sortorder to sql, it has nothing to do with
sql.
What you are doing is working only for that click beacuse the code you
have
needs to run evey time if you are going to do it like that.

Load your data into a datatable, then when sorting you sort the
datatables
default view and then databind to the grid.
you may need to take a few mintes to learn about datatables and
datviews

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.sorting.aspx


I have a webpage with a gridview on it that has 6 pages with 20 per
page.
The issue I have is when it sorts and then try and go to another
page
the
sorting discontinues and the grid order goes back to the original
order.
I
am not getting both to work together and it is getting frustrating.
I
am
definitely considered a newbie at this, but I'm working to better
the
status.
Here is all the C# code I have concerning the gridview (no html,
that
is
fine).

protected void Page_Load(object sender, EventArgs e)
{
string connStr =
ConfigurationManager.ConnectionStrings["ProteusConnectionString"].ConnectionString;
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(connStr);

{
conn.Open();
string sql = "FillGridView";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
sqlDa.Fill(dt);

if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
//Load data and do nothing
}
}
{
conn.Close();
}
}

protected void GridView1_PageIndexChanging(object sender,
GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}

private string ConvertSortDirectionToSQL(SortDirection
sortDirection)
{
string newSortDirection = string.Empty;
switch (sortDirection)
{
case SortDirection.Ascending:
newSortDirection = "ASC";
break;
case SortDirection.Descending:
newSortDirection = "DESC";
break;
}
return newSortDirection;
}

protected void GridView1_Sorting(object sender,
GridViewSortEventArgs
e)
{
DataTable dt = GridView1.DataSource as DataTable;

if (dt != null)
{
DataView dvw = new DataView(dt);

dvw.Sort = e.SortExpression + " " +
ConvertSortDirectionToSQL(e.SortDirection);
e.SortExpression = e.SortExpression.ToString();
GridView1.DataSource = dvw;
GridView1.DataBind();
}
}

protected void GridView1_RowEditing(object sender,
GridViewEditEventArgs
e)
{
GridView1.EditIndex = e.NewEditIndex;
GridView1.DataBind();
}

protected void GridView1_RowCancelingEdit(object sender,
GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GridView1.DataBind();
}
protected void GridView1_SelectedIndexChanging(object sender,
GridViewSelectEventArgs e)
{
GridViewRow row = GridView1.Rows[e.NewSelectedIndex];
}

protected void GridView1_SelectedIndexChanged(object sender,
EventArgs
e)
{
GridViewRow row = GridView1.SelectedRow;
}
}

Can anyone please review this and see what I am doing wrong or its
right
but
just not enough. Whatever the case, let me know.

Thanks... John
Click to expand...
Click to expand...
Click to expand...





//ok here we load the data into grid for the first time
//then we store the datable in session

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
string connStr =
ConfigurationManager.ConnectionStrings["ProteusConnectionString"].ConnectionString;
string sql = "FillGridView";
SqlConnection conn = new SqlConnection(connStr);

DataTable dt = new DataTable();
SqlDataAdapter sqlDa = new SqlDataAdapter(sql,conn);
sqlDa.Fill(dt);

GridView1.DataSource = dt;
GridView1.DataBind();

Session["mydataTable"] = dt;
}

}

// not seen your syntax in c# before , string sortExpression =
ViewState["SortExpression"] as string;
// usely , string sortExpression =
(String)ViewState["SortExpression"]
// so changed it
private string GetSortDirection(string column)
{
string sortDirection = "ASC";
string sortExpression = (String)ViewState["SortExpression"];

if (sortExpression != null)
{
if (sortExpression == column)
{
string lastDirection =
(String)ViewState["SortDirection"];

if ((lastDirection != null) && (lastDirection == "ASC"))
{
sortDirection = "DESC";
}
}
}
ViewState["SortDirection"] = sortDirection;
ViewState["SortExpression"] = column;

return sortDirection;
}


// here we get the datatabe back from session
//sort it
//the bind it to grid using its default view
// the datatabe does not sort, only its default view sorts
protected void GridView1_Sorting(object sender,
GridViewSortEventArgs e)
{
//convert session var to datatabe
DataTable dt = (DataTable)Session["mydataTable"];

if (dt != null)
{
dt.DefaultView.Sort = e.SortExpression + " " +
GetSortDirection(e.SortExpression);
GridView1.DataSource = dt.DefaultView;
GridView1.DataBind();
}
}


// hope this helps, code not tested but closer then before. let me know
how you go
 
Well, it failed at the same spot. Had to tweak some of what you did. The
grid filled on first opening but if I tried to page, the gridview
disappeared. So I had to review and tweak the page load. Otherwise used
what you provided.

What puzzles me is that one would think Microsoft would know about the
oddity in the paging and sorting they would patch it.

Time to move on and come back to the gridview some other day. Thanks for
your help and perseverance on this.

.... John



ThatsIT.net.au said:
JohnE said:
There is a field in the table called Originator. I am providing the code
so
you can see it all rather the just the line it fails on.

public void GridView1DataBind()
{
string connStr =
ConfigurationManager.ConnectionStrings["ProteusConnectionString"].ConnectionString;
string sql = "FillGridView";

SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(sql, conn);
Click to expand...

conn.open and close not needed when filling datatable, not do you need to
make command, addapter has its own command


all you need is (in vb )

Dim ad As New SqlDataAdapter(Sql, conn)
Dim dt As New Table
ad.Fill(dt)

now save in session if you like
Session["TaskList"] = dt

conn.Open();

DataTable dt = new DataTable();
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);

sqlDa.Fill(dt);

GridView1.DataSource = dt;
GridView1.DataBind();

conn.Close();
}

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
Click to expand...


not sure what your trying to do here, think the example may be confusing you



I have rewritten it cutting out a lot of code, see bottom of page


DataTable taskTable = new DataTable("TaskList");

taskTable.Columns.Add("ID", typeof(int));
taskTable.Columns.Add("Descrip", typeof(string));

for (int i = 0; i < 21; i++)
{
DataRow tableRow = taskTable.NewRow();
tableRow["ID"] = i;
tableRow["Descrip"] = "Task " + (20 - i).ToString();
taskTable.Rows.Add(tableRow);
}
Session["TaskList"] = taskTable;

GridView1.DataSource = Session["TaskList"];
GridView1.DataBind();
}
GridView1DataBind();
}

protected void GridView1_PageIndexChanging(object sender,
GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}

private string GetSortDirection(string column)
{
string sortDirection = "ASC";
string sortExpression = ViewState["SortExpression"] as string;

if (sortExpression != null)
{
if (sortExpression == column)
{
string lastDirection = ViewState["SortDirection"] as
string;

if ((lastDirection != null) && (lastDirection == "ASC"))
{
sortDirection = "DESC";
}
}
}
ViewState["SortDirection"] = sortDirection;
ViewState["SortExpression"] = column;

return sortDirection;
}

protected void GridView1_Sorting(object sender, GridViewSortEventArgs
e)
{
DataTable dt = Session["TaskList"] as DataTable;

if (dt != null)
{
dt.DefaultView.Sort = e.SortExpression + " " +
GetSortDirection(e.SortExpression);
GridView1.DataSource = Session["TaskList"];
GridView1.DataBind();
}
}

One thing I did notice is that when stepping thru, the gridview loads like
it should. Then on the attempts to sort, it gets to the If in the page
load
and goes straight to the fail line I mentioned.

Thanks for hangin' in there on this.

... John




ThatsIT.net.au said:
I reviewed the info at the link and was finally able to give it a try.
It
keeps failing at this line in the Sorting part, which I have pasted
below.
It tells me it can not find the column I am trying to sort on. But
yet,
when
I hover over the information in the line, it shows the correct info
including
the column I'm trying to sort by.


check that the datatable has that column


dt.DefaultView.Sort = e.SortExpression + " " +
GetSortDirection(e.SortExpression);



One thing I am wondering about is the Session object. I might not be
listing the correct one. How can I verify the name of the session
object
that is used to fill the gridview? I have the connection string info,
is
that it? The original posting has the info in it. I have moved the
connection info out of the page load section.


The datasouce is the default view.

in that example im not sure why it is using Session("TaskTable") when we
want the defaultview we just sorted
try

TaskGridView.DataSource = dt.DefaultView





Any further assistance is appreciated.

Thanks.
John



:

Without testing it...

i can see what you are trying to do, and i wouldent

You do not need to convert sortorder to sql, it has nothing to do with
sql.
What you are doing is working only for that click beacuse the code you
have
needs to run evey time if you are going to do it like that.

Load your data into a datatable, then when sorting you sort the
datatables
default view and then databind to the grid.
you may need to take a few mintes to learn about datatables and
datviews

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.sorting.aspx


I have a webpage with a gridview on it that has 6 pages with 20 per
page.
The issue I have is when it sorts and then try and go to another
page
the
sorting discontinues and the grid order goes back to the original
order.
I
am not getting both to work together and it is getting frustrating.
I
am
definitely considered a newbie at this, but I'm working to better
the
status.
Here is all the C# code I have concerning the gridview (no html,
that
is
fine).

protected void Page_Load(object sender, EventArgs e)
{
string connStr =
ConfigurationManager.ConnectionStrings["ProteusConnectionString"].ConnectionString;
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(connStr);

{
conn.Open();
string sql = "FillGridView";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
sqlDa.Fill(dt);

if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
//Load data and do nothing
}
}
{
conn.Close();
}
}

protected void GridView1_PageIndexChanging(object sender,
GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}

private string ConvertSortDirectionToSQL(SortDirection
sortDirection)
{
string newSortDirection = string.Empty;
switch (sortDirection)
{
case SortDirection.Ascending:
newSortDirection = "ASC";
break;
case SortDirection.Descending:
newSortDirection = "DESC";
break;
}
return newSortDirection;
}

protected void GridView1_Sorting(object sender,
GridViewSortEventArgs
e)
{
DataTable dt = GridView1.DataSource as DataTable;

if (dt != null)
{
DataView dvw = new DataView(dt);

dvw.Sort = e.SortExpression + " " +
ConvertSortDirectionToSQL(e.SortDirection);
e.SortExpression = e.SortExpression.ToString();
GridView1.DataSource = dvw;
GridView1.DataBind();
}
}

protected void GridView1_RowEditing(object sender,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top