muhammad12 says:
DataTable dt = GridView1.DataSource as DataTable;
Since the dt is null, the sorting is not working.
You need to create a method (GetData) that will return DataTable and that DataTable will be used to populate the GridView and sorting to be work correctly.
Refer the updated code with method that will return the DataTable.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.BindGridView();
this.PopulateDepartmentDropdown();
}
}
private DataTable GetData()
{
string query = "SELECT ext as Extensions, description as Name, department as Department, station as Station FROM users";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
// Check if a department is selected
if (DropDept.SelectedIndex > 0) // Assuming "All" is at index 0
{
query += " WHERE department = @department";
}
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand(query))
{
// Add department parameter if a department is selected
if (DropDept.SelectedIndex > 0)
{
cmd.Parameters.AddWithValue("@department", DropDept.SelectedValue);
}
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
}
}
}
private void BindGridView()
{
GridView1.DataSource = this.GetData();
GridView1.DataBind();
}
[WebMethod]
[ScriptMethod]
public static List<string> GetSearchSuggestions(string input)
{
List<string> suggestions = new List<string>();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "SELECT ext, description, department, station FROM users WHERE description LIKE @search OR ext LIKE @search ORDER BY description ASC";
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand(query, con))
{
cmd.Parameters.AddWithValue("@search", "%" + input + "%");
con.Open();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string suggestion = string.Format("{0} - {1} - {2} - {3}",
reader["ext"].ToString(),
reader["description"].ToString(),
reader["department"].ToString(),
reader["station"].ToString());
suggestions.Add(suggestion);
}
}
}
}
return suggestions;
}
private void PopulateDepartmentDropdown()
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "SELECT DISTINCT department FROM users ORDER BY department";
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand(query, con))
{
con.Open();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
DropDept.Items.Clear(); // Clear existing items
DropDept.Items.Add(new ListItem("All", "")); // Add "All" option
while (reader.Read())
{
ListItem item = new ListItem(reader["department"].ToString(), reader["department"].ToString());
DropDept.Items.Add(item);
}
}
}
}
}
protected void DropDept_SelectedIndexChanged(object sender, EventArgs e)
{
this.BindGridView();
}
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
DataTable dt = this.GetData();
if (dt != null)
{
dt.DefaultView.Sort = e.SortExpression + " " + GetSortDirection(e.SortExpression);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
private string GetSortDirection(string column)
{
string sortDirection = "ASC";
string lastDirection = ViewState["SortDirection"] as string;
if (lastDirection != null)
{
if (lastDirection.Equals("ASC") && column.Equals(ViewState["SortExpression"]))
{
sortDirection = "DESC";
}
}
ViewState["SortDirection"] = sortDirection;
ViewState["SortExpression"] = column;
return sortDirection;
}
