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

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

How to: Debug SharePoint Applications

You can greatly simplify debugging by using Visual Studio extensions for Windows SharePoint Services. This topic includes procedures that explain the following: Debugging with Visual Studio extensions for Windows SharePoint Services Performing manual debugging Performing remote debugging Debugging with Visual Studio extensions for Windows SharePoint Services Press the F5 key to begin debugging with Visual Studio extensions for Windows SharePoint Services. The following procedure demonstrates how to enable F5 debugging. To enable F5 debugging Locate and open the target SharePoint application's Web.config file. By default, it is located in C:\Inetpub\wwwroot\wss\VirtualDirectories\80. Find the following line of code and change the  debug  attribute to  true . Save the changes to the Web.config file. In Visual Studio, right-click the SharePoint project, and then click  Properties . Click the  Debug  tab, and then type the target SharePoint URL in t
Read more

SharePoint 2010 – How to use Audio and Video Webpart

Video and Audio Web Part [ a.k.a Media Web Part ] is one of the new cool web parts in SharePoint 2010, so in this article I am going to show you the way to add this web part in your page. To add Media web part to the page you must activate those two features 1- SharePoint Server Publishing Infrastructure Feature in Site Collection features 2- SharePoint Server Publishing Feature in Site features After that edit page then click on Insert tab in SharePoint Ribbon then select Video and Audio Now the Media Web Part in the page Click on the Media Web Part and you will find Options Tab in the Ribbon As you can see in the Options tab there are Chang Media button allow you to select the video and Chang Image button to change the image of web part and Styles (Dark, Light) Click on Change Media to select the Media file. From Computer opens up the Upload media so you can upload the video and display it. Once you click ok and saving the page. Click on play button and enj
Post a Comment
Read more