SharePoint: Integrate ASP.net controls with Forms – Part 4: Trimming Drop Down List results using CAML

Guest Author: Matt Bramer
http://mattbramer.blogspot.com

ACK!!! CODE!!!

I’ve been writing these posts by using a *real* no code approach, sort of. I have a very different view of what no code means, but that’s a different story altogether. However, this article I’d like to have you peer into the dark-side. I know all too well, viewing code can make your eyes bug out, but I do feel it’s necessary at some point to learn. I’ve taught myself most of the programming skills I possess today, so I know you can too. I understand trying to learn these languages on your own can be a real burden, so if you don’t understand how it works, just try it anyway. The worst that will happen is the solution will not work. There’s no shame in trying and if it does work, then you can impress your boss!

DVWP & CAML

We should probably look at how these two are related. CAML (Collaborative Application Markup Language) queries are the instructions within the DVWP or SPDatasource that controls what data is retrieved from the database. The CAML query is hidden from you unless you know what you are looking for. Here’s a sample line of code that contains a CAML query:

<SharePoint:SPDataSource runat="server" DataSourceMode="List" SelectCommand="&lt;View&gt;&lt;/View&gt;" UseInternalName="True" ID="dataformwebpart2">

Within this line of code is a SelectCommand. This command combined with the CAML: "<View></View>" will control what information is displayed to us in the browser. I wont get into all of the details here, but I thought it would be important to show you what’s happening under the hood.

Okay CAML, I sort of get it. Now what?

What we want to do is trim the results of the Drop Down List to only show what items have been created by the currently logged in user. To do that, we need to update the SelectCommand for our SPDatasource with our own CAML. What I like to do is search for the SelectCommand by clicking into the Code View window, and then clicking on Edit, Find. Remember to click into the code view at the top of the page.

If you’ve followed along from Step 1, then the first SelectCommand that you’ll find is the one we’ll need to update. The code should look similar to this:

<SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" selectcommand="&lt;View&gt;&lt;/View&gt;" id="spdatasource1">
	<SelectParameters>
		<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>
	</SelectParameters>
	<DeleteParameters>
		<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>
	</DeleteParameters>
	<UpdateParameters>
		<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>
	</UpdateParameters>
	<InsertParameters>
		<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>
	</InsertParameters>
</SharePoint:SPDataSource>

We’ll need to only update the SelectCommand with our custom CAML query:

<View><Query><Where><Eq><FieldRef
Name="Author"/><Value
Type="Integer"><UserID/></Value></Eq></Where></Query></View>>

The final result should look similar to this:

<SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" selectcommand="&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;Author&quot;/&gt;&lt;Value Type=&quot;Integer&quot;&gt;&lt;UserID/&gt;&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;" id="spdatasource1">
	<SelectParameters>
		<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>
	</SelectParameters>
	<DeleteParameters>
		<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>
	</DeleteParameters>
	<UpdateParameters>
		<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>
	</UpdateParameters>
	<InsertParameters>
		<asp:Parameter Name="ListID" DefaultValue="{D3CB17ED-B351-4463-94E5-9C4863A3CEA3}"/>
	</InsertParameters>
</SharePoint:SPDataSource>

Now would be a good time to save your work! When we view the page now, all you will see in the Drop Down is the documents you’ve created. When a different user views this page, the same will hold true. They’ll only view the documents that they’ve created. With this tiny piece of CAML, you can see how easy it can be to change the interaction within the page. Don’t lock yourself into a box either, CAML will allow you to filter anything within the list. So, if I wanted to, I could change the CAML to retrieve only the documents I’ve created and also if the DocCategory equals: I’ve been slacking. Try to be as creative as you can, I think you’ll be impressed with what you can do with this.

CAML is tough to learn. Is there an easier way?

Absolutely! There are several ways of learning CAML. First you *could* decipher the official documentation for CAML on MSDN. Or you can try out these 2 different approaches. First up is to crack open your Document Library and create a view. Let’s filter this library for all Docs created by [Me] AND when the DocCategory equals: I’ve been slacking.

Once you’ve created this view, you’ll notice in SPD, the view appears as an .aspx page. If it doesn’t, refresh SPD by pressing F5 and it’ll be there.

We need to open this page and convert this Web Part into an XSLT View. I do that by right clicking on the Web Part and selecting; you guessed it: Convert To XSLT Data View. Once this has been converted, we can search through the code for our SelectCommand. Yep, we just used SharePoint Designer as a CAML code generator. Pretty cool eh? I use this approach when I know my filters are going to be more complex than normal. I can take it as far as I can before I have to tweak it manually. So that’s one way to start learning CAML.

Next up is to use the free CAML Builders that are on the internet. These two are the best I’ve found: U2U CAML Query Builder & Stramit CAML Viewer. These work a little differently than the first method, but you can build your code all the same. I suggest downloading both of them and experimenting with the capabilities of both. I’m still learning the nuances of CAML myself, so I always use these different methods to generate my code. Once you’ve generated the code, learning the language will take you to the next level and allow you to produce some very cool results.

You’ll also notice when you use these two tools, the code is in this format:

<View>
   <Query>
      <Where>
         <Eq>
            <FieldRef Name="Author"/>
            <Value Type="Integer">
               <UserID/>
            </Value>
         </Eq>
      </Where>
   </Query>
</View>

You’ll need to escape this code in order to work well with your DVWP or SPDatasource. I’ve wrote a PowerShell script to do just that or you can use the XML Escape Tool. Both of these will convert all of the characters that aren’t allowed in the SelectCommand into characters that are allowed. I’ve listed them below for you:

Note: The CAML query cannot contain any white space. The code must also be in one continuous line. It sounds like a lot of steps, but once you get the hang of it, you’ll be flying through this stuff. Post a comment and let me know which method you like the best.

What’s next?

This article wasn’t supposed to be this long, but in the end, I felt it was necessary. Hopefully it was clear enough to follow. There’s still one more tweak I’d like to show you. With the next article, I’ll show you how to force a selection within the Drop Down List. This will make the page a bit more user friendly, so that’s always worth the extra effort. I promise, we’ll jump out of code view and start clicking around more: Screenshots and all… Are you still interested?

Guest Author: Matt Bramer
http://mattbramer.blogspot.com

Matt Bramer works as a Systems Admin in the construction industry.  He has been working with Search Server Express since 2008 and frequently blogs about his findings at http://mattbramer.blogspot.com.  Matt often posts to Stump the Panel and likes to tweet about his IT projects on Twitter (@iOnline247).