Notes Import Hell.

I needed to import some data from Excel into Lotus Notes. We're phasing out Lotus Notes and we're stuck on version 6.0.4, which only allows you to import from Lotus 123, tabular text, or structured text. The latter two options were not ideal because of the special characters that live within the data, so I decided to try the old, trusted, method of saving the Excel spreadsheet as 123, setting up a COL file, and running the import. I've done this dozens of times before.

This time, however, when I tried to save the Excel spreadsheet to 123 I received the following message:

"You are attempting to save a file type that is blocked by your registry policy setting."

I'm guessing a recent service pack or update must've added the restriction. I fought with this for at least 2 hours...what a waste. I found a site that told me what registry setting to adjust, and it didn't work either. Turns out they had the key misspelled...

So for any of you with this problem, here's the real fix:

Edit your registry using regedit. Create this key with a DWORD value of 0. That's it.

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security\FileSaveBlock\LotusandQuattroFiles

You can substitute FileSaveBlock with FileOpenBlock if you'd like to allow opening of Lotus files.

This google group entry helped me solve the problem finally (http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/283da40744cc51f4?pli=1)

Errors after installing Infrastructure update for MOSS 2007

After I ran the Infrastructure updates, which add new search features to MOSS 2007, I received a few errors trying to access the Search Admin interface.

When I clicked the Search administration link at /ssp/admin/default.aspx" href="http:///ssp/admin/default.aspx">/ssp/admin/default.aspx" href="http:///ssp/admin/default.aspx">http://<myserver>/ssp/admin/default.aspx, I first received this error

"The resource object with key 'S2SearchAdminDashboard_Title' was not found"

Also, the other links under the Search heading were all failing (either giving errors or giving the dreaded Unknown Error)

After searching a while on Google, I intuited the fix for this, which was to look in my Default Web Site's App_GlobalResources folder and compare it to the App_GlobalResources folder within my Shared Services Provider virtual directory. I noticed a few files that were in the Default Web Site folder, and not in the SSP folder. They had a newer date of 3/25/2008, so I copied them over to the SSP virtual directory and made a step forward. The files were:

searchadmin.en-us.resx

SearchAdmin.resx

sps.en-US.resx

and sps.resx

I then tried to open the Search Administration page again, and ran into another error:

Could not find the sitemap node with URL '/SearchAdministration.aspx'.

More digging around led me to discover other files that were newer in my Default Web site that hadn't been copied to the SSP site. Within the _app_bin folder, there is a layouts.sitemap file that needs to be copied over. As soon as that was copied over, the search administration page appeared fine.

One last thing - when the page came up, there were missing images for refresh and the left and right arrows at the bottom of the screen. This was a authorization error and I discovered that my Shared Services Provider Web site was using an incorrect Application Pool. I switched it to the correct one and the images appeared. Some incorrect access rights may be why the infrastructure update failed to copy the files over in the first place.

One other possible reason for the bug in the Infrastructure update is that I changed my SSP's port number after installing SharePoint. The port number conflicted with a backup product that used the same port, and changing the port number on SharePoint was much easier than changing the port on the backup product. As a result, the name of the virtual directory folder, which usually matches the port number for that virtual directory, did not match. Perhaps the install looked for a virtual directory via the port number.

SharePoint Error: Access web datasheet is attempting to retrieve data from a different domain.

A user reported this error today on a custom list built using the Datasheet view. We both could open the view, but as soon as any changes were made, the error would popup:

Access web datasheet is attempting to retrieve data from a different domain...

I had recently moved the site collection from a server (let's call it Terrance) to a new server (let's call it Phillip). I have to assume that when the datasheet view was trying to save changes, it was posting to http://terrance... instead of http://phillip...

I did some searching and found the answer: Alternate Access Mappings. You'll find this in SharePoint Central Administration, under Operations, and Global Configuration.

I needed to add a new Internal URL for http://terrance for the default zone, which has a public URL of http://phillip

According to Microsoft's documentation, when someone visits the site using the internal URL, the the public URL for the zone will be returned to the browser. So, visiting http://terrance/default.aspx, returns http://phillip/default.aspx in the address bar of the browser and the page that exists there.

Adding this Alternate Access Mapping immediately solved the problem the datasheet view was having.

Specified Cast is Invalid using LINQ to SQL

I found a helpful troubleshooting option when debugging an error in some LINQ to SQL code.

I have a custom class in my designer called ActualHoursData and it has a few properties. This is not auto-generated from the designer but rather is used as a class to hold results from a method call to the database.

I have a simple ASP:Repeater on a Web Form bound to a LINQ to SQL method result that is essentially a collection of this class.

