none
CAML Query returning no result RRS feed

  • Question

  • I have this CAML query running fine on my local dev machine, but on live production machine, it returns zero result.

    <Where>
    <And>
          <Geq><FieldRef Name='Created' /><Value Type='DateTime'>[Today-60Day(s)]</Value></Geq>
          <And>
             <Contains><FieldRef Name='Author' /><Value Type='User'>Phil Smith</Value></Contains>       
             <And>
                  <IsNotNull><FieldRef Name='Customer' /></IsNotNull>
                  <Or>
                      <IsNotNull><FieldRef Name='_x0031_Quantity' /></IsNotNull>
                      <Or>
                            <IsNotNull><FieldRef Name='_x0032_Quantity' /></IsNotNull>
                                <Or>
                                     <IsNotNull><FieldRef Name='_x0033_Quantity' /></IsNotNull>
                                     <IsNotNull><FieldRef Name='_x0034_Quantity' /></IsNotNull>
                                </Or>
                       </Or>
                  </Or>
               </And>
          </And>
    </And>
    </Where>
    

    I even run it in CAML Query builder on Live machine and it does return result, but it's just in code, it's not returning anything. The code is as follow:

    spQuery.Query = String.Format("<Where>" +
                                         "<And>" +
                                             "<Geq>" +
                                                 "<FieldRef Name='Created' />" +
                                                 "<Value Type='DateTime'>[Today-60Day(s)]</Value>" +
                                             "</Geq>" +
                                             "<And>" +
                                                 "<Contains>" +
                                                     "<FieldRef Name='Author' />" +
                                                     "<Value Type='User'>{0}</Value>" +
                                                 "</Contains> " +
                                                 "<And>" +
                                                     "<IsNotNull><FieldRef Name='Customer' /></IsNotNull>" +
                                                     "<Or>" +
                                                         "<IsNotNull><FieldRef Name='_x0031_Quantity' /></IsNotNull>" +
                                                         "<Or>" +
                                                             "<IsNotNull><FieldRef Name='_x0032_Quantity' /></IsNotNull>" +
                                                             "<Or>" +
                                                                 "<IsNotNull><FieldRef Name='_x0033_Quantity' /></IsNotNull>" +
                                                                 "<IsNotNull><FieldRef Name='_x0034_Quantity' /></IsNotNull>" +
                                                             "</Or>" +
                                                         "</Or>" +
                                                     "</Or>" +
                                                  "</And>" +
                                              "</And>" +
                                           "</And>" +
                                        "</Where>", myWeb.CurrentUser.Name);
                   SPListItemCollection result = oPriceList.GetItems(spQuery);
                   if (result.Count > 0)
    



    • Moved by Mike Walsh FIN Friday, September 16, 2011 8:22 AM A CAML query could be regarded as Programming or Admin but it certainly isn't General (From:SharePoint - General Question and Answers and Discussion (pre-SharePoint 2010))
    Friday, September 16, 2011 7:23 AM

Answers

  • Hi,

    You probably forgot this statment; View Scope='RecursiveAll' , have a look :

    <View Scope='RecursiveAll'>
       <Query>
           <Where>
     ....
           </Where>
       </Query>
    </View>

     


    Gabriel Renom - Business Systems Analyst - Pannone LLP - Blog: www.gabrielrenom.net
    • Marked as answer by Pengyu Zhao Monday, September 26, 2011 2:01 AM
    Tuesday, September 20, 2011 9:44 AM

All replies

  • Can't you debug the code and fetch the query?

    Might be the date format be the issue.


    Regards, Dharnendra Shah "strong belief is the only way to success"
    Friday, September 16, 2011 10:55 AM
  • I debugged and it shows the result count as zero. But if I paste the query into CAML Query Builder, it returns result.
    Monday, September 19, 2011 12:19 AM
  • Instead of "<Value Type='DateTime'>[Today-60Day(s)]</Value>", Could you try with the following:

    <Value Type='DateTime'><Today OffsetDays="-60"/></Value>"

    Also, it is better to debug the code and see the value of caml string during runtime (rather than seeing the values in caml buider).


    Thanks Arut
    • Edited by arutjothi Monday, September 19, 2011 6:38 AM
    Monday, September 19, 2011 6:36 AM
  • Hey Wll6568,

     

    can you verify the column internal names correct on Live environment, as to your Local Dev box.

     

    you can use Windows SharePoint Service RPC Protocol to get the Fields, List Data information from live site

     

    http://www.synergyonline.com/Blog/Lists/Posts/Post.aspx?ID=123


    Thanks
    Akhilesh Nirapure
    Monday, September 19, 2011 6:37 AM
  • That's very strange.

    1. are u using the same query generated by debug code to check ? are u adding "<Query> </Query> tag above <Where> tag generated by debug query at the time of checking its result in to CAML query builder. As without this tag it might return all values.

    2. Check the site URL and URL which you connect through CAML builder, both are same.

     


    Regards, Dharnendra Shah "strong belief is the only way to success"
    Monday, September 19, 2011 6:42 AM
  • Tried... no luck.

    I did grab the caml string from the debug mode in run time, and put it in Caml Query Builder and it did return result,

    Just not returning anything in Sharepoint/ code.

    Monday, September 19, 2011 6:45 AM
  • Tried... no luck.

    I did grab the caml string from the debug mode in run time, and put it in Caml Query Builder and it did return result,

    Just not returning anything in Sharepoint/ code.


    Did you paste the query which you got from debug mode? Is it same like above?

    and Did you added <Query> tag as I mentioned above  while adding this query into CAML builder?

     


    Regards, Dharnendra Shah "strong belief is the only way to success"
    Monday, September 19, 2011 6:54 AM
  • Wll6568,

     

    You can try http://splistkit.codeplex.com/


    Thanks
    Akhilesh Nirapure
    Monday, September 19, 2011 9:01 AM
  • Hi,

    You probably forgot this statment; View Scope='RecursiveAll' , have a look :

    <View Scope='RecursiveAll'>
       <Query>
           <Where>
     ....
           </Where>
       </Query>
    </View>

     


    Gabriel Renom - Business Systems Analyst - Pannone LLP - Blog: www.gabrielrenom.net
    • Marked as answer by Pengyu Zhao Monday, September 26, 2011 2:01 AM
    Tuesday, September 20, 2011 9:44 AM
  • <Where><Eq><FieldRef Name='D'  /><Value Type='Text'>" + id + "</Value></Eq></Where>", "Scope='RecursiveAll'"

    Hi,

    We have the same problem on our production environment.

    The caml query works fine on my Dev machine , but same code doesnt work on Production environment through code, however results are returned if I use camlquey builder.

    The only difference is My dev macine is a single Farm env. an dproduction env is mult-Farm env.

    Does anyone has any idea what can be the issue ?


    Regards, Parag

    Friday, January 17, 2014 10:48 AM
  • The problem was with the web service and not realy caml query as the web service is invoked from worker process  and due to slow server performance it was taking long to return results on production environment and because of it web service was timing out,however same code was working fine in development environment as server performance was good and web service returned results before timing out.

    Regards, Parag

    Friday, February 21, 2014 11:25 AM