SharePoint Development Blog

BDC Refresh causes null pointer exception

2008-12-03T16:20:00.002-05:00

The current status:

Executing a BDC refresh in specific cirsumstances causes a null pointer exception to be thrown by SharePoint.

Scenario #1:

Add a BDC column without related columns to a list
Add row(s) to the list and assign values to the BDC field
Update the BDC column to use additional related fields
Refresh the BDC column in the list

Result:
After clicking the refresh button and 'OK' on the next page, the next page displays a progress bar, then displays the following error: "An error occurred while retrieving data from . Administrators, see the server log for more information."

The log contains the following line:
Exception thrown while synchronizing business data System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.SharePoint.Portal.WebControls.BusinessDataSynchronizerJob.UpdateListItem(SPListItem item, BusinessDataField bizDataField, LobSystemInstance sysinst, Entity entity, View view, String[] secondaryBdcFieldNames, String[] secondaryWssFieldNames)
at Microsoft.SharePoint.Portal.WebControls.BusinessDataSynchronizerJob.DoWork()

Scenario #2

Add a BDC column with related fields to a list
Add row(s) to the list where the related fields have non-empty values
Refresh the BDC column in the list

Result: Works fine

Scenario #3:

Add a BDC column with related fields to a list
Add row(s) to the list where the related fields have empty or null values
Refresh the BDC column in the list

Result: Throws null pointer exception as described above.

I'll contend that this is a bug for the following reason: reflecting on the Microsoft.SharePoint.Portal.WebControls.BusinessDataSynchronizerJob class that is throwing the exception yields a segment of code that checks the current value of the related field to the updated value retrieved from the BDC LineOfBusiness. If the two values are different, then it sets the item's value for that related field to the updated BDC value.

.NET Reflector gives the following code snippets that are useful:


Label_0144:
    str = item[secondaryWssFieldNames[num2]].ToString();
    str2 = Convert.ToString(instance.GetFormatted(field), CultureInfo.InvariantCulture);
    if (!(str != str2))
    {
        goto Label_00F6;
    }
    goto Label_0113;
Label_0113:
    flag = true;
    item[secondaryWssFieldNames[num2]] = str2;
    goto Label_00F6;

Label_001D:
    if (flag)
    {
        item.Update();
    }

Label_0144 creates str and str2 to compare the original (str) value and the updated (str2) value. If they are different, then Label_0113 sets the item's value to the updated value and sets Flag to true. Label001D checks the flag value and, if true, updates the item. This should only update items that have actually been updated.

(by the way, this is all SP1 code, post-Infrastructure update)

It seems a viable candidate for this null pointer exception would be the line str = item[secondaryWssFieldNames[num2]].ToString();. If the item's value is null, then the ToString() would obviously fail.

A quick PowerShell script shows that following scenario #1 above yields a null value for the related column. This is indeed a valid candidate for this issue.

If this is the case, has this been brought to Microsoft's attention? Is this addressed in any hotfix or update for SharePoint?

Disable BDC Refresh icon

2008-12-03T14:44:00.004-05:00

To disable (properly, to not render) the BDC Refresh "hurricane" icon (as some have called it), simply edit the fldtypes_spsbizdata.xml file and find the  XML comment. Below this XML comment, remove/comment the following lines:

