Timer Job throws Field types not installed correctly Exception when querying a list

Question

Hi Guys I have this situation:

A timer job queries a list and gets items from it. The 2 queries are:

   <Where>
    <And>
        <And>
            <Leq>
                <FieldRef Name="To" />
                <Value Type="DateTime">[Today+5Day(s)]</Value>
            </Leq>
            <Gt>
                <FieldRef Name="To" />
                <Value Type="DateTime">[Today+0Day(s)]</Value>
            </Gt>
        </And>
        <BeginsWith>
            <FieldRef Name="ContentTypeId"/>
            <Value Type="ContentTypeId">0x010050E924B6CB83F348AFD03D6A6A639920</Value>
        </BeginsWith>
    </And>
   </Where>

And

<Where>
      <And>
         <Geq>
            <FieldRef Name="Next_x0020_Oil_x0020_Change_x002" />
            <Value Type="DateTime">[Today+0Day(s)]</Value>
         </Geq>
         <Leq>
            <FieldRef Name="Next_x0020_Oil_x0020_Change_x002" />
            <Value Type="DateTime">[Today+5Day(s)]</Value>
         </Leq>
      </And>
   </Where>

Both queries are generated with the U2U Caml Query Builder work fine and return the items I need.

The Problem:

When I added code to use the queries the first returns 0 rows(shouldn't be 0), and the second throws the SPException - One or more field types are not installed properly.

How come that both queries work just fine in the U2U tool?

Here is some of the code I use:

SPList techInfoList = web.GetList(site.Url + "/Lists/TechInfo");

SPQuery caml = new SPQuery();
caml.Query = ...

items = techInfoList.GetItems(caml);

As far as I know there are no problems with the field types and the exception does not give any useful information of the field that could be causing the problem.

I read that [Today] could cause problems so I tried <Today ...>. Still the same.

Answer 1

based on experience of the error, its due to the internal name being wrong!

goto list settings, click on the field in question, it should take you to the edit page for the field. look at the url should be somthing like:

http://mysite:40669/_layouts/15/FldEdit.aspx?List=%7BE072EA9D%2D1D1F%2D4B95%2D8A8C%2D7CE83142D736%7D&Field=Group%5Fx0020%5Fhome

my field is called group home

the url consist of Group%5Fx0020%5Fhome so the internal name is Group_x0020_home (replace %5F with _)

just like to note that internal names are truncated to 32 in length.... so your right in using:

Next_x0020_Oil_x0020_Change_x002 

and not

Next_x0020_Oil_x0020_Change_x0020

try this:

if its the main site that your trying to call than do it through the site url and openweb for SPWeb, otherwise state the subsite within the SPWeb.

  using(SPSite site = new SPSite("http://mysite"))
  {
    //for specific web
    //using(SPWeb web = site.OpenWeb("subsite"))

    //current top level (site)
    using(SPWeb web = site.OpenWeb()) 
    {
       SPList techInfoList = web.Lists["TechInfo"];

       SPQuery caml = new SPQuery();
       caml.Query = "<Where>"+
                       "<And>"+
                         "<Geq>"+
                             "<FieldRef Name='Next_x0020_Oil_x0020_Change_x002' />"+
                             "<Value Type='DateTime'>[Today+0Day(s)]</Value>"+
                         "</Geq>"+
                         "<Leq>"+
                             "<FieldRef Name='Next_x0020_Oil_x0020_Change_x002' />"+
                             "<Value Type='DateTime'>[Today+5Day(s)]</Value>"+
                         "</Leq>"+
                       "</And>"+
                     "</Where>";
       SPListItemCollection items = techInfoList.GetItems(caml);
   }
  }

Answer 2

I found a way to fix it.

These queries are part of 4 Caml queries. I execute them one by one. My mistake was that I used the same SPQuery object. I just changed the Query property. What I didn't know was that after the first execution the SPQuery object stores some configuration data for faster querying.You can not just change the Query property and expect it to work fine. It appears to work but makes changes to the logic and does not always return the items you need. After the 3-rd time it blew up completely.

The Solution:

Create a new SPQuery object every time you want to query a list.