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 company

IN 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.