CAML Query and the SharePoint List Views Filtering

I have a list which resembles a Tasks List with an additional column ‘TaskCreated’ – a DateTime column.

I wanted to query the list with the following filters :

  1. Get all the items with status “Not Started”
  2. Get all the items with status “In Progress”
  3. Get all the items with status “Completed” AND the ‘Task Created’ is within the given number of days.
  4. Get all the items with status ‘Deferred” AND the ‘TaskCreated’ is within the given number of days.

I used the following CAML query to fetch the results :

oQuery.Query = String.Format(
“<Where>” +
“<Or>” +
“<Or>” +
“<Eq>” +
“<FieldRef Name=’Status’ />” +
“<Value Type=’Choice’>Not Started</Value>” +
“</Eq>” +
“<Eq>” +
“<FieldRef Name=’Status’ />” +
“<Value Type=’Choice’>In Progress</Value>” +
“</Eq>” +
“</Or>” +
“<Or>” +
“<And>” +
“<Eq>” +
“<FieldRef Name=’Status’ />” +
“<Value Type=’Choice’>Completed</Value>” +
“</Eq>” +
“<Geq>” +
“<FieldRef Name=’TaskCreated’/>” +
“<Value Type=’DateTime’ StorageTZ=’TRUE’>{0}</Value>” +
“</Geq>” +
“</And>” +
“<And>” +
“<Eq>” +
“<FieldRef Name=’Status’ />” +
“<Value Type=’Choice’>Deferred</Value>” +
“</Eq>” +
“<Geq>” +
“<FieldRef Name=’TaskCreated’/>” +
“<Value Type=’DateTime’ StorageTZ=’TRUE’>{1}</Value>” +
“</Geq>” +
“</And>” +
“</Or>” +
“</Or>” +
“</Where>”,
SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.UtcNow.AddDays(Completed)),
SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.UtcNow.AddDays(Failed)));

I was curious to know how SharePoint would have done if I had created a view out of it.

This is how it looks like . Since there are nested operators it is very difficult to relate to the the filters displayed ….If you have a complex view then you are better of by creating a custom view using code and the CAML rather than relying on the view creation UI of SharePoint.

image

The view as seen by Stramit CAML Viewer :

<Query>
<Where>
<Or>
<Or>
<Eq>
<FieldRef Name=”Status” />
<Value Type=”Choice”>Not Started</Value>
</Eq>
<Eq>
<FieldRef Name=”Status” />
<Value Type=”Choice”>In Progress</Value>
</Eq>
</Or>
<Or>
<And>
<Eq>
<FieldRef Name=”Status” />
<Value Type=”Choice”>Completed</Value>
</Eq>
<Geq>
<FieldRef Name=”TaskCreated” />
<Value Type=”DateTime” StorageTZ=”TRUE”>2009-11-16T22:05:59Z</Value>
</Geq>
</And>
<And>
<Eq>
<FieldRef Name=”Status” />
<Value Type=”Choice”>Deferred</Value>
</Eq>
<Geq>
<FieldRef Name=”TaskCreated” />
<Value Type=”DateTime” StorageTZ=”TRUE”>2009-11-16T22:05:59Z</Value>
</Geq>
</And>
</Or>
</Or>
</Where>
</Query>

SharePoint Content Approval – Never ending workflow

The Content Approval feature permits you to create a list or a document library where items or files that are submitted by users are not visible to other site users until the items or the files are approved by a user (such as the site administrator) who belongs to a site group that has the Manage Lists right.

When you add a new item or when you modify an existing item in a list or a document library after you enable content approval for that list or document library, the item is marked as Pending. Pending items are only visible to the user who created or modified the item and to users with the Manage Lists right. Pending items are not visible to users until the item is approved. When an item is approved, it is marked as Approved and is visible to all users. When an item is rejected, it is marked as Rejected, and is returned to the user who created it.

 

Use a workflow to manage content approval for a library

You can use a workflow to manage content approval (also called moderation) in libraries for which content approval is required.

Enabling OOTB Approval workflow for a list:

image

 

image

The Problem : 

In a SharePoint list after you are done with enabling content approval and creating an OOTB approval workflow, you might experience a strange behavior during the approval process.

