My SPQuery doesn’t work

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 SPQuery returns all items SPQuery returns nothing

http://rcm-uk.amazon.co.uk/e/cm?t=binaryjam-21&o=2&p=8&l=as1&asins=0735623201&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr