Update CAML Query for SharePoint Data View Web Part

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>
Advertisements

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.

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s