none
Problem in Dynamic CAML in SharePoint 2013 server RRS feed

  • Question

  • Hi Folks,

     I need to select specific IDs from a Workflow Tasks in SharePoint. I made dynamic CAML query and created a console application and this is the code: 

    using Microsoft.SharePoint;
    using Microsoft.SharePoint.Utilities;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleAppCAML_Test
    {
        class Program
        {
            static void Main(string[] args)
            {
    
                //make changes based on your site url
                string siteurl = "http://intranet.contoso.com/";
                using (SPSite site = new SPSite(siteurl))
                {
                    using (SPWeb web = site.OpenWeb() ) {
    
                        SPList listWF = web.Lists.TryGetList("Workflow Tasks");
    
    
                        SPQuery queryAll = new SPQuery();
                        queryAll.Query = "<view><Query /></view>";
    
                        SPListItemCollection collListItems = listWF.GetItems(queryAll);
    
                        Console.WriteLine("---------------------GET ALL LIST ITEMS---------------------------------" + "\n\r");
                       
    
                        foreach (SPListItem oListItem in collListItems)
                        {
                            Console.Write(SPEncode.HtmlEncode(oListItem["Title"].ToString()) + " :  " + SPEncode.HtmlEncode(oListItem["ID"].ToString() + "\n\r "));
                        }
    
    
                   
                        Console.WriteLine("---------------------GET SOME LIST ITEMS---------------------------------" + "\n\r");
    
    
                        SPQuery querySelected = new SPQuery();
    
    
                        // Get selectedValues
                        string selectedValues = "5, 8";
                        string queryMainFirst = "";
                        string queryFilter = "";
                        string queryMainLast = "";
    
    
                        string[] values = selectedValues.Split(',');
                        for (int i = 0; i < values.Length; i++)
                        {
                            values[i] = values[i].Trim();
                            queryMainFirst = "<Where><Or>";
                            queryMainLast = "</Or></Where>";
                            queryFilter += "<Eq><FieldRef Name='ID' /><Value Type='Counter'>" + values[i].Trim() + "</Value></Eq>";
    
                        }
    
                        //WORKS FINE but IT IS HARD CODED Values
                        querySelected.Query = "<Where><Or><Eq><FieldRef Name='ID' /><Value Type='Counter'>5</Value></Eq><Eq><FieldRef Name='ID' /><Value Type='Counter'>8</Value></Eq></Or></Where>";
                        SPListItemCollection querySelectedItemColl = listWF.GetItems(querySelected);
                        Console.WriteLine("Hard Coded CAML, Items in list: " + querySelectedItemColl.Count);
    
                        /////HARD CODED Values
                        foreach (SPListItem item in querySelectedItemColl)
                        {
                            Console.WriteLine(" ID is: " + item["ID"].ToString());
                        }
    
    
                        //TRIED TO MAKE CAMLE QUERY Dynamic 
                        string finalQuery = queryMainFirst + queryFilter + queryMainLast;
    
                        // THESE below TWO Statements both the strings Hard coded and Dynamic are EXACTLY same. 
                        SPQuery queryDynamic = new SPQuery();
                        queryDynamic.Query = finalQuery;
    
                        SPListItemCollection itemsColl = listWF.GetItems(finalQuery);
                        Console.WriteLine("Dynamic CAML, Items in list: " + itemsColl.Count);
    
                        foreach (SPListItem item in itemsColl)
                        {
                            Console.WriteLine("ID is: " + item["ID"].ToString());
                        }
                    }
                }
                Console.ReadKey();
            }
        }
    }



    Basically I need to dynamically select the tasks which users have selected and pass it CAML query. This the code for Console application, both of Hard Coded Values and Dynamic values in CAML in the list.



    For the Hard Coded values/filters I am exact results for selected IDs.
    However, for the Dynamic values/filters I am getting all the entries in the in the list as follows:



    I don’t know what is the issue.

    Any pointer would be greatly appreciated.
    Thank you.   


    Sandy


    • Edited by Sandy 791 Monday, December 19, 2016 3:12 PM updated the question
    Monday, December 19, 2016 3:12 PM

Answers

  • Thanks Lee.

    My issue was resolved. I had to use String.Format(finalQuery)

    Thanks!


    Sandy

    • Marked as answer by Sandy 791 Sunday, December 25, 2016 10:15 AM
    Sunday, December 25, 2016 10:14 AM

