Dev Notes

Notes on Development with Microsoft Technologies

Update CAML Query for SharePoint Data View Web Part

3 Comments

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

Author: 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 began working with MOSS 2007 as soon as Microsoft released the RTM version in November 2006. The platform was implemented at the organization I worked for in 2007 and went live in March of that year. I was tasked with the administration and ongoing development of the platform. I currently work as a SharePoint Architect with Artis Consulting, developing solutions for a wide variety of business problems. The goal of this blog is to share my discoveries developing solutions with SharePoint. 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.

  2. I typically export these DVWP’s out into a good text editor(Notepad++) and do my updates within that. What do you use?

    • 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