Sorting By Multiple Columns How Can i Do i?

[Eradicate]

I have some problem with sorting in multiple columns. I tried duplicating the the events etc but it still won't work. It tends to only sort by one expression. For example, i have 2 columns "name" and "company". When i click name, it will sort in ascending order of the names, which is correct but when i tried sorting by company, it sorts by name which isn't correct.

The code below is for sorting only by name.

private void Page_Load(object sender, System.EventArgs e)
{
if (!Page.IsPostBack)
{
ViewState["sortField"] = "Name";
ViewState["sortDirection"] = "DESC";
BindGrid();
}
}

private void BindGrid()
{
SqlCommand cmd = sqlConnection1.CreateCommand();
cmd.CommandText = "SELECT * FROM Factory.Company ORDER BY " + ViewState["sortField"].ToString() + " " + ViewState["sortDirection"].ToString();
SqlDataAdapter sqlDataAdapter11 = new SqlDataAdapter();
sqlDataAdapter11.SelectCommand = cmd;
DataSet ds = new DataSet();
sqlConnection1.Open();
sqlDataAdapter11.Fill(ds, "Company");
sqlConnection1.Close();
DataGrid5.DataSource = ds;
DataGrid5.DataBind();
}
private void DataGrid5_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
DataGrid5.CurrentPageIndex = e.NewPageIndex;
BindGrid();
}

private void DataGrid5_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{
if(e.SortExpression.ToString() == ViewState["sortField"].ToString())
{
switch(ViewState["sortDirection"].ToString())
{
case "ASC":
ViewState["sortDirection"] = "DESC";
break;

case "DESC":
ViewState["sortDirection"] = "ASC";
break;
}
}
else
{
ViewState["sortField"] = e.SortExpression;
ViewState["sortDirection"] = "DESC";
}
BindGrid();
}


What should be added in order to make multi column sorting work? or i have to re-write the whole program?

Help needed thanks in advance

-John

[Krzemo]

U need some kind of collection (not a single value):

For example something like this:


ViewState["sortFields"][0].FieldName = "Name1";
ViewState["sortFields"][1].FieldName = "Name2";
ViewState["sortFields"][1].Ascending= false;
.....
cmd.CommandText = "SELECT * FROM Factory.Company ORDER BY " + ViewState["sortFields"].ToString() ;
it should build SQL like this:


SELECT * FROM Factory.Company ORDER BY Name1,Name2 DESC

Best regards,
Krzemo.

[Eradicate]

After making the collection, how do i specify the cloumn name will sort by name and the company column will sort by company. Without them sticking together as a single sort expression?

[Krzemo]

I don't undestand what U wrote...

how do i specify the cloumn name will sort by name and the company column will sort by companyIN SQL: SELECT * FROM Factory,Company ORDER BY name,company
in c# U add Factory and Company to sort fields collection.

When U build SQL expression U must iterate through sort fields collection and concatenate strings to compose ORDER BY clause.

Without them sticking together as a single sort expression They have to stick together (in SQL expression).

Hope it helps.

[Eradicate]

Sorry for some mistakes in my last post lol. What im trying to say is.
Right now, i have 2 sort column "Name" & "Company". I've created 2 sort expressions named "SortName" and "SortCompany". But the problem is, How do i specify that "SortName" is for sorting the column "Name" and "SortCompany" is for sorting Column "Company" ?

The code i attached on my first post, is my original codes which does only 1 column sorting.

I don't think it is wise to post the 2 column sorting codes because it doesn't even runs just errors :).

Well sorry for my poor english. Hope you can understand better of my problem now. Thanks for replying.

[Krzemo]

Sorry I'm not shure I understand it now.

First thing:

U have problem with SQL or C#?

Is this SQL (below) working or not?
SELECT * FROM Factory.Company ORDER BY name,company
Sorry for my poor english too :) .

[Eradicate]

The problem is, i have no idea. How to activate the 2 sort expression when the event handler on the datagrid only allow me to choose one sort expression. After adding

SELECT * FROM Factory.Company ORDER BY name,company


Which settings and commands should i change or add in order for 2 sort expression to activate on both columns?

[Krzemo]

i have no idea. How to activate the 2 sort expression when the event handler on the datagrid only allow me to choose one sort expression.1 scenario:

* at start collection is empty so it should be no order by clause
* when user click at column company then,because there is no entries in collection, add entry "company" to collection. So your's code should generate:
ORDER BY company
* when user click name it again failed to find entry, so entry should be added to collection (preferably on begining, if U add it on end U have to construct backward loop).
Now generated code looks like:
ORDER BY name,company

* user again clicked on name and it is already in collection ,so ensure that it is first element in collection and toggle it sorting direction
Now generated code looks like:
ORDER BY name DESC,company

* user clicked on company and it is already in collection ,so ensure that it is first element in collection and toggle it sorting direction
Now generated code looks like:
ORDER BY company DESC,name DESC

or (if U prefer) dont toggle if it is not first element in collection.

* etc.

Hope it helps.