The workflow can run endlessly. Every time the workflow task is approved, it triggers a Item Updated event which in turn starts another instance of the workflow…So the item remains pending even after the approval ! (After the workflow instance completes another instance is started because of the item updated event triggered by the workflow approval.

This happens in the list. The reason for the difference in behavior is the way the workflow setup is done for the list and document library. We will come to the settings in the library shortly.

The workaround Solution:

The solution is to handle the ItemUpdated event and update the Moderation status programmatically after turning off the event. The code is listed below.

 

class UpdateWorkflowStatus : SPItemEventReceiver
   {

       public override void ItemUpdating(SPItemEventProperties properties)
       {
           EventLog m_EventLog = new EventLog("");
           m_EventLog.Source = "Approval Workflow Status Update";

           try
           {
               base.ItemUpdating(properties);
               if (properties.AfterProperties["_ModerationStatus"] != null && properties.ListItem["_ModerationStatus"] != null)
               {
                   if(properties.AfterProperties["_ModerationStatus"].ToString() != properties.ListItem["_ModerationStatus"].ToString())
                   {
                       this.DisableEventFiring();

                       properties.Status = SPEventReceiverStatus.CancelNoError;
                       properties.ListItem["_ModerationStatus"] = properties.AfterProperties["_ModerationStatus"].ToString();
                       properties.ListItem.Update();

                       this.EnableEventFiring();
                   }
               }
           }
           catch (Exception ex)
           {
               m_EventLog.WriteEntry("Workflow Status Update Event Handler Error : " + ex.Message,EventLogEntryType.Error);
           }


       }

The reason why it won’t happen in a document library is that the way the settings are done. In a document library for the content approval minor and major version has to be enabled. And any edit that happens becomes a minor version and a workflow gets triggered only when the major version is published.

image

image

BUT ! there is a way to reproduce it in document library as well. You should create the workflow first and then enable content approval, you will end up in the same problem that I explained for the list !!!

SharePoint Document Library and List – File Upload

There are different ways you can add documents to a doc library or to list item as an attachment. You could use object model, built-in web service, custom web service that uses object model, or HTTP PUT method.

In this post I will be stepping through them in detail. Let us take the object model approach first. For the sake of simplicity I have created a simple windows forms app that will push documents in to the document library and list

 

Adding documents to a Document Library and updating document attributes :

   1: private void button1_Click(object sender, EventArgs e)

   2:         {

   3:             if (openFileDialog1.ShowDialog() == DialogResult.OK)

   4:             {

   5:                 try

   6:                 {

   7:                     FileStream myStream;

   8:                     string fullName;

   9:                     if ((myStream = (FileStream)openFileDialog1.OpenFile()) != null)

  10:                     {

  11:                         fullName = openFileDialog1.FileName;

  12:                         FileInfo myFileInfo = new FileInfo(fullName);

  13:                         using (myStream)

  14:                         {

  15:                             using (SPSite oSite = new SPSite("http://vslearnmoss/sites/FileUpload"))

  16:                             {

  17:                                 using (SPWeb oWeb = oSite.OpenWeb())

  18:                                 {

  19:                                     SPFolderCollection oFolders = oWeb.GetFolder("MyDocuments").SubFolders;

  20:                                     SPFolder oFolder = oFolders["Folder1"];

  21:                                     SPFileCollection oFileColl = oFolder.Files;

  22:                                     Hashtable oDocAttribs = new Hashtable { { "Field1", "Value1" }, { "Field2", "Value2" } };

  23:                                     SPFile oFile = oFileColl.Add(myFileInfo.Name, myStream, oDocAttribs, true);

  24:                                 }

  25:                             }

  26:  

  27:                         }

  28:                     }

  29:                 }

  30:                 catch (Exception ex)

  31:                 {

  32:                     MessageBox.Show("Error: " + ex.Message);

  33:                 }

  34:             }

  35:  

  36:         }

Adding document attachments to a list item  :

   1: private void button1_Click(object sender, EventArgs e)

   2:         {

   3:             if (openFileDialog1.ShowDialog() == DialogResult.OK)

   4:             {

   5:                 try

   6:                 {                    

   7:                     string fullName;

   8:                     fullName = openFileDialog1.FileName;

   9:                     FileInfo myFileInfo = new FileInfo(fullName);

  10:                     byte[] fileData = System.IO.File.ReadAllBytes(fullName);

  11:  

  12:                     using (SPSite oSite = new SPSite("http://vslearnmoss/sites/FileUpload"))

  13:                     {

  14:                         using (SPWeb oWeb = oSite.OpenWeb())

  15:                         {

  16:                             SPList oList = oWeb.Lists["MyList"];

  17:                             SPQuery oQuery = new SPQuery { Query = "0" };

  18:                             SPListItem oItem = oList.GetItems(oQuery).Add();

  19:                             oItem["Title"] = textBox1.Text;

  20:                             SPAttachmentCollection attachments = oItem.Attachments;

  21:                             attachments.Add(myFileInfo.Name, fileData);

  22:                             oItem.Update();

  23:                         }

  24:                     }

  25:                 }

  26:                 catch (Exception ex)

  27:                 {

  28:                     MessageBox.Show("Error: " + ex.Message);

  29:                 }

  30:  

  31:             }

  32:         }

One thing to note when accessing the list for creating a new list item is to be mindful that when SPList.Items.Add is called it fetches all the items before creating a new item, one way to avoid is to make a blank query that fetches zero results and add a new list item to the collection. [Thanks to Rob Garret]

SPQuery oQuery = new SPQuery { Query = "0" };

SPListItem oItem = oList.GetItems(oQuery).Add();

 

You can look for more info about adding list item efficiently in this article by Rob Garret.

In the next part of this article, I will show you how to utilize web services or HTTP PUT method to push documents into the SharePoint document library.

File Not Found – Assembly Binding Error

 

I am sure many of you might have come across the “File Not Found” error related to the Assembly Binding.

 

FileNotFound Error

 

The best way to diagnose is to use the Fusion Log Assembly Viewer. The tool FUSLOGVW.exe is located at C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin

You can also access this tool from your Visual Studio Command Prompt.

CommandPrompt

Fusion Assembly Log Viewer Settings:

FusLogVw Settings

  • Set your Log Settings as per the screenshot above. Set your own custom log path.
  • Close the Fusion Assembly Log Viewer
  • Once you are done with those settings make sure you delete all the Temporary ASP.NET Files from the “Windows\Microsoft.NET\Framework\v2.0.50727” folder.
  • Do an IISRESET
  • Start the Fusion Assembly Log Viewer
  • Open your ASP.Net web application and recreate the erroring scenario.
  • Click Refresh button in the Log Viewer
  • Look for App_Web_error.aspx entry..and move up in the list you will find the dlls that the application is trying to bind. Double click it and you will find the details of the binding error.

FusionLog Error Output

Ref: http://msdn.microsoft.com/en-us/library/e74a18c4.aspx

Unable to Open a CHM file – You get a Unable to display web page error

When you try to open CHM files you might encounter issues. This is primarily due to 2 reasons.

 

  1. Security
  2. File path contains certain characters like #, ?, &, +

 

To overcome the security issue you have to follow the steps below..

  1. When you open the file you will be prompted with a security warning which you can dismiss by un checking the “Always ask…” checkbox and clicking “Open” button.
  2. Right Click the file and select properties from context menu. Click unblock button at the bottom and click Apply, OK

image

image

 

If you still get the error “This program cannot display the webpage” then verify if the path to the file has any invalid characters. For example a  path “C:\My C# Project Documentation\MyHelpFile.chm” will not work !

 

Change your path to “C:\My CSharp Project Documentation\MyHelpFile.chm” and it will work.

Error when trying to delete a master page, page layout from SharePoint master pages gallery

 

You might receive an error “This item cannot be deleted because it is still referenced by other pages”. I was searching through the web for a solution then I came across this site. This site gives you a neat little hack !

http://www.katriendg.com/aboutdotnet/2007_1_cannot_delete_page_layout.aspx

 

Reproducing the text from the original site.

Today I ran into an error while creating page layouts using a feature. I made a mistake in the XML that provisions the files to install with the feature for my page layouts. My problem was an incorrect value in the attribute for the content type ID linked to my installed page layout.

After realizing my mistake and correcting it, uninstalling the feature and reinstalling it again the original incorrect page layout was still installed. This seems to be normal in SharePoint. Even when deactivating and uninstalling a page layouts feature these pages still stay in the "Master Page and Page Layout Gallery". I still have to find the right documentation on why this happens but I imagine it has to do with ghosting of the pages.

Now back to my problem. Since the incorrect page layout was still installed and giving me error messages I tried to delete it manually from the gallery. This gave me the following error message:

"This item cannot be deleted because it is still referenced by other pages"

Even if this is not the case (there was no page using this page layout), the problem is mentioned in the following KB article:

http://support.microsoft.com/?kbid=926812

The KB proposes to change the properties of the master page to Hidden. This workaround did not solve my problem since everytime I tried to access the page layout’s Properties screen I got another error message due to my mistake discussed above.

There was however one workaround that solved the problem:

  1. Create a new sub-folder in the master page gallery
  2. Move the page layout to the new folder (I did this by using SharePoint Designer)
  3. In the MOSS interface, delete the complete folder by using the dropdown menu.

Yep, my page layout is finally gone from the gallery!

While my problem was specific to a page layout I think the same solution will work with master pages as well.

How to overwrite SharePoint master page using a feature

I ran into a scenario where I had to overwrite an already existing master page using a feature.

In my elements.xml I had an entry for the custom master page like the following

   
   1: <Module Name="OSGMasterPages" Url="_catalogs/masterpage"   Path="MasterPages" RootWebOnly="TRUE">

   2: <File Url="default.master"   Type="GhostableInLibrary" >


I was unable to overwrite then found that the file has an additional attribute to specify what to do if the file already exists.

   
   1: <File   IgnoreIfAlreadyExists = "TRUE" | "FALSE"   

   2:    Name = "Text"   

   3:    NavBarHome = "TRUE" | "FALSE"  

   4:    Path = "Text"   

   5:    Type = "Text"   

   6:    Url = "Text >   

   7: </File>

IgnoreIfAlreadyExists – Optional Boolean. TRUE to provision the view even if the file aready exists at the specified URL; otherwise, FALSE.

I tried both TRUE and FALSE..no luck.

To add more twist to the problem I also got an error

error CS0030: Cannot convert type Microsoft.SharePoint.WebControls.EncodedLiteral’ to ‘System.Web.UI.IAttributeAccessor’

I tried to manually do the master page upload and then changed the site collection master page reference, it worked without any problem. Then I hit google found this site.

Investigated the custom master page and found lot of “_designer” variables which have been added through the SPD. If I remove this variables then the master page applied successfully and the site is alive.

Since this master page was also edited using the SPD i eagerly opened the page and checked..no luck… all the “designer “ variables looks like already commented out.

Note: Got a feedback from an anonymous user that if you go ahead and remove those variables the solution will work ! I am yet to try that

Thanks to the MSDN forums, Later I found that there is no way you can overwrite an existing file that is already provisioned by a feature. It has to be done programmatically !  and btw the IgnoreIfAlreadyExists attribute just lets the feature not error out if it hits that module and the file exists.

Feature event receiver is the right place to manage your provisioning of the updated master page. Following are the steps:

  • Find out if the master page that you want to replace is already checked out.
  • If yes then do a check in.
  • From the feature folder structure query for the master page file.
  • Add this file to the site collection master page gallery
  • Check in the file.
  • publish and approve the file.

The following is the sample code that does the same. This piece of code should be part of the Feature Activated Handler. You can modify the code so that you have the master page name parameterized.

       1: // Here we are trying to overwrite the default.master   

       2: oFile = oCurrentWeb.GetFile("_catalogs/masterpage/default.master");   

       3: oFolder = oCurrentWeb.Lists["Master Page Gallery"].RootFolder;   

       4: // Check In the file   

       5: if (oFile.CheckOutStatus != SPFile.SPCheckOutStatus.None)   

       6: oFile.CheckIn("auto checkin");   

       7: // Check Out the file for edit.   

       8: oFile.CheckOut();   

       9: string directory = oFeature.Definition.RootDirectory;  

      10: directory += @"\masterpages";  

      11: // Get the master page name from the feature properties as defined in the elements.xml  

      12: SPFeatureProperty oPropertyName = properties.Feature.Properties["MasterPageName"]; 

      13:  

      14: // Get the file names from the specified directory matching the feature property value.  

      15: string[] templates = Directory.GetFiles(directory, oPropertyName.Value, System.IO.SearchOption.TopDirectoryOnly);  

      16: // templates should have all the matching file names. In this instance it whould be only one file name.  

      17: string MasterPageFile = templates[0];  

      18: FileInfo fileInfo = new FileInfo(MasterPageFile);  

      19: byte[] byteArr = System.IO.File.ReadAllBytes(MasterPageFile);  

      20: oFolder.Files.Add(fileInfo.Name, byteArr, true);  

      21: oFile.Update();  

      22: oFile.CheckIn("Check in");  

      23: oFile.Publish("Published");  

      24: oFile.Approve("Approved");

If your site collections is referencing this master page then on feature activation the master page gets updated and it gets reflected on the site collection sites which uses this master page.

Follow

Get every new post delivered to your Inbox.