Peter Peter - 5 months ago 30
SQL Question

Sorting a dropdown list in C#

I acquired this code from someone and need to organize the list alphabetically. I tried to add an

ORDER BY
at the end of the
Select
but it actually makes the whole list empty. I then tried adding additional code to sort it after the list is retrieved, still nothing. Any ideas?

Thanks in advance!

public void FillDropdowns()
{
SPWeb web = site.RootWeb;
string queryText = "SELECT JobTitle1,Department,PracticeArea,Offices from Scope() WHERE \"SCOPE\" = 'People' AND DisplayInDirectory = 'Include'";//,SecondaryOfficeLocationFROM Scope() WHERE \"SCOPE\" = 'People' " + finalquery + " AND DisplayInDirectory = 'Include' ";//,SecondaryOfficeLocation
FullTextSqlQuery query1 = new FullTextSqlQuery(site);
query1.QueryText = queryText;
query1.ResultTypes = ResultType.RelevantResults;
query1.RowLimit = 1000;
ResultTableCollection resultTables = query1.Execute();
DataTable dt = new DataTable();
if (resultTables.Count > 0)
{
ResultTable relevantResults = resultTables[ResultType.RelevantResults];
dt.Load(relevantResults, LoadOption.OverwriteChanges);
}

int j = dt.Rows.Count;
DataView dv = new DataView(dt);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
ddldept.DataSource = dv.ToTable(true, "Department");
ddldept.DataTextField = "Department";
ddldept.DataValueField = "Department";
ddldept.Items.Insert(0, new ListItem("select department", "select department"));
ddldept.DataBind();

ddlpractice.DataSource = dv.ToTable(true, "PracticeArea");
ddlpractice.DataTextField = "PracticeArea";
ddlpractice.DataValueField = "PracticeArea";
ddlpractice.Items.Insert(0, new ListItem("select practiceArea", "select practiceArea"));
ddlpractice.DataBind();

ddltitle.DataSource = dv.ToTable(true, "JobTitle1");
ddltitle.DataTextField = "JobTitle1";
ddltitle.DataValueField = "JobTitle1";
ddltitle.Items.Insert(0, new ListItem("select title", "select title"));
ddltitle.DataBind();

ddlOffice.DataSource = dv.ToTable(true, "Offices");
ddlOffice.DataTextField = "Offices";
ddlOffice.DataValueField = "Offices";
ddlOffice.Items.Insert(0, new ListItem("select offices", "select offices"));
ddlOffice.DataBind();

}


EDIT: Here is where I added the
ORDER BY
:

string queryText = "SELECT JobTitle1,Department,PracticeArea,Offices from Scope() WHERE \"SCOPE\" = 'People' AND DisplayInDirectory = 'Include' ORDER BY Department ASC";

Answer

Here are a few options:

  1. Add the order by within your SQL Statement

  2. Sort the dataview

Example:

DataTable table = dataSet.Tables["TableName"];
DataView view = table.DefaultView;
view.Sort = "Criteria";