<HTML>&nbsp;<![CDATA[<a style="padding-left:2px;padding-right:12px" href="]]></HTML>
<HttpVDir/>
<HTML><![CDATA[/_layouts/BusinessDataSynchronizer.aspx?ListId=]]></HTML>
<ListProperty Select='Name'/>
<HTML><![CDATA[&ColumnName=]]></HTML>     
<Property Select='Name'/>
<HTML><![CDATA[" OnClick="GoToLink(this);return false;">]]></HTML>
<HTML><![CDATA[<img border="0" src="/_layouts/images/bdupdate.gif" alt="$Resources:spscore,BusinessDataField_UpdateImageAlt;"/></a>]]></HTML>

Save the file, then perform an IIS reset. After reset, the BDC Refresh icon will be gone. Note that this changes all BDC columns in the site.

While you're editing this file, go ahead and fix the BDC CSS styling bug.

Bug in fldtypes_spsbizdata.xml

2008-11-12T17:52:00.003-05:00

The css styling for displaying tables inline for BDC columns is not correct into the fldtypes_spsbizdata.xml file. To correct, simply replace "display=inline" with "display:inline;" and reset IIS.

In some environments, this does not cause a problem. However in some situations (perhaps with custom css and master page styling), if the BDC column is used to group a view, the column header does not render properly.

Group By Date Field off by one day

2007-08-08T12:12:00.001-04:00

I've recently noticed that, when creating a list view that groups by a date field, the date field can be off by one day.

The issue comes when grouping by a date field and setting the default behavior to Collapsed.

It doesn't matter that the Expires date is just a date and not a Date/Time: the same bug appears with fields using both properties (with any time of the day).

The bug does not appear when converting a view to an XSLT view, although you lose much of the customizations and nifty interaction you get with a SharePoint DataView.

If I can get Windows Live Writer configured to upload images, I'll attach screenshots.

Differences in handling CR/LF differences between InfoPath and SharePoint

2007-08-06T15:31:00.001-04:00

Although InfoPath claims to handle CR, LF, and CR/LF the same, it does not follow the conventions of other Microsoft products when it comes to generating new line characters. SharePoint, like most (if not all) Windows applications, uses the CR/LF combination (0x0D, 0x0A) to denote a new line. This is apparent when viewing the raw data contained within a Multiline Text field.

If you are creating an InfoPath form that submits to a SharePoint library, you can set specific fields within the InfoPath form to be promoted to the SharePoint list. You can also promote a field within a repeating group. When you promote this field, you have the option of promoting the first or last item in the list, the number of items in the group, or the entire collection of that field merged into one field.

When choose the fourth option, a Multiline text field is generated by InfoPath in the SharePoint library. However, when submitting, the merged fields are separated by only a Line Feed character (0x0A)!

This issue will come into play when you want to perform operations on the SharePoint list through the SharePoint object model. Dot-matching, for instance, may not work because the .NET Regex processor will use CR/LF as its newline character(s) and won't match the single LF character. SharePoint Designer may also have difficulty if you want to use substring to parse items from the merged list.

The only fix known so far is to simply remember that merged fields that InfoPath uses will have only the LF character, while other SharePoint-created multiline text fields will use CR/LF.

Blogging with SharePoint 2007 and Windows Live Writer

2007-07-26T10:02:00.001-04:00

I'm sure there are a handful of posting out there about blogging on SharePoint 2007 with Windows Live Writer, which is what I'm using now to publish to BlogSpot. Microsoft's made it very easy to publish blog posts to SharePoint, including automatic image storage. I haven't seen any blog posting about it yet, so I'll offer a simple one of my own.

Since the blog's Posts is a list, the images are stored as attachments to that list item. They would be stored in the /Lists/Posts/Attachments/<Post Id> directory structure for easy reference. The file names are generated as image.png, image_1.png, etc.

You can also publish with Word 2007 (yet to see if Word 2003 can do the trick), but the images are saved into the Photos. The file names are generated with the date and the name of the posting (072607_1337_Testing1.png, 072607_1337_Testing2.png, etc). Not sure if 1337 refers to the time somehow (did this in the morning, but it could be GMT time) or it's Leetspeak...

Customizing the Filter Query in a List View

2007-07-25T14:38:00.001-04:00

Filtering a list view offers a efficient way of displaying data within a list or document library. One of its main detractors, however, is that you're limited to what the SharePoint interface allows you to filter by. You can create complex filters using AND and OR conjunctions, but there's one main catch: the conjunctions are evaluated in order and doesn't allow any sort of order to be forced.

An Example:

Suppose I had two fields, Fruit and Quantity, in my SharePoint list and I wanted to filter a view so that I would show all combinations where I'd get a stomach ache: that would be, I guess, when Fruit = Apples and Quantity > 2, or when Fruit = Oranges and Quantity > 4.

In pseudo code, it would appears as such:

(Fruit = Apples && Quantity > 2) || (Fruit = Oranges && Quantity > 4)

The problem is that the query will not be constructed in that way. Instead, the logic is captured as such:

(((Fruit = Apples && Quantity > 4) || Fruit = Oranges) && Quantity > 8).

Situations, such as having six apples, should appear in my new list, but don't because the Quantity > 8 overrides that for the apples.

The Fix

This is where SharePoint Designer comes to the rescue. You can build the CAML query for the 'Where' clause appropriately to capture the logic that you are looking for.

The original Where clause will look something like this:

<Where>
<And>
    <Or>
      <And>
        <Eq><FieldRef Name="Fruit"/><Value Type="Text">Apples</Value></Eq>
        <Gt><FieldRef Name="Quantity"/><Value Type="Number">4</Value></Gt>
      </And>
      <Eq><FieldRef Name="Fruit"/><Value Type="Text">Oranges</Value></Eq>
    </Or>
    <Gt><FieldRef Name="Quantity"/><Value Type="Number">8</Value></Gt>
</And>
</Where>

My 'Where' clause will need to be changed to look like this:

<Where>
<Or>
    <And>
      <Eq><FieldRef Name="Fruit"/><Value Type="Text">Apples</Value></Eq>
      <Gt><FieldRef Name="Quantity"/><Value Type="Number">4</Value></Gt>
    </And>
    <And>
      <Eq><FieldRef Name="Fruit"/><Value Type="Text">Apples</Value></Eq>
      <Gt><FieldRef Name="Quantity"/><Value Type="Number">4</Value></Gt>
    </And>
</Or>
</Where>

Once you build your query offline, you'll need to search for and replace the old query with your new one. Note that the XML is encoded as text, so '<', '>' characters are encoded as < and >, respectively. Use your favorite notepad (Notepad++, with the XML Tools, is my personal favorite because it will do this automatically) to set your query appropriately.

Final Thoughts

Note that, if you need to change something in your view (order of fields, grouping, sorting, etc), then SharePoint will rewrite your query with what it thinks is correct. In this example, it will replace your query with what it was before. You'll just need to make sure that your view is set up correctly before updating the query manually.

SharePoint Designer bugs

2007-07-19T08:53:00.001-04:00

While I'm on a blogging kick this morning, has any one else noticed how buggy SharePoint Designer tends to be? Besides the occasional crashing when editing aspx files, the tree view in the Data Source Library always gets corrupted and I can't view any of the data sources.

Just a rant

Take Care when Copying Files

2007-07-19T08:50:00.001-04:00

Copying files in SharePoint can have some unintended consequences that may not be apparent at first. Creating a copy of a file will create a link between the two files, so that, for instance, changes to one file will be reflected in the other file.

SharePoint and SharePoint Designer both can create these special linked documents, but they do it in different ways:

With SharePoint, use the Manage Copies feature in the View properties of the item, or else choose Send To > Other Location from the ECB menu. When copying files using SharePoint Manage Copies feature, the copy will have an additional option in the ECB menu: Go to Source Item. This will navigate you back to the URL of the copied file.
With Designer, you can use the Remote Web Site interface. The remote site can be on the same server, but it cannot be the same SharePoint site. When copying files using Designer, the copy will not have the "Go to Source Item" option.

The biggest annoyance: When you create a linked copy in these ways, you will create a new version of the original document. This is important if you have event handlers or workflows that kick off as a result of items updating.

If you view the file properties through Windows Explorer, for instance, you'll notice that the date/time stamp of the file doesn't change. This indicates that SharePoint internally is creating a new version of the file, but nothing beyond that.

And, in case you were wondering, this all happens whether you have versioning enabled on your system or not.

If these are not your intentions, then you have a more basic option. To copy files without creating linked copies (copying as we know it), then your best bet is to download a copy and save it to the alternative document library.

You can find more information about this in the Microsoft's SharePoint help site at http://office.microsoft.com/en-us/help/HA101181721033.aspx

InfoPath: Use SharePoint Web Services

2007-06-26T15:07:00.000-04:00

InfoPath is great at using Web Services as a data source, but it doesn't quite do the trick when it comes to using SharePoint's Web Services. Obviously, InfoPath includes the built-in ability to use SharePoint lists as data sources, but is limited to the default, sorted-by-ID view of the List. If you wanted to sort or filter the items, especially from a browser-enabled form, then you're out of luck unless you use Web Services.

Why won't InfoPath work with SharePoint Web Services? It comes down to the fact that InfoPath doesn't recognize that some web services, such as Lists.asmx's GetListItems requires strings (which InfoPath handles just fine) and XmlNodes (which InfoPath can't seem to figure out). If you try to use this web service as a data connection, you'll get error messages, such as "Root Element is Missing" or, if you try to add some XML code to one of the fields, you'll get "Element <Query> of parameter query is missing or invalid."

The solution? Create a Proxy Web Service that takes in strings representing the Xml code and create XmlNodes from them.

Here's the code to create such as proxy:


[WebMethod]
public XmlNode GetListItems(string listName, string viewID, string query, string viewFields, string rowLimit, string queryOptions, string webID)
{
  // Create an instance of the SharePoint Lists Web Service
  ListsWebService.Lists listService = GetListsServiceClient();
  // Create the XmlNodes from the given innerXml strings
  XmlNode queryElement = CreateNode("Query", query);
  XmlNode viewFieldsElement = CreateNode("ViewFields", viewFields);
  XmlNode queryOptionsElement = CreateNode("QueryOptions", queryOptions);
  // Execute the SharePoint Lists Web Service
  return listService.GetListItems(listName, viewID, queryElement, viewFieldsElement, rowLimit, queryOptionsElement, webID);
}

private ListsWebService.Lists GetListsServiceClient()
{
  ListsWebService.Lists listService = new ListsWebService.Lists();
  listService.Credentials = System.Net.CredentialCache.DefaultCredentials;
  listService.Url = SPContext.Current.Web.Url + "/_vti_bin/Lists.asmx";
  return listService;
}

private XmlNode CreateNode(string name, string innerXml)
{
  XmlDocument xmlDoc = new XmlDocument();
  XmlNode element = xmlDoc.CreateElement(name);
  element.InnerXml = innerXml;
  return element;
}

Just create a new web service with this information and follow the instructions on the proper deployment procedures in the WSS 3.0 SDK article Walkthrough: Creating a Custom Web Service. The facade method above has essentially the same method signature as the Lists.asmx web service, except that instead of XmlNode being the input types, strings are the input types.

SharePoint Designer: Use Data Sources by List Name

2007-06-21T10:45:00.000-04:00

By default, SharePoint Designer create a SPDataSource using <asp:parameter> controls. The fallback of these controls is that it references Lists (and Document Libraries) by the ID of the respective list. If you ever delete the list and recreate it with the same name, you'd have to find the new ID and change it in the SPDataSource.

An alternative is to replace the <asp:parameter> with a <webpartpages:dataformparameter> that can use the ListName instead. Note that the Display Name is used -- if you have a list with a static name of "MyList", but changed its appearance to "My List", either in SharePoint or SharePoint Designer, then you'll reference the list as "My List". The same would go for using the Lists.asmx web service (although, oddly enough, column names are referenced by their original, static name).


<SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" selectcommand="&lt;View&gt;&lt;/View&gt;">
  <SelectParameters>
    <WebPartPages:DataFormParameter Name="ListName" ParameterKey="ListName" PropertyName="ParameterValues" DefaultValue="ISDA" />
  </SelectParameters>
</SharePoint:SPDataSource>

It also creates SelectParameters, UpdateParameters, InsertParameters, and DeleteParameters by default; if you're only displaying data, then you can safely remove the other three.

I'm interested in how the selectcommand works in the SPDataSource. I'd be interested to see if any CAML can be used in it properly to filter the source data.

InfoPath: Remove Validation Popup Windows

2007-06-20T14:40:00.001-04:00

One of the most requested features of InfoPath is to remove the annoying popup boxes on required fields. The main reason for the frustration is there's no way to turn off these popup boxes and, often, they are rendered on top of other controls.

You can customize the validation message to trick InfoPath in displaying nothing. If you try to do this through InfoPath's designer, it will require a message. So, like most InfoPath tricks, we'll have to go to the source.

For each control that you would want to remove the validation popup message, you'll need to clear the "Cannot be blank" property and add a custom validation rule. Note that I've added $$$$$ as the message (you'll see why below).

Next, save your InfoPath template as Source Files (which I use for all my InfoPath templates). Then open the manifest.xml file in your favorite text editor (Notepad++ is my favorite). Find the node, which contains a collection of fields. Find the errorMessage that corresponds to what you just added. Alternatively, you can just search for $$$$$ to find it.

The fix: Erase the shortMessage (shortMessage="") and add text ("Cannot be blank" is my recommendation) to the errorMessage as its element.

Cannot be blank

Follow these steps for all your controls. You can change the control's Required properties and validation messages one by one, then edit the maniest at the very end. Doing a quick search/replace for $$$$$ will speed up the steps.

InfoPath: Conditionally Format a Hyperlink

2007-06-20T14:16:00.000-04:00

So here's the scenario: the user is presented with an InfoPath form with a dropdown menu. The menu is populated by a SharePoint list. When the user chooses an item, a hyperlink appears allowing the user to navigate directly to the item's display page. The dropdown menu should have the ID stored as the code and anything (Title, most likely) for display.

Nearly all the controls in InfoPath have the ability to be conditionally formatted. My personal favorite is to hide controls based on events or data in the current page. Unfortunately, Hyperlinks don't have this ability.

To conditionally format a hyperlink using sections:

Create a new section and size it as needed
Create a hyperlink within the section
Set the DataSource of the hyperlink to be the following:
concat("http://servername/subsites/Lists/listname/DispItem.aspx?ID=", element)
Set the Display of the hyperlink to be something useful ("Open" comes to mind)
Set the conditional formatting to hide the control whenever the dropdown list's data source element is blank

Because the section is rendered as a block-level DIV element, it creates a new line and pretty much position itself. In some situation you way want to position it yourself without relying on the section to do it for you. One situation would be to position it immediately after the dropdown list on the same line. You could do this with a table, but you're then relying on the table's column sizes to determine where the Open link goes.

To conditionally format a hyperlink directly without using a section:

Create a hyperlink wherever you want it
Set the DataSource of the hyperlink to be the following:
concat("http://servername/subsites/Lists/listname/DispItem.aspx?ID=", my:element)
Set the Display of the hyperlink to be the following:
substring("Open", 0, number(my:element * 5))
where 5 is one more than the length of the text that you want to display

The substring function will multiply the value of the element by this length to retrieve the number of characters to parse the substring by. Because the blank value is determined to be zero, substring (text, 0, 0) will yield a blank string and won't be rendered as a link. Otherwise, the ID of the item (it's minimum is one), multipled by this value, will render the link correctly.

Welcome

2007-06-20T13:34:00.000-04:00

Welcome to my SharePoint Development. I've been working with both SharePoint, SharePoint Designer, and InfoPath for a while now and have stumbled upon a number of useful tips and tricks along the way. I'm using this to offer help to the SharePoint community, as well as try to keep track of my own thoughts.