Skip to main content

How to Write An SPQuery Using Multiple AND OR Operators

Writing CAML queries can sometimes make your head hurt. Not because it’s terribly complicated, but because there’s not much useful information out there that demonstrates, with REAL examples, how these should be written. So let’s see if we can clarify this:

Scenario 1

Get me all items in a list WHERE fullName equals the currently logged in user.
1
2
3
4
5
6
7
8
9
10
11

SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
 
oQuery.Query = 
    "<Where>" + 
    "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" + 
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";

Scenario 2

Get me all items in a list WHERE fullName equals the currently logged in user AND status equals ‘Complete’.
1
2
3
4
5
6
7
8
9
10
11
12
13

SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
oQuery.Query = 
    "<Where>" +
    "<And>" +
        "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" +
        "<Eq><FieldRef Name='Status'/><Value Type='Text'>Complete</Value></Eq>" + 
    "</And>" +
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";

Scenario 3

Get me all items in a list WHERE fullName equals the currently logged in user AND status equals ‘Complete’ AND manager is James Lane.
This is where it gets a bit tricky. The following example is INCORRECT and will produce an error when run:
1
2
3
4
5
6
7
8
9
10
11
12
13
14

SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
oQuery.Query = 
    "<Where>" +
    "<And>" +
        "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" +
        "<Eq><FieldRef Name='Status'/><Value Type='Text'>Complete</Value></Eq>" +
        "<Eq><FieldRef Name='Manager'/><Value Type='Text'>James Lane</Value></Eq>" +
    "</And>" +
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";
This is the correct way to do it:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
oQuery.Query = 
    "<Where>" +
    "<And>" +
        "<And>" +
            "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" +
            "<Eq><FieldRef Name='Status'/><Value Type='Text'>Complete</Value></Eq>" +
        "</And>" +
        "<Eq><FieldRef Name='Manager'/><Value Type='Text'>James Lane</Value></Eq>" +
    "</And>" +
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";

Scenario 4

Get me all items WHERE fullName equals the currently logged in user AND status equals ‘Complete’ OR status equals ‘On Hold’.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
oQuery.Query = 
    "<Where>" +
    "<And>" +
        "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" +
        "<Or>" +
            "<Eq><FieldRef Name='Status'/><Value Type='Text'>Complete</Value></Eq>" +
            "<Eq><FieldRef Name='Status'/><Value Type='Text'>On Hold</Value></Eq>" +
        "</Or>" +
    "</And>" +
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";
Labels:
Location: Anand, Gujarat, India

Popular posts from this blog

How to deal with SharePoint 2010 exception "An exception occurred when trying to issue security token: The server was unable to process the request due to an internal error"

Scenario: You receive the below exception when you try to logon to a site that has been configured to use Claims Based Authentication with a custom membership provider using FBA credentials: Event ID from Event Log  - 8306 An exception occurred when trying to issue security token: The server was unable to process the request due to an internal error.  For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the <serviceDebug> configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework 3.0 SDK documentation and inspect the server trace logs.. Explanation: This error started to appear in our QA environment which does not have Visual Studio installed. I have tried starting the service "Claims to Windows Token Service" but that did not help either. I have made sure that all config...
Post a Comment
Read more

SharePoint SPFX components basic details and understanding

Microsoft developed SharePoint, a web-based platform that enables businesses and organisations to share and manage documents and information. The SharePoint Framework (SPFx) is a set of client-side tools and components that can be used to construct unique solutions on top of SharePoint. The creation of a SharePoint SPFx component will be covered in this blog. Let's address specific requirements before creating a SharePoint SPFx component. Install the necessary software on your computer: Version 10 or later of Node.js SharePoint Framework (SPFx) generator using Git Now that all the prerequisites have been deployed, let's start developing the SharePoint SPFx component. Start by making a new SPFx project. Making a new SPFx application is the initial step. Launch the command prompt and execute the following commands  yo @microsoft/sharepoint The Yeoman generator for the SharePoint Framework will be started using this command. You will be prompted to enter details about your project...
Post a Comment
Read more

Cascading drop down column in a SharePoint List

This article will show how to use codeplex project to achieve Cascading drop down columns in SharePoint list. This article will show how to achieve parent child relationship in column of SharePoint list.   Objective This article will show how to use codeplex project to achieve Cascading drop down columns in SharePoint list. This article will show how to achieve parent child relationship in column of SharePoint list. Step 1 Download the project from codeplex . Choose WSP file to download. To download the project Click here Step 2 After downloading the WSP add the solution using STSADM command. Navigate to BIN folder and add the solution. Command C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\BIN> stsa dm -o addsolution -filename c:\Stoneshare.CascadingDropDown.WithFilter.wsp Step 3 Open Central ADMIN and deploy solution. Navigate to Operation -> Global Configuration -> Solution Management. Select the Global Deployment option. St...
Post a Comment
Read more