none
CAML - Datetime column comparison with blank (SharePoint 2010)

    Question

  • Scenario: I have a custom List named MyList with columns 1) Cycle as Choice (with values Q1, Q2, Q3 and Q4) and 2) "Cycle Due Date" as Date and Time as Optional and few other fields. The data in the list looks like below.

    Cycle List

    Question: How do I compare the Datetime column for blank values. From the above scenario, before Inserting a new row I need to validate if the combination already exist. So user should be prompted an error when he tries to enter Cycle as Q1 and "Cycle Due Date" as blank.

    Here is my code. 

    string sDueDateFormatted = SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtDueDate);
                                
                                StringBuilder sCAMLQuery = new StringBuilder(string.Empty);
                                sCAMLQuery.Append("<Where><And>");
                                sCAMLQuery.Append("<Eq>");
                                sCAMLQuery.Append("<FieldRef Name='Cycle'/>");
                                sCAMLQuery.Append("<Value Type='Choice'>" + sCycle + "</Value>");
                                sCAMLQuery.Append("</Eq>");
                                sCAMLQuery.Append("<Eq>");
                                sCAMLQuery.Append("<FieldRef Name='Cycle_x0020_Due_x0020_Date'/>");
                                sCAMLQuery.Append("<Value Type='DateTime'>" + sDueDateFormatted + "</Value>");
                                sCAMLQuery.Append("</Eq>");
                                sCAMLQuery.Append("</And></Where>");
    
                                SPQuery QueryResult = new SPQuery();
                                QueryResult.Query = sCAMLQuery.ToString();
                                SPListItemCollection ResultListItems = list.GetItems(QueryResult);
    
                                if (ResultListItems.Count > 0)
                                {
                                    lblError.Text = "Error: The selected Cycle & Due Date combination already exist.";
                                }

    Thanks!


    Regards, Sagir R Kazi

    Wednesday, June 12, 2013 12:30 PM

Answers

  • hi

    you can use the following query:

    <Where>
      <And>
        <Eq>
          <FieldRef Name="Cycle " />
          <Value Type="Text">Q1</Value>
        </Eq>
        <IsNull>
          <FieldRef Name="Cycle Due Date" />
        </IsNull>
      </And>
    </Where>

    Also you can use free open source Camlex library for dynamic building of the queries using C# without CAML. There is online service which allows to convert CAML to Camlex syntax: http://camlex-online.org. This example will be re-written with Camlex like this:

    string query = Camlex.Query().Where(x => (string)x["Cycle "] == "Q1" && x["Cycle Due Date"] == null).ToString();

    Blog - http://sadomovalex.blogspot.com
    CAML via C# - http://camlex.codeplex.com


    • Edited by sadomovalexMVP Wednesday, June 12, 2013 12:50 PM .
    • Marked as answer by Sagir Kazi Wednesday, June 12, 2013 12:58 PM
    Wednesday, June 12, 2013 12:50 PM

All replies

  • hi

    you can use the following query:

    <Where>
      <And>
        <Eq>
          <FieldRef Name="Cycle " />
          <Value Type="Text">Q1</Value>
        </Eq>
        <IsNull>
          <FieldRef Name="Cycle Due Date" />
        </IsNull>
      </And>
    </Where>

    Also you can use free open source Camlex library for dynamic building of the queries using C# without CAML. There is online service which allows to convert CAML to Camlex syntax: http://camlex-online.org. This example will be re-written with Camlex like this:

    string query = Camlex.Query().Where(x => (string)x["Cycle "] == "Q1" && x["Cycle Due Date"] == null).ToString();

    Blog - http://sadomovalex.blogspot.com
    CAML via C# - http://camlex.codeplex.com


    • Edited by sadomovalexMVP Wednesday, June 12, 2013 12:50 PM .
    • Marked as answer by Sagir Kazi Wednesday, June 12, 2013 12:58 PM
    Wednesday, June 12, 2013 12:50 PM
  • You could try this

     StringBuilder sCAMLQuery = new StringBuilder(string.Empty);
                                sCAMLQuery.Append("<Where><And>");
                                sCAMLQuery.Append("<Eq>");
                                sCAMLQuery.Append("<FieldRef Name='Cycle'/>");
                                sCAMLQuery.Append("<Value Type='Choice'>" + sCycle + "</Value>");
                                sCAMLQuery.Append("</Eq>");
                                sCAMLQuery.Append("<Eq>");
                                sCAMLQuery.Append("<FieldRef Name='Cycle_x0020_Due_x0020_Date'/>");
    			   if(sDueDateFormatted=="")
    			   {
    				sCAMLQuery.Append("<IsNull><FieldRef Name='DateTime' /></IsNull>");
    			   }
    			   else
    			   {
    				sCAMLQuery.Append("<Value Type='DateTime'>" + sDueDateFormatted + "</Value>");                           
    			   }
                                sCAMLQuery.Append("</Eq>");
                                sCAMLQuery.Append("</And></Where>");


    --Cheers



    • Edited by Prasath C Wednesday, June 12, 2013 1:06 PM
    Wednesday, June 12, 2013 12:53 PM
  • Thanks sadomovalex and Prasath C for the quick response. That solved my problem. I was unaware of the <IsNull> thing in CAML.

    Prasath C - the CAML is incorrect but I got the point :).


    Regards, Sagir R Kazi


    • Edited by Sagir Kazi Wednesday, June 12, 2013 1:15 PM typo
    Wednesday, June 12, 2013 1:02 PM