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

Migrating from Skype for Business to Microsoft Teams: A Step-by-Step Guide

Do you still use Skype for Business to meet the communication and collaboration needs of your business? If so, now is the perfect time to think about switching to Microsoft Teams, a cutting-edge platform with cutting-edge capabilities and seamless connectivity with other Microsoft services. But if you're unfamiliar with the procedure, switching to a new platform can seem like a difficult task. I'll walk you through the process of switching from Skype for Business to Microsoft Teams in this article. Plan the migration in Step 1 You must make a plan before you start the relocation procedure. Set a deadline, make a list of all the Skype for Business capabilities you presently use, and choose whether to migrate gradually or all at once. Step 2: Set up your surroundings. Make sure your network and infrastructure fulfil Microsoft Teams' standards. Upgrades to your hardware, software, and licences might be necessary. Additionally, confirm that you have the right permissions to ca...
Post a Comment
Read more

Custom Form Base Login Web Part With Sharepoint 2010

In my  Last Post , I talked about creating a Form based authentication web application with Active Directory LDAP. After implementing that you have surely noticed that, when you try to sign in after creating a site collection from FBA web application, you get the following sign in page: Surely, it does not look pretty at all. So, you might want to change this default login page. In order to do this, first let us create an empty SharePoint project in Visual Studio 2010. After that, add an application page by right click-selecting “Add New Item” and you will get like the following window in Solution Explorer: Notice that, when you add any application page to your project, Visual Studio automatically creates a Layouts  folder. To keep things simple enough for your understanding, you may keep your application page under a folder where you might name it as same as your project like the figure above. Now, add a reference for  Microsoft.SharePoint.IdentityModel...
2 comments
Read more