All replies

  • Had to use String.Format(finalQuery) and it worked.



    using Microsoft.SharePoint;
    using Microsoft.SharePoint.Utilities;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleAppCAML_Test
    {
        class Program
        {
            static void Main(string[] args)
            {
    
                //make changes based on your site url
                string siteurl = "http://intranet.insight-dev.com/";
                using (SPSite site = new SPSite(siteurl))
                {
                    using (SPWeb web = site.OpenWeb() ) {
    
                        SPList listWF = web.Lists.TryGetList("Workflow Tasks");
    
       
                        SPQuery queryDynamic = new SPQuery();
    
                        // Get selectedValues
                        string selectedValues = "5, 8";
                        string queryMainFirst = "";
                        string queryFilter = "";
                        string queryMainLast = "";
    
    
                        string[] values = selectedValues.Split(',');
                        for (int i = 0; i < values.Length; i++)
                        {
                            values[i] = values[i].Trim();
                            queryMainFirst = "<Where><Or>";
                            queryMainLast = "</Or></Where>";
                            queryFilter += "<Eq><FieldRef Name='ID' /><Value Type='Counter'>" + values[i].Trim() + "</Value></Eq>";
    
                        }
                        string finalQuery = queryMainFirst + queryFilter + queryMainLast;
                        //queryDynamic.Query = finalQuery;
                        queryDynamic.Query = String.Format(finalQuery);
    
                        SPListItemCollection itemsColl = listWF.GetItems(queryDynamic);
                        Console.WriteLine("Dynamic CAML, Items in list: " + itemsColl.Count);
    
                        foreach (SPListItem item in itemsColl)
                        {
                            Console.WriteLine("ID is: " + item["ID"].ToString());
                        }
                    }
                }
                Console.ReadKey();
            }
        }
    }
    

     

    Sandy

    Tuesday, December 20, 2016 9:37 AM
  • Hi,

    You have found the issue, but you need to rebuild your query for multiple ids(more than 2), below is the sample code I tested based on your code for your reference:

    SPList listWF = web.Lists.TryGetList("Employee");
    
                        // Get selectedValues
                        string selectedValues = "1,2,3,4,5";
                        string queryMainFirst = "";
                        string queryFilter = "";
                        string queryMainLast = "";
    
                        queryMainFirst = "<Where>";
                        queryMainLast = "</Or>";
                        string[] values = selectedValues.Split(',');
                        for (int i = values.Length - 1; i >= 0; i--)
                        {
                            values[i] = values[i].Trim();
                            if (i < 2)
                            {
                                if (i == 1)
                                    queryFilter += "<Or><Eq><FieldRef Name='ID' /><Value Type='Counter'>" + values[i].Trim() + "</Value></Eq>";
                                else
                                    queryFilter += "<Eq><FieldRef Name='ID' /><Value Type='Counter'>" + values[i].Trim() + "</Value></Eq>";
                            }
                            else
                            {
                                queryFilter += "<Or><Eq><FieldRef Name='ID' /><Value Type='Counter'>" + values[i].Trim() + "</Value></Eq>";
                                queryMainLast += "</Or>";
                            }
                        }
    
                        //TRIED TO MAKE CAMLE QUERY Dynamic 
                        string finalQuery = queryMainFirst + queryFilter + queryMainLast + "</Where>";
    
                        // THESE below TWO Statements both the strings Hard coded and Dynamic are EXACTLY same. 
                        SPQuery queryDynamic = new SPQuery();
                        queryDynamic.Query = finalQuery;
    
                        SPListItemCollection itemsColl = listWF.GetItems(queryDynamic);
                        Console.WriteLine("Dynamic CAML, Items in list: " + itemsColl.Count);
    
                        foreach (SPListItem item in itemsColl)
                        {
                            Console.WriteLine("ID is: " + item["ID"].ToString());
                        }

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, December 21, 2016 5:13 AM
  • Thanks Lee.

    My issue was resolved. I had to use String.Format(finalQuery)

    Thanks!


    Sandy

    • Marked as answer by Sandy 791 Sunday, December 25, 2016 10:15 AM
    Sunday, December 25, 2016 10:14 AM