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” />



2 Responses to “Sorting and filtering problem in DataFormWebPart if used with SqlDataSource”

  1. Vijay Says:

    Hi vle4 – This sounds great. You seem to be the only person to have come up with a solution to an old problem. However I am no expert and do not have access to our SharePoint server, only front-end owner rights. Do you have any solutions or advice? Are you still working on a web part solution?

    • vle4 Says:

      Hi Vijay,

      You need to have access to the WFE servers to be able to install the component (assembly) as well as modify the web.config files. We can automate these steps by packaging them as a sharepoint solution (.wsp file) so that people can use stsadm tool and/or central addministion site to deploy it into the farm. This has both pros and cons.

      Pros – It (.wsp file) will simplify the installation process. This is the way to go if we trust the solution (wsp) provider.

      Cons – We don’t know what it is going to install into the farm. So it might be a risk to the system unless we trust the provider of solution.

      I jwanted people to know exactly what they are going to make changes to their system by installing this solution so I did not make it a .wsp file.

      Even with the wsp file you need to be a farm admin to be able to install it into your farm.

      Originally I developed it as a custom DFWP, it worked for me but I realized that it did not support design-time features as the standard one does. This is a chanllenge when developing custom controls for use in SharePoint Desginer. That was reason why I stopped it and come with the current solution.

      Hope this helps.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: