ASP.NET - Sorting the gridview which which displays the data from multiple tables.

Asked By keerthi Tata on 18-Jun-12 12:31 PM

Hi,

I am new to asp.net

I am using a grid view whose data I am populating on hit of button by running a sql query called on button_click function.The data comes from multiple tables and it is a joint query used based on the text field input on the page.

Presently I am not able to sort the Gridview when I click on the individual column field. Please help me I need to do it urgently.



I am getting the following error :

The GridView 'GridView1' fired event Sorting which wasn't handled.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: The GridView 'GridView1' fired event Sorting which wasn't handled.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[HttpException (0x80004005): The GridView 'GridView1' fired event Sorting which wasn't handled.]
   System.Web.UI.WebControls.GridView.OnSorting(GridViewSortEventArgs e) +2375041
   System.Web.UI.WebControls.GridView.HandleSort(String sortExpression, SortDirection sortDirection) +88
   System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +744
   System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument) +215
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981
dipa ahuja replied to keerthi Tata on 18-Jun-12 05:27 PM
   protected void Page_Load(object sender, EventArgs e)
  {
    if (!IsPostBack)
    {
      ViewState["sortOrder"] = "";
      bindGridView("", "");
    }
  }
  public void bindGridView(string sortExp, string sortDir)
  {
    string ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    SqlConnection SQLconnection = new SqlConnection(ConnString);
 
    if (SQLconnection.State == ConnectionState.Closed)
    {
      SQLconnection.Open();
    }
 
    SqlCommand mySqlCommand = new SqlCommand("select * from people", SQLconnection);
    SqlDataAdapter mySqlAdapter = new SqlDataAdapter(mySqlCommand);
    DataSet myDataSet = new DataSet();
    mySqlAdapter.Fill(myDataSet);
 
    DataView myDataView = new DataView();
    myDataView = myDataSet.Tables[0].DefaultView;
 
    if (sortExp != string.Empty)
    {
      myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir);
    }
 
    GridView1.DataSource = myDataView;
    GridView1.DataBind();
 
    // if condition that can be used to check the sql connection
    // if it is open then close it.
    if (SQLconnection.State == ConnectionState.Open)
    {
      SQLconnection.Close();
    }
  }
 
  protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
  {
    bindGridView(e.SortExpression, sortOrder);
  }
  public string sortOrder
  {
    get
    {
      if (ViewState["sortOrder"].ToString() == "desc")
      {
        ViewState["sortOrder"] = "asc";
      }
      else
      {
        ViewState["sortOrder"] = "desc";
      }
 
      return ViewState["sortOrder"].ToString();
    }
    set
    {
      ViewState["sortOrder"] = value;
    }
  }
   
 
keerthi Tata replied to dipa ahuja on 18-Jun-12 05:43 PM
Hi dipa,

Thanks for the reply. But can you provide me the code in visual Basic code behind for .aspx.vb page.

thanks
Keerthi
aneesa replied to keerthi Tata on 18-Jun-12 11:23 PM
<asp:GridView ID="GridView1"
runat="server"
AllowSorting="True"
OnSorting="GridView1_Sorting"
AutoGenerateColumns="False"
CellPadding="5">
<Columns>
<asp:BoundField DataField="productid" SortExpression="productid" HeaderText="Product ID" HeaderStyle-HorizontalAlign="Left" />
<asp:BoundField DataField="productname" SortExpression="productname" HeaderText="Product" HeaderStyle-HorizontalAlign="Left" />
<asp:BoundField DataField="unitprice" SortExpression="unitprice" HeaderText="Price" HeaderStyle-HorizontalAlign="Left"/>
</Columns>
</asp:GridView>
Imports System.Data
Imports System.Data.SqlClient
The above namespaces will allow you to access the SqlConnection, SqlCommand, SqlAdapter classes which provide the functionality to connect the SQL database and fetch the records from the specified table as in the following
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  If Not IsPostBack Then
    ViewState("sortOrder") = ""
    bindGridView("", "")
  End If
End Sub
Public Sub bindGridView(ByVal sortExp As String, ByVal sortDir As String)
  ' string variable to store the connection string
  ' defined in appsettings section of web.config file.
  Dim connStr As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
  ' object created for SqlConnection Class.
  Dim mySQLconnection As New SqlConnection(connStr)
  ' if condition that can be used to check the sql connection
  ' whether it is already open or not.
  If mySQLconnection.State = ConnectionState.Closed Then mySQLconnection.Open()
  Dim mySqlCommand As New SqlCommand("select * from products", mySQLconnection)
  Dim mySqlAdapter As New SqlDataAdapter(mySqlCommand)
  Dim myDataSet As New DataSet()
  mySqlAdapter.Fill(myDataSet)
  Dim myDataView As New DataView()
  myDataView = myDataSet.Tables(0).DefaultView
  If Not String.IsNullOrEmpty(sortExp) Then
    myDataView.Sort = String.Format("{0} {1}", sortExp, sortDir)
  End If
  GridView1.DataSource = myDataView
  GridView1.DataBind()
  ' if condition that can be used to check the sql connection
  ' if it is open then close it.
  If mySQLconnection.State = ConnectionState.Open Then mySQLconnection.Close()
End Sub
Protected Sub GridView1_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs)
  bindGridView(e.SortExpression, sortOrder)
End Sub
Public Property sortOrder() As String
  Get
    If ViewState("sortOrder").ToString() = "desc" Then
      ViewState("sortOrder") = "asc"
    Else
      ViewState("sortOrder") = "desc"
    End If
    Return ViewState("sortOrder").ToString()
  End Get
  Set(ByVal value As String)
    ViewState("sortOrder") = value
  End Set
End Property[/code]
keerthi Tata replied to aneesa on 20-Jun-12 11:54 AM
Thanks a lot aneesa,

I am able to implement the sorting  for the most of the pages for my website.

I have one situation where I am calling SQL query dynamically based on the value selected in the dropdown list on the aspx page and once the "get report" button is clicked.  And my function is Button1_click. Can you suggest the changes for this scenario.

Anyway I am trying to do some changes based on your feedback. Any help is highly appreciated.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
      Dim myConn As New SqlConnection
      myConn.ConnectionString = ConfigurationManager.ConnectionStrings("Fall2010_MSIS5643_Team1ConnectionString").ToString
      myConn.Open()
      Dim MySQL As String = " Select tA.Employee_Id,(substring(cast(tA.Training_Date as varchar),0,13)) as Training_Date,e.Employee_Fname,e.Employee_Lname,tA.Comments from Employee e,Training_Attended tA where(tA.Training_Id = " & _
      DropDownList1.SelectedValue & " and e.Employee_Id=tA.Employee_Id)"

      Dim MyDS = New DataSet()
      Dim MyDA = New SqlDataAdapter(MySQL, myConn)
      MyDA.Fill(MyDS)
      GridView1.DataSource = MyDS.Tables(0)
      GridView1.DataBind()
      myConn.Close()
        End Sub
keerthi Tata replied to aneesa on 21-Jun-12 02:13 PM

Thanks a lot aneesa,

I was able to solve the issue :)

I have set the table to session by:
 Session("TaskTable") = GridView1.DataSource

and used it in the GridView1_Sorting method