When I opened the Web form in a browser, I kept getting the "Specified Cast is Invalid" error, and the stack trace showed the error occurred during the databind.

I couldn't easily tell which column from the database was causing the issue, and as far as I could tell, the data types were correct. But I discovered that I can set each property's type to System.Object, instead of System.Decimal, and the code runs fine.

I was then able to revert each property's type back to the expected specific type, and I found which property that was really causing the problem.

Using WrenSoft's ZoomSearch engine on an ASP.NET Web site

Wrensoft makes a very affordable and easy to use search engine that you can plug into your existing Web site. There are a few versions that are supported directly (ASP, CGI, and Javascript), but you can also use it on an ASP.NET Web site.

There are some instructions located on Wrensoft's website, which will help you build a basic search.aspx page for querying your website. However, if you want to integrate the search form into your own theme or site with masterpages, you'll need to take another approach.

The key is to use an asp:Literal control, and place it wherever you'd like the search results to appear. You can then build your search.aspx page just like any other themed page on your site and consider the Literal control to act like a 'Search results' control.

Using the code provided by wrensoft we'll write the output to that Literal control instead of the Response stream.

First, add your literal wherever you want the results to appear.

   1: <p>

   2:     <asp:Literal ID="ZoomSearch" runat="server"></asp:Literal>

   3: </p>

Then insert the code below (written in VB) in the Page_Load event. Note the code is copied from wrensoft's page, but I've changed the last line to take the results from the CGI search and place them into the asp:Literal

   1: 'Code from Wrensoft.com:  http://www.wrensoft.com/zoom/support/aspdotnet.html#vbdotnet

   2:  

   3:         Dim paramStr As String = ""

   4:         Dim parampos As Integer = Request.RawUrl.IndexOf("?")

   5:         If (parampos >= 0) Then

   6:             paramStr = Request.RawUrl.Substring((parampos + 1))

   7:         End If

   8:         Dim psi As ProcessStartInfo = New ProcessStartInfo

   9:         psi.FileName = Server.MapPath("~/search/search.cgi")

  10:         psi.EnvironmentVariables("REQUEST_METHOD") = "GET"

  11:         psi.EnvironmentVariables("QUERY_STRING") = paramStr

  12:         psi.EnvironmentVariables("REMOTE_ADDR") = Request.ServerVariables("REMOTE_ADDR")

  13:         psi.RedirectStandardInput = False

  14:         psi.RedirectStandardOutput = True

  15:         psi.UseShellExecute = False

  16:         Dim proc As Process = Process.Start(psi)

  17:         proc.StandardOutput.ReadLine()

  18:         Dim zoom_results As String = proc.StandardOutput.ReadToEnd

  19:         ' read from stdout 

  20:         proc.WaitForExit()

  21:         ' Print the output 

  22:         Me.ZoomSearch.Text = zoom_results

Sorting DataView in SharePoint

Recently I was trying to connect a DataView Web Part to another Web Part containing sort options, so I could present a view of names and phone numbers and let the end-user choose how they'd like the view sorted.

Seems simple enough, right?

Maybe there's an easier way, but twice I've been trapped by the same idiosyncrasy in XSL sorting.

When you add a DataView Web Part in SharePoint designer, the XSL code is generated for you. If you specify a sort for your DataView, for example a sort by 'Name', you'll see something similar as your xsl-sort tag:

<xsl:sort select="Name" order="ascending" />

To make it dynamic, I connected the Web Parts and passed a parameter from my sort options Web Part to my DataView Web Part. The parameter was called SortOrder, so I assumed I could just change the select attribute to $SortOrder and it would sort by Name or Phone depending on what the user had selected.

It turns out this does not work, and the workaround is to use this code instead in the select attribute:

<xsl:sort select="*[name()=$SortOrder]" order="ascending" />

That worked great for me the first time I ran into this problem. The XML that was feeding the DataView had the data stored in elements like so:

<People>
 <Person>
   <Name>Joe Smith</Name>
   <Phone>999-999-9999</Phone>
 </Person>
</People>

But what if the data is stored in attributes instead? This is the case when you feed your DataView Web Part with another SharePoint List. The data comes over in a form somewhat like this (thanks to David Wise's post for help in discovering this)

<People>
 <Person Name="Joe Smith" Phone="999-999-9999">
 </Person>
</People>

In this case, you'll need to use the following select attribute to make this work (note the @ attribute sign)

<xsl:sort select="@*[name()=$SortOrder]" order="ascending" />

I'm no expert in XSL, but essentially this says to me select all attributes with an attribute name of $SortOrder.