I had my first tortuous experience with SPQuery today and found out a few things. Now this isn’t a full explanation of SPQuery, I suggest Ted’s book for that. But there is the answer to a couple of gotcha’ s that can truip you up
Using the handy U2U CAML builder helps you build correct CAML, so start there. So here is an example query I created.
<Query>
<Where>
<Eq>
<FieldRef Name=’CustomerReference’ />
<Value Type=’Lookup’>CS0000001</Value>
</Eq>
</Where>
<OrderBy>
<FieldRef Name=’Created’ Ascending=’False’ />
</OrderBy>
</Query>
And that’s where the first problem begins.
I found that you need to drop the <Query> tags else it returns everything.
query.Query = "<OrderBy><FieldRef Name=’Created’ Ascending=’False’ /></OrderBy>
<Where><Eq><FieldRef Name=’CustomerReference’ /><Value
Type=’Lookup’>CS0000001</Value></Eq></Where>"
Is what you need to put in.
I had to do two queries for my problem where I found my next problem.
You Can’t re-use the Query object, or maybe you can but certainly not with different lists. Despite only having populated one property, when used the Query object get populated by other values making it useless for re-use. CREATE A NEW ONE.
query=new SPQuery();
Third problem, more my stupidity, but easily done. Havnig been used to using Lists and databases etc I’m used to referencing them as
returnvalue.Items / returnedItems.Rows.
Don’t make the same mistake with the returned values from the query e.g.
collListItems = list.GetItems(query);
foreach ( SPListItem item in colListItems.List)
{…..}
The above is SO wrong, but an easy mistake, it references the main list again and not your filtered items.
foreach (SPListItem item in colListItems)
{….}
Is what it should be.
I can’t think of any more gotchas that I stumbled across, but next time your SPQuery doesn’t work, its probably you that broke it.
List of possible google searches for someone to help find this page. SPQuery not sorting |
http://rcm-uk.amazon.co.uk/e/cm?t=binaryjam-21&o=2&p=8&l=as1&asins=0735623201&fc1=000000&IS2=1<1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr |
I had exactly the same problem:
http://blogs.imeta.co.uk/njenman/archive/2008/08/18/317.aspx
Hi Binaryjam,
I know it is not clearly mentioned by the U2U CAML Query Builder but the tags are only needed when you work with the GetListItems method of the Lists web service. For the rest, the query is the same. This will become more clearly when the tool generates code samples instead of pure CAML.
I appoligize for the confusion.
Karine Bosch
Developer of the U2U CAML Query Builder
Thanks Binaryjam
You actually saved me from going nuts.