Maintain (Retain) ASP.Net GridView Sorting after row Updating using C# and VB.Net

Hi Hubi,

I have modified your code please take its refrence and correct it.

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

HTML

<asp:GridView ID="gvAB" runat="server" AutoGenerateColumns="false" AllowPaging="true"
    AllowSorting="true" OnSorting="OnSorting" OnRowCancelingEdit="gvABRowCancelingEdit"
    OnRowEditing="gvABRowEditing" OnRowUpdating="gvABRowUpdating" OnPageIndexChanging="gvAB_PageIndexChanging"
    PageSize="10" DataKeyNames="CustomerID">
    <Columns>
        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" ReadOnly="true" SortExpression="Country" />
        <asp:CommandField ShowEditButton="true" HeaderText="Edit" />
    </Columns>
</asp:GridView>

Namespaces

C#

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

Code

C#

private string SortColumn
{
    get { return ViewState["SortColumn"] != null ? ViewState["SortColumn"].ToString() : "Name"; }
    set { ViewState["SortColumn"] = value; }
}

private string SortDirection
{
    get { return ViewState["SortDirection"] != null ? ViewState["SortDirection"].ToString() : "ASC"; }
    set { ViewState["SortDirection"] = value; }
}

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

protected void ShowData()
{
    using (SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString))
    {
        string query = "SELECT CustomerID,Name,Country FROM Customers ORDER BY " + this.SortColumn + " " + this.SortDirection;
        SqlCommand cmd = new SqlCommand(query, sqlCon);
        SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
        sqlCon.Open();
        DataTable dtbl = new DataTable();
        sqlDa.Fill(dtbl);
        gvAB.DataSource = dtbl;
        gvAB.DataBind();
    }
}

protected void OnSorting(object sender, GridViewSortEventArgs e)
{
    this.SortDirection = this.SortDirection == "ASC" ? "DESC" : "ASC";
    this.SortColumn = e.SortExpression;
    this.ShowData();
}

protected void gvABRowEditing(object sender, GridViewEditEventArgs e)
{
    gvAB.EditIndex = e.NewEditIndex;
    this.ShowData();
}

protected void gvABRowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
    gvAB.EditIndex = -1;
    this.ShowData();
}

protected void gvAB_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    this.gvAB.PageIndex = e.NewPageIndex;
    this.ShowData();
}

protected void gvABRowUpdating(object sender, GridViewUpdateEventArgs e)
{
    //Update code
    GridViewRow row = gvAB.Rows[e.RowIndex] as GridViewRow;
    string conString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    string query = "UPDATE Customers SET Name = @Name WHERE CustomerID = @Id";
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        cmd.Connection = con;
        cmd.Parameters.AddWithValue("@Id", e.Keys[0]);
        cmd.Parameters.AddWithValue("@Name", e.NewValues[0]);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
    gvAB.EditIndex = -1;
    this.ShowData();
}

VB.Net

Private Property SortColumn As String
    Get
        Return If(ViewState("SortColumn") IsNot Nothing, ViewState("SortColumn").ToString(), "Name")
    End Get
    Set(ByVal value As String)
        ViewState("SortColumn") = value
    End Set
End Property

Private Property SortDirection As String
    Get
        Return If(ViewState("SortDirection") IsNot Nothing, ViewState("SortDirection").ToString(), "ASC")
    End Get
    Set(ByVal value As String)
        ViewState("SortDirection") = value
    End Set
End Property

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Me.ShowData()
    End If
End Sub

Protected Sub ShowData()
    Using sqlCon As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString").ConnectionString)
        Dim query As String = "SELECT CustomerID,Name,Country FROM Customers ORDER BY " & Me.SortColumn & " " & Me.SortDirection
        Dim cmd As SqlCommand = New SqlCommand(query, sqlCon)
        Dim sqlDa As SqlDataAdapter = New SqlDataAdapter(cmd)
        sqlCon.Open()
        Dim dtbl As DataTable = New DataTable()
        sqlDa.Fill(dtbl)
        gvAB.DataSource = dtbl
        gvAB.DataBind()
    End Using
End Sub

Protected Sub OnSorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
    Me.SortDirection = If(Me.SortDirection = "ASC", "DESC", "ASC")
    Me.SortColumn = e.SortExpression
    Me.ShowData()
End Sub

Protected Sub gvABRowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
    gvAB.EditIndex = e.NewEditIndex
    Me.ShowData()
End Sub

Protected Sub gvABRowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
    gvAB.EditIndex = -1
    Me.ShowData()
End Sub

Protected Sub gvAB_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    Me.gvAB.PageIndex = e.NewPageIndex
    Me.ShowData()
End Sub

Protected Sub gvABRowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
    ' Update code
    Dim row As GridViewRow = TryCast(gvAB.Rows(e.RowIndex), GridViewRow)
    Dim conString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Dim query As String = "UPDATE Customers SET Name = @Name WHERE CustomerID = @Id"
    Dim cmd As SqlCommand = New SqlCommand(query)
    Using con As SqlConnection = New SqlConnection(conString)
        cmd.Connection = con
        cmd.Parameters.AddWithValue("@Id", e.Keys(0))
        cmd.Parameters.AddWithValue("@Name", e.NewValues(0))
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Using
    gvAB.EditIndex = -1
    Me.ShowData()
End Sub

Screenshot