Formula refers to a column that does not exist

February 25, 2011

While working on a recent project I was creating/updating some site columns on an intranet which was based on Windows SharePoint Services v3.0. The changes were mainly creating new site columns to capture additional data and adjusting some existing columns, including a few calculated columns. I have used calculated columns in other projects so was expect the changes to be very straight forward.

When looking at the formula site columns I copied the existing formula into notepad twice using one to change and one as a backup. I made my changes to the formula and then pasted the code back into SharePoint but when I updated the site column I got the following error:

“The formula refers to a column that does not exist.  Check the formula for spelling mistakes or change the non-existing column to an existing column.”

While I was positive none of my changes could have resulted in this error I went back and compared my new version to the original just in case I had accidentally changed one of the column names but they were all correct. Next I checked that all the columns used in my formula were in the ‘Insert Column’ list to the right hand side of the formula box in case any of the columns had been deleted since the work was originally done but again everything was as it was supposed to be. When I went back over my new formula I noticed that while one of the fields was wrapped in square brackets, [column], another wasn’t. My initial reaction was that shouldn’t make a difference as my understanding was only fields that contained spaces in the name required square brackets but I though I would add them anyway. The result was the update to the formula worked.

I have since done some additional testing and it is true that when you add a new columnn from the ‘Insert Column’ list it automatically adds square brackets around it but if you then click ok and go back into the formula it seems to remove those square brackets from any columns that don’t have spaces in the name. I have spent some time testing on a development environment but can’t seem to establish exactly what it was about this particular formula that required the square brackets around the site column name.

At the end of the day I managed to get the functionality to work but I would still be interested in others experience of anything similar.

Advertisements

Master page and page layout reporting

February 22, 2011

A few weeks ago while working on a publically facing website built in MOSS 2007 I had to make some changes that affected all page layouts and master pages. The problem was overtime lots of test and unused page layouts and master pages had been left in the master page gallery. Rather than add the changes to all the page layouts and master pages I decided to write a tool that iterated through all webs and all pages in a site collection outputting the master page and page layout being used.

Since I am used to writing them I created a windows forms application which asks the user to supply the URL of the site, the only downside to this is the tool must be run on one of the web front end (WFE) servers.For my purposes I decided to take a snapshot of the site at a specific point and essentially export the data to a SQL DB. I create a variety of tables to store the information but I won’t cover this here as what I needed may not be required by most people. The reason I took this approach is I wanted to do some reporting on the data and didn’t want to be constantly programmatically accessing the site in case it had an effect on the performance.

Since I am not covering the DB side of things i have adjusted the code below to instead output all the details to a text file, this should give you an idea of what the process would be and you can alter it as you see fit.

Please note that while I have tested and ran this several times all use is at your own risk and strongly recommend this is tested in a development environment prior to being used on a production site.

private void button1_Click(object sender, EventArgs e)

{

    if (String.IsNullOrEmpty(TbxSiteURL.Text))

    {

        MessageBox.Show("Please enter a URL");

        return;

    }

 

    //open site and get web

    using (SPSite site = new SPSite(TbxSiteURL.Text))

    using (SPWeb web = site.OpenWeb())

    {

        //call function to loop through all sites and publishing pages

        IterateWebs(web);   

    }

}

 

/// <summary>

/// An iterative function that takes a site and log details to a text file such as masterpage and 

/// publishing pages page layouts. It then loops through all subsites of the site

/// passed in and calls the method again.

/// </summary>

/// <param name="web">A SPWeb object of the site to log details and check subsites</param>

private void IterateWebs(SPWeb web)

{

    string message = String.Format("Web: {0}{1}Location: {2}{1}Masterpage URL: {3}{1}",

        web.Title, Environment.NewLine, web.Url, web.MasterUrl);

    

    LogWebDetails(message);

 

    if (PublishingWeb.IsPublishingWeb(web))

    {

        PublishingWeb pubWeb = PublishingWeb.GetPublishingWeb(web);

 

        foreach (PublishingPage page in pubWeb.GetPublishingPages())

        {

            string pageLayout = page.ListItem.Properties["PublishingPageLayout"] as string;

            message = String.Format("Page name: {0}{1}Page URL: {2}{1}Page layout: {3}{1}",

                        page.Title, Environment.NewLine, page.Url, pageLayout);

            LogWebDetails(message);

        }

    }

    else

    {

        LogWebDetails("Web is not a publishing web" + Environment.NewLine);

    }

 

    foreach (SPWeb childWeb in web.Webs)

    {

        try

        {

            IterateWebs(childWeb);

        }

        finally

        {

            if (childWeb != null)

            {

                childWeb.Close();

            }

        }

    }            

}

 

/// <summary>

/// Log details to a text file

/// </summary>

/// <param name="logMessage">A string of the message to be written to the file</param>

private void LogWebDetails(string logMessage)

{

    File.AppendAllText("Log.txt", logMessage);

}

 

 


Useful Resources

February 18, 2011

As part of my everyday routine I come in the office and the first thing I do, apart from getting a cup of tea to wake up, is check what other people are doing/blogging about in the SharePoint arena. Some of the links below are people I have worked with but most are well respected SharePoint people. Even though I am sure most people regularly check these blogs I thought I would be a good idea to share the links anyway as it might help some people but also to see if there are any other good resources out there that I could be using. If there is someone/resource that you think is worth adding to the list let me know.


New Start

February 18, 2011

This week I started a new job working for TSG as a SharePoint Developer based in Hamilton, which meant leaving Dog Digital after only 5 months. It was a tough decision as while I was only at Dog for a short period everyone made me feel welcome and I got to learn a lot, particularly around using MOSS 2007 for public facing web sites and the joys of content deployment jobs. I would like to thank everyone at Dog for everything and wish them well in the future.

