GetListItems not returning items from a date range

Nov 5, 2014 at 3:53 PM
Edited Nov 5, 2014 at 3:54 PM
Hello,

I am using SPServices to a multitude of different things on our SP 2010 portal. One of them is using a custom HTML form to set query parameters to aggregate items from multiple lists and write them to a single list. Basically we are setting a combination of user, project, start date and end date to formal the CAMLQuery parameters. All of which are optional. Everything works fine until I try to use a start date and end date in the query. The server returns a SOAP error and I cannot figure out why.

*Note that when I specify only start date or only end date the query runs successfully.

SPServices call:
    GetListItems: function (list, fields, query) {

        $().SPServices({
            operation: "GetListItems",
            async: false,
            listName: list,
            CAMLViewFields: fields,
            CAMLQuery: query,
            completefunc: function (xData, Status) {

                results = $(xData.responseXML).SPFilterNode("z:row").SPXmlToJson({

                    includeAllAttrs: true,
                    removeOws: true

                });

            }

        });
        return results;
    }
As you can see I am using parameters for the list, view fields, and query. The code for these is as follows:
list = "{" + GUID + "}";

fields = "<ViewFields><FieldRef Name='User' /><FieldRef Name='ID' /><FieldRef Name='Date_x0020_Completed' /><FieldRef Name='Hours' /><FieldRef Name='Notes' /><FieldRef Name='Project' /><FieldRef Name='Task_x0020_Type' /></ViewFields>";

query = "<Query><Where><And><Contains><FieldRef Name='Project' /><Value Type='Lookup'>" + application + "</Value></Contains><Leq><FieldRef Name='Date_x0020_Completed' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + endDateFx + "</Value></Leq><Geq><FieldRef Name='Date_x0020_Completed' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + startDateFx + "</Value></Geq></And></Where></Query>";
When I run SPServices with the above parameters, I receive a SOAP error back from the server. Here is the response:
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<soap:Fault>
<faultcode>soap:Server</faultcode>
<faultstring>Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.</faultstring>
<detail>
<errorstring xmlns="http://schemas.microsoft.com/sharepoint/soap/">Cannot complete this action. Please try again.</errorstring>
<errorcode xmlns="http://schemas.microsoft.com/sharepoint/soap/">0x80004005</errorcode>
</detail>
</soap:Fault>
</soap:Body>
</soap:Envelope>
Here is what my post XML looks like on a successful query:
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
      <listName>{66A8B776-A1C2-41A4-8E01-870A59768063}</listName>
      <viewName></viewName>
      <query>
        <Query>
          <Where>
            <And>
              <Contains>
                <FieldRef Name="Project"></FieldRef>
                <Value Type="Lookup">SharePoint</Value>
              </Contains>
              <Leq>
                <FieldRef Name="Date_x0020_Completed"></FieldRef>
                <Value IncludeTimeValue="TRUE" Type="DateTime">2014-10-01 00:00:00</Value>
              </Leq>
            </And>
          </Where>
        </Query>
      </query>
      <viewFields>
        <ViewFields>
          <FieldRef Name="User"></FieldRef>
          <FieldRef Name="ID"></FieldRef>
          <FieldRef Name="Date_x0020_Completed"></FieldRef>
          <FieldRef Name="Hours"></FieldRef>
          <FieldRef Name="Notes"></FieldRef>
          <FieldRef Name="Project"></FieldRef>
          <FieldRef Name="Task_x0020_Type"></FieldRef>
        </ViewFields>
      </viewFields>
      <rowLimit>0</rowLimit>
      <queryOptions>
        <QueryOptions></QueryOptions>
      </queryOptions>
    </GetListItems>
  </soap:Body>
</soap:Envelope>
However, when I add one more query parameter to the call it fails. This is what the query looks like on the failed query:
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
      <listName>{66A8B776-A1C2-41A4-8E01-870A59768063}</listName>
      <viewName></viewName>
      <query>
        <Query>
          <Where>
            <And>
              <Contains>
                <FieldRef Name="Project"></FieldRef>
                <Value Type="Lookup">SharePoint</Value>
              </Contains>
              <Leq>
                <FieldRef Name="Date_x0020_Completed"></FieldRef>
                <Value IncludeTimeValue="TRUE" Type="DateTime">2014-10-01 00:00:00</Value>
              </Leq>
              <Geq>
                <FieldRef Name="Date_x0020_Completed"></FieldRef>
                <Value IncludeTimeValue="TRUE" Type="DateTime">2014-09-01 00:00:00</Value>
              </Geq>
            </And>
          </Where>
        </Query>
      </query>
      <viewFields>
        <ViewFields>
          <FieldRef Name="User"></FieldRef>
          <FieldRef Name="ID"></FieldRef>
          <FieldRef Name="Date_x0020_Completed"></FieldRef>
          <FieldRef Name="Hours"></FieldRef>
          <FieldRef Name="Notes"></FieldRef>
          <FieldRef Name="Project"></FieldRef>
          <FieldRef Name="Task_x0020_Type"></FieldRef>
        </ViewFields>
      </viewFields>
      <rowLimit>0</rowLimit>
      <queryOptions>
        <QueryOptions></QueryOptions>
      </queryOptions>
    </GetListItems>
  </soap:Body>
</soap:Envelope>
I am at a loss here as the SOAP error codes are basically useless and my code works until I add the second date parameter to the query. Any help you could provide would be greatly appreciated.

Thanks,

Brandon Hankins
Nov 5, 2014 at 9:12 PM
It was a poorly formatted CAMLQuery. I had to add an additional <And> clause. Problem solved.
Coordinator
Nov 5, 2014 at 9:13 PM
Glad you got it sorted!

M.