Archive for the ‘SharePoint’ Category

Sorting and filtering problem in DataFormWebPart if used with SqlDataSource


Hi everyone. This is my first blog on WP and I would like to share my experience about the sorting and filtering problem in the Data Form Web Part (DFWP) when it is used with the SqlDataSource (SDS) control. Last week, I used SharePoint Designer (SPD) to add new content page to a site and put a DFWP and a SDS control together. I enabled the sorting and filtering function (basic table layout) on the web part and thought it should work for me but not. The page run just fine but the sorting and filtering did not take any effect. I tried to find a workaround over the internet but I was not lucky. Seemed that there is no solution or workaround for this problem available out there. I tried some debugging techniques and figured that the sort and filter expression have not been passed to the SqlDataSourceView instance associated with the SDS. The DFWP has not sent these piece of information to the SDS thus I decided to write a custom web part extended the built-in DFWP but eventually I came up with creating a custom SDS as I realized that it is not easy to leverage the visual design capability for the custom web part in SPD. It cost me couple of days to create the custom SDS control. It has been tested with the DFWP and works like a charm.

You can get it here. It is actually a .zip file but was renamed to .doc to be able to upload to WP. Thus you need to rename it back to .zip before decompressing it. The package includes an assembly named vle4.sharepoint in the bin folder and a sample.aspx file.
The assembly consists of a custom data source control also named SqlDataSource located in the namespace vle4.sharepoint.webcontrols so you can use it the same way as the built-in SDS control. Before you can do that you would need to install and register it as follows:

  • Install the assembly – there are two options
    • Partial trust: copy the assembly to the bin of the SharePoint application; e.g this is the normal path to the bin directory of the application on port 80 C:\Inetpub\wwwroot\wss\VirtualDirectories\80\bin
    • Full trust: install the assembly into GAC by dragging-n-dropping it into C:\windows\assembly directory or by using gacutil tool normally found in C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin
  • Register the control as safe control by copy-paste the following line of code to section in the web config file of the sharepoint app on port 80 for example.
    <SafeControl Assembly=”VLE4.SharePoint, Version=, Culture=neutral, PublicKeyToken=08b8d189bec44098″ Namespace=”VLE4.SharePoint.WebControls” TypeName=”*” Safe=”True” />

To run the sample.aspx page attached you need to download the AdventureWorksLT sample database from the codeplex here

02/04/2010 NOTE: If you go with the partial trust option as mentioned above and you application is configured to use WSS_Minimal trust level (default trust level) in the web config file you will encounter the following security issue at runtime.

Request for the permission of type ‘Microsoft.SharePoint.Security.SharePointPermission, Microsoft.SharePoint.Security, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c’ failed

This is because the custom SDS requires WSS object model access. To get rid of this issue you can go with one the following approaches:

  • Change the trust level in the web.config from WSS_Minimal to WSS_Medium, or
  • Explicitly grant WSS object model access for the partially trusted code by making the following modifications to the wss_minimaltrust.config file typically found in C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\CONFIG
    • Copy-paste the following line into SecurityClasses section; <SecurityClass Name=”SharePointPermission” Description=”Microsoft.SharePoint.Security.SharePointPermission, Microsoft.SharePoint.Security, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c”/>
    • Copy-paste the following line into the PermissionSet named “SPrestricted”; <IPermission Class=”SharePointPermission” version=”1″ ObjectModel=”True” />