How to simulate the SQL WHERE IN clause in a SPQuery

Simulate a WHERE IN clause in a SPQuery

If you ever have used something like

 

in T-SQL, and wanted to mimic this functionality in the SharePoint SPQuery class, you probably have found out there is no IN statement in the CAML Query language.
So what do do? Here’s a way to simulate WHERE IN clause in a SPQuery statement.

One solution is to use a LinQ query that looks something like this:

However, this has a serious performance problem. Because the .AllItems property will do a full T-SQL select all items behind the scenes, it can get very slow if you have thousands and thousand of items in your list.
It do look elegant, however.

Another way is to build up a CAML query that does an OR statement for every item that you have in your list of values.
Essentially, you will have to build up something that looks similar to this CAML query:

 

Notice that the CAML syntax for multiple OR statements is a little bit odd. You would at first glance think that is would look like this:

But this will throw an error. You will need to have nest the OR statements within each other.

For this purpose I have written a small helper method that will build the CAML query for a given list of values in a List of strings.
You call it like this:

The first parameter is the field that you want to comapre on, the second parameter is the type (“Eq” makes most sense in this scenario), and the third parameter is of List of strings.
Here’s the code for the helper method:

Microsoft SharePoint 2007 June 2010 CU published

Today Microsoft published the June 2010 CU for SharePoint 2007. This CU also addresses the important security bulletin MS10-039. To read more about this CU, please follow these links:

MOSS 2007 KB983310

WSS 3.0 KB983311

This CU requires you to have the Service Pack 2 installed. And remember to either run the Configuratio Wizard or run this command: psconfig –cmd upgrade –inplace b2b -wait on all of your servers in the farm.

 

Here is a list of known updates for SharePoint 2010.

SharePoint: How to apply ViewStyle in schema.xml

When you are customizing a SharePoint list with a custom schema.xml file, you may also want to set the ViewStyle for your View.

You do that by adding a <ViewStyle ID="xx"/> element within the <View> element. For exampl ID 17 corresponds to the "Shaded" view style.

Here’s a table with the common ViewStyles available, as defined in the <12 HIVE>\TEMPLATE\GLOBAL\XML\VWStyles.xml

0 Basic Table
12 Boxed, no labels
13 Boxed
15 Newsletter
16 Newsletter, no lines
17 Shaded
20 Preview Pane
0 Default

Using UserMulti field in the CAML schema

This was unexpected, and not documented at all in the SharePoint SDK.

When you create a UserMulti column, you must also specify Mult=”TRUE”, otherwise it will fall back to a standard (single select) User column type.

<Field ID=”{12312312-1234-1234-1234-123412341234}”


Group=”My Group”
Type=”UserMulti”
Mult=”TRUE”

 

How to determine which databases SharePoint uses

In order to tell which databases your SharePoint 2007 / WSS 3.0  installation uses, you will have to look at multiple locations.

Content databases

Open Central Administration and go to Application Management  and then Content databases
Review for all Site Collection URLs

Shared Services Databases

Open Central Administration,  Application Management, Manage this Farm’s Shared Services
There you will find
SSP Database
and
Search Database

Configuration database

Look in the registry at:
HKLM\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\ConfigDB

SharePoint: How to select multiple default items in a choice field.

If you have a field in SharePoint that is setup as a choice field, and you want to have multiple default values, you can do the following:

Instead of selecting the Default option, select the Calculated Value and enter the items separated with semicolon and the hash sign.

Like this:

=";#Red;#Green;#Blue;#"

How to get the full URL from SPListItem in SharePoint

The SPListItem URL property does return the URL for the SPListItem including the List/Documetn Library name, but it doesn’t return the full URL including the server and site names.

To get the full URL, you can either concatenate the SPLIstItem.Web.Url and the SPListItem.URL, or extract the full URL from the SPListItem.XML data like this:

 

 

Of course, you should add the usual error handling and checking.

 

How to disable the MySite and MyLinks in SharePoint and MOSS

I was struggling with this problem, but I did found that Michael Van Cleave had the solution.

 

Here’s the quote from his page:

Go to the Central Administration Web Page
Click on the link for Shared Services Administration
     –If you have more than one SSP, select the one that is running the MySites functionality
Under "User Profiles and My Sites" click Personalization Services Permissions

Select the group you want to limit the functionality for.  More than likely you will just have NTAuthority\Authenticated Users.

In the next screen you will see a list of checkboxes,
     –To disable MySites uncheck "Create Personal Site"
     –To disable MyLinks uncheck "Use Personal Features"

 

How to fix the The type Microsoft.SharePoint.Portal.Analytics.UI.ReportViewerMessages does not implement IReportViewerMessages or could not be found error

The type Microsoft.SharePoint.Portal.Analytics.UI.ReportViewerMessages, Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c does not implement IReportViewerMessages or could not be found

 This nasty error message appeared when I published my Reporting Services local report to a custom Web Part.

I solved this be comment out the ReportViewerMessages line in appSettings section in the web.config

<appSettings>
<!–  <add key="ReportViewerMessages" value="Microsoft.SharePoint.Portal.Analytics…… /> –>

So far, I haven’t seen any issues by commenting out this entry.

 

How to fix the Failed to load expression host assembly error in a SharePoint custom solution

I was writing a custom Reporting Services local report , placed in a SharePoint Web Part, and got this error:

Failed to load expression host assembly. Details: Could not load file or assembly ‘expression_host_31f4d73d84764f4b9a7332f3673bdf0b, Version=10.8.30729.1, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. Failed to grant permission to execute. (Exception from HRESULT: 0x80131418)

This was fixed by setting the ExecuteReportInCurrentAppDomain property like so:

reportViewer1.LocalReport.ExecuteReportInCurrentAppDomain(System.Reflection.Assembly.GetExecutingAssembly().Evidence);