Answered by:
CAML query order by not working in SPList

Question
-
I am using the following code display items in list but it is not sorting them. No matter what I write in order by clause, it always brings the same results.
SPListItemCollection items = null; using (SPSite site = new SPSite(SPContext.Current.Site.Url)) { using (SPWeb web = site.OpenWeb("mysite")) { SPList list = web.Lists["Pages"]; SPQuery query = new SPQuery(); query.Query = "<ViewFields><FieldRef Name='Title' /><FieldRef Name='Created' /><FieldRef Name='LinkFilenameNoMenu' /></ViewFields><OrderBy><FieldRef Name='Created' Ascending='TRUE' /></OrderBy><QueryOptions><RowLimit>5</RowLimit></QueryOptions>"; items = list.GetItems(query); foreach (SPListItem lst in list.Items) { label1.Text += "||"+ lst["Title"].ToString() ; } } }
- Edited by Frank Martin Consultant Thursday, November 21, 2013 11:26 AM
Thursday, November 21, 2013 11:17 AM
Answers
-
I took a look at your code and was supprised to see that you aren't doing anything wrong:
These are the results I got myself on a document library:FieldName:Created FieldValue:9/26/2013 3:55:46 PM
FieldName:Created FieldValue:10/16/2013 11:16:35 AM
FieldName:Created FieldValue:10/16/2013 11:16:45 AMAs you can see they are ordered, just like you requested :-)
No not really.. You see my test is faulty, it seems to order by month day and then year so 10/5/2001 would end up behind 1/1/2013.
You can fix this by ordering by the "Created_x0020_Date" Field.
Since this field seem to be displayed in a year-month-day orderFieldName:Created_x0020_Date FieldValue:2013-10-16 11:16:46
public override void FeatureActivated(SPFeatureReceiverProperties properties) { SPListItemCollection items = null; SPWeb web = (SPWeb)properties.Feature.Parent; SPList list = web.Lists.TryGetList("Documents"); SPQuery query = new SPQuery(); query.Query = "<ViewFields><FieldRef Name='Title' /><FieldRef Name='Created_x0020_Date' /><FieldRef Name='LinkFilenameNoMenu' /></ViewFields><OrderBy><FieldRef Name='Created_x0020_Date' Ascending='TRUE' /></OrderBy><QueryOptions><RowLimit>5</RowLimit></QueryOptions>"; items = list.GetItems(query); foreach (SPListItem lst in list.Items) { foreach(SPField fld in lst.Fields) { if (lst[fld.InternalName] != null) System.Diagnostics.Debug.WriteLine("FieldName:" + fld.InternalName + " FieldValue:" + lst[fld.InternalName].ToString()); } } }
Kind regards, Rainier van Slingerlandt | www.justanothertechnologyguy.com
- Proposed as answer by Rainier van Slingerlandt Thursday, November 21, 2013 1:53 PM
- Marked as answer by tracycai Wednesday, November 27, 2013 4:21 AM
Thursday, November 21, 2013 1:52 PM -
- Marked as answer by tracycai Wednesday, November 27, 2013 4:21 AM
Thursday, November 21, 2013 2:53 PM
All replies
-
I took a look at your code and was supprised to see that you aren't doing anything wrong:
These are the results I got myself on a document library:FieldName:Created FieldValue:9/26/2013 3:55:46 PM
FieldName:Created FieldValue:10/16/2013 11:16:35 AM
FieldName:Created FieldValue:10/16/2013 11:16:45 AMAs you can see they are ordered, just like you requested :-)
No not really.. You see my test is faulty, it seems to order by month day and then year so 10/5/2001 would end up behind 1/1/2013.
You can fix this by ordering by the "Created_x0020_Date" Field.
Since this field seem to be displayed in a year-month-day orderFieldName:Created_x0020_Date FieldValue:2013-10-16 11:16:46
public override void FeatureActivated(SPFeatureReceiverProperties properties) { SPListItemCollection items = null; SPWeb web = (SPWeb)properties.Feature.Parent; SPList list = web.Lists.TryGetList("Documents"); SPQuery query = new SPQuery(); query.Query = "<ViewFields><FieldRef Name='Title' /><FieldRef Name='Created_x0020_Date' /><FieldRef Name='LinkFilenameNoMenu' /></ViewFields><OrderBy><FieldRef Name='Created_x0020_Date' Ascending='TRUE' /></OrderBy><QueryOptions><RowLimit>5</RowLimit></QueryOptions>"; items = list.GetItems(query); foreach (SPListItem lst in list.Items) { foreach(SPField fld in lst.Fields) { if (lst[fld.InternalName] != null) System.Diagnostics.Debug.WriteLine("FieldName:" + fld.InternalName + " FieldValue:" + lst[fld.InternalName].ToString()); } } }
Kind regards, Rainier van Slingerlandt | www.justanothertechnologyguy.com
- Proposed as answer by Rainier van Slingerlandt Thursday, November 21, 2013 1:53 PM
- Marked as answer by tracycai Wednesday, November 27, 2013 4:21 AM
Thursday, November 21, 2013 1:52 PM -
- Marked as answer by tracycai Wednesday, November 27, 2013 4:21 AM
Thursday, November 21, 2013 2:53 PM