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

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

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

Real World Branding with SharePoint 2010 Publishing Sites

Introduction to Real World Branding with SharePoint 2010 Publishing Sites Gathering Design Requirements for a SharePoint Publishing Site Planning for SharePoint Branding Tasks Creating Realistic Design Comps for SharePoint Site Designs Creating the Adventure Works Travel HTML Creating .css files for Adventure Works Travel Testing SharePoint Webpage Design in Multiple Browsers Creating the Brand in SharePoint Adding .css and Image Files to SharePoint Building the Master Page with HTML Building Out .css Rules for the SharePoint Site Design Creating a Custom Page Layout Editing a Page Layout with SharePoint Designer Packaging and Deploying SharePoint Branding     Download code Introduction to Real World Branding with SharePoint 2010 Publishing Sites Microsoft SharePoint Server 2010 publishing sites use Publishing Features to provide capabilities to create engaging web content management (WCM) sites. Frequently used as Internet-facing websites, th...
Post a Comment
Read more