I am very excited about my future with TSG and am looking forward to getting to used SharePoint 2010 in a commercial sense. Even though I have only been here a few days I can tell its going to be a good move and am excited about the future.


SPContext.Web access denied error on SharePoint 2007 publically available sites

February 8, 2011

Towards the end of last week and the start of this week at work I was putting live some changes to a publically available site built in SharePoint 2007 when I started to get access denied errors on some pages which had been created using a new page layout. Initially I assumed that one of the components on the page were not checked in and published so I checked all the usual items such as:

  • The content pages stored in the site Pages Library
  • Master pages and page layouts in the site collection gallery
  • Shared content used on the pages
  • Images and JavaScript used on the page

After reviewing all the items mentioned above I was no further forward so I started to remove sections from the master page and page layout until I was able to pinpoint it was a particular web part, which was used to generate the sub navigation. Using a process of elimination and colleague and I were able to identify the accessed denied error was being triggered by a call to the SPContext.Current.Web SPWeb object. Initially I was confused as the code was wrapped in an SPSecurity.RunWithElevatedPrivileges block thus I assumed all code executed was getting run under the context of the application pool and should not be getting an access denied error. After some future research I found an msdn article which detailed the fact that using the SPContext.Current.Web will not be affected by running code in the Elevated Privileges block as this is created with the context of the currently running user.

To correct this issue I created new SPSite and SPWeb objects using the URL of the page being hit and the site worked as expected. I am aware lots of people may be aware of this caveat but thought it would be useful to document, partly for myself and partly for any others that may encounter a similar issue.


LinqDataSource has no values to insert

February 1, 2011

As promised in my first post the issued described in this post was what inspired me to start blogging as I was unable to find much information around similar issues.

I encountered the issue described in this blog when updating my wedding website. Before getting into the details of the error I will first give some background information into the original functionality and the changes that resulted in the error occurring.

When developing my wedding website one of the key requirements I identified was the ability for users to add comments about the wedding or the website. The idea being to promote conversation among users to increase their use of the site. When I was initially building the site I quickly identified I didn’t want anonymous users to be able to add comments as this could result in inappropriate comments or language. To ensure this did not happen I put in place two safeguards. Firstly, I created the comments page within the secure guests section of the website that only allows logged in users who are in a certain role access. Secondly, I added functionality to ensure all comments must first be reviewed and then approved by an authorized user.

Using this approach I was confident that I could prevent any unauthorized or unwelcome comments. After further consideration I decided while this two-pronged approach to comments was secure I decided it may affect users interaction with the site and it may be better for the comments to be on the publically accessible section of the site. After considering the options I implemented an approach that allowed anonymous users to see comments but only grant logged in users in a specific group access to add comments. This offered the visibly and ease of use for authorised users but also allowed people browsing the site to view comments. The solution I implemented was to add a login view control within the insert item template of the listview that was being used to render the comments.

Before change:

<InsertItemTemplate>
<div>
<div>
Comment:
<tinymce:TextArea ID=”tinyTextArea” runat=”server” Value='<%# Bind(“Name”) %>’ />
CustomValidator ValidationGroup=”InsertItem” ID=”CfvComment” runat=”server”
ErrorMessage=”Please enter a comment” OnServerValidate=”CfvComment_ServerValidate”>CustomValidator>
<div>
<asp:Button ID=”InsertButton” CssClass=”purpleButton” ValidationGroup=”InsertItem”
runat=”server” CommandName=”Insert” Text=”Insert” />
<asp:Button ID=”CancelButton” CssClass=”purpleButton” runat=”server” CommandName=”Cancel”
Text=”Clear” />
</div>
</div>
</InsertItemTemplate>

After change:

<InsertItemTemplate>
LoginView ID=”LvComments” runat=”server” EnableViewState=”false”>
<AnonymousTemplate>
<div>
In order to add any comments you must first <a href=”/Account/Login.aspx”>Log In</a>
or if you have not already created an account please <a href=”/Account/Register.aspx”>
Register</a> to get full access to the site, including the ability to choose
your menu options and RSVP.
</div>
</AnonymousTemplate>
<LoggedInTemplate>
<div>
<div>
Comment:
<tinymce:TextArea ID=”tinyTextArea” runat=”server” />
<asp:CustomValidator ValidationGroup=”InsertItem” ID=”CfvComment” runat=”server”
ErrorMessage=”Please enter a comment” OnServerValidate=”CfvComment_ServerValidate”></asp:CustomValidator></div>
<div>
<asp:Button ID=”InsertButton” CssClass=”purpleButton” ValidationGroup=”InsertItem”
runat=”server” CommandName=”Insert” Text=”Insert” />
<asp:Button ID=”CancelButton” CssClass=”purpleButton” runat=”server” CommandName=”Cancel”
Text=”Clear” />
</div>
</div>
</LoggedInTemplate>
</asp:LoginView>
</InsertItemTemplate>

The other crucial factor in this example is the fact that I am using a LinqDataSource to populate the listview and insert the details into the database. Prior to implementing the change above I was populating the data to be inserted in the database in the ‘LinqDataSource1_Inserting’ method, however afterwards I started getting the error below:

Exception information:
Exception type: InvalidOperationException
Exception message: LinqDataSource ‘LinqDataSource1’ has no values to insert. Check that the ‘values’ dictionary contains values.

While debugging the issue on my development environment I found I was unable to hit any break points in the ‘LinqDataSource1_Inserting’ method. Further testing found I was able to hit the ‘LsvComments_ItemInserting’ method before getting the error thrown by the LinqDataSource. I then adjusted my code to use the ‘LsvComments_ItemInserting’ to populate the details to be stored in the database and get around this issue, however I would be very interested to hear other people’s experience around this.


%d bloggers like this: