Update CAML Query for SharePoint Data View Web Part

Posted on by Chris Quick

In a recent project, it was necessary to change the query being executed for a highly customized data view web part. One of the challenges you face when creating heavily customized data view web parts is the difficulty of changing the query through SharePoint Designer’s UI. Here’s a simple technique that should help.

When a DVWP is placed into the page, an SPDataSource is also created. This control has an attribute named SelectCommand that contains the CAML Query executed by the DVWP. You can go to the code and change it to whatever you need it to be.

	<DataSources>
		<SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" selectcommand="&lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;servicestatus&quot; /&gt;&lt;Value Type=&quot;Choice&quot;&gt;{Service}&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;OrderBy&gt;&lt;FieldRef Name=&quot;fullname&quot; Ascending=&quot;True&quot; /&gt;&lt;/OrderBy&gt;&lt;/Query&gt;" id="Veterans1"><SelectParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/><WebPartPages:DataFormParameter Name="Directory" ParameterKey="Directory" PropertyName="ParameterValues" DefaultValue="B"/></SelectParameters><DeleteParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/></DeleteParameters><UpdateParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/></UpdateParameters><InsertParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/></InsertParameters></SharePoint:SPDataSource>
	</DataSources>

Here’s my original query:
&lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;servicestatus&quot; /&gt;&lt;Value Type=&quot;Choice&quot;&gt;{Service}&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;OrderBy&gt;&lt;FieldRef Name=&quot;fullname&quot; Ascending=&quot;True&quot; /&gt;&lt;/OrderBy&gt;&lt;/Query&gt;

Notice that the query is HTML Encoded. Here’s how the query appears when decoded:

<Query><Where><Eq><FieldRef Name="servicestatus" /><Value Type="Choice">{Service}</Value></Eq></Where><OrderBy><FieldRef Name="fullname" Ascending="True" /></OrderBy></Query>

One of my dynamic properties appears in this query enclosed in brackets (i.e. {Service}). I can now simply change the query and ensure that it is HTML encoded as a replacement to the original SelectCommand:

<Query><Where><And><Eq><FieldRef Name="servicestatus" /><Value Type="Choice">{Service}</Value></Eq><Eq><FieldRef Name="TributeStatus" /><Value Type="Choice">Approved</Value></Eq></And></Where><OrderBy><FieldRef Name="fullname" Ascending="True" /></OrderBy></Query>

This makes the SPDataSource appear as follows:

	<DataSources>
		<SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" selectcommand="&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;And&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;servicestatus&quot; /&gt;&lt;Value Type=&quot;Choice&quot;&gt;{Service}&lt;/Value&gt;&lt;/Eq&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;TributeStatus&quot; /&gt;&lt;Value Type=&quot;Choice&quot;&gt;Approved&lt;/Value&gt;&lt;/Eq&gt;&lt;/And&gt;&lt;/Where&gt;&lt;OrderBy&gt;&lt;FieldRef Name=&quot;fullname&quot; Ascending=&quot;True&quot; /&gt;&lt;/OrderBy&gt;&lt;/Query&gt;&lt;/View&gt;" id="Veterans1"><SelectParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/><WebPartPages:DataFormParameter Name="Directory" ParameterKey="Directory" PropertyName="ParameterValues" DefaultValue="B"/></SelectParameters><DeleteParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/></DeleteParameters><UpdateParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/></UpdateParameters><InsertParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="BEAC53BD-E139-46C9-8517-E21891D9AA3B"/></InsertParameters></SharePoint:SPDataSource>
	</DataSources>

Published by Chris Quick

I have been a developer of web based solutions since early 2001 delivering solutions to a wide array of organizations using ASP, ASP.NET and SharePoint. I was introduced to SharePoint in 2003 when the consulting firm I worked for at the time introduced it into the workplace. I currently work as a Senior Consultant with Artis Consulting, developing solutions for a wide variety of business problems. The goal of this blog is to share my discoveries developing solutions using a wide array of technologies. I welcome your comments and feedback to any post -- and I welcome suggestions for future topics.

3 thoughts on “Update CAML Query for SharePoint Data View Web Part”

  1. Hi Chris,
    I’m having a hard time setting the dynamic property in the CAML query.

    What I need to do is to achieve DataView List box cascade filtering, so I am trying to pass the selected option from one DataView to the next so it filters its contents.

    Do you know how to break this?.
    Thank you in advance.

    Reply

    1. Matt,

      I use Notepad++ as well, especially when dealing with a lot of XML. If I have it available, I also like to use Visual Studio for modifying XML as it can also provide some intellisense support, especially around XSLT.

      The best thing about Notepad++ is the price — can’t beat free.

      Reply

Leave a reply to Chris Quick Cancel reply