Danushka Silva

How to: Create an External Content type using SharePoint Designer 2010 (Business Connectivity Services)

Here we are going to do a lab that creates an external content type using SharePoint 2010. Assume your company has a database with product details. You need to retrieve those details from the database and show them in the SharePoint site. Here you need to create, update and delete those records within SharePoint environment. So basically we are talking about creating CRUD methods for the external database inside the SharePoint UI.

First I will breakdown entire process into few points where we can very easily follow.

• Create an External Content Type in SharePoint Designer 2010.
• Create CRUD methods.
• Deploy the external content type to SharePoint server.
• Create an external list
• Test the lab

OK… will start SharePoint designer and connect to our site. Once you connected to the server you get all the site objects on your left pane. Select External Content Types and you get all the external content types listed on the right pane.

Create new external content type by clicking External Content Type button on the top left corner

It will open the new external content type screen. Here you can change the key information of your external content type such as Name, Display Name , etc.

Now we have to create the data source to connect to our external database. Go to the bottom of the screen and click ‘Click here to discover external data sources and define operations’ link.

Now you’re in the screen where you can manage connections to external data sources and operations for the external content type you are going to create.

Click Add Connection to add our data source and select SQL Server from the dropdown menu. Press OK button

Type your Database Server and Database Name. Select the appropriate security method to connect and press OK.

Here you can see your data source has been added to the list.

Now we need to add methods to perform our CRUD actions. So will select the Product table which we need to extract data from and right click. Here you can see all the operations you can add to the particular table. Select New Read List Operation from the menu.

Now you can see the pop up window where you can configure read list operation. Change the names as you want and click Next.

Next you can add filter parameters to filter your data. Otherwise it will make large result with unwanted data. Since our lab database doesn't have much data, we skip that part. Press Next.

Here you can see all you columns in the table and select the appropriate columns to show in the external list. You can change their Display Names as well. Make sure to select Map to Identifier check box in the primary key. Click Finish

Now you can see the Read List operation in the External Content Type Operations.

Create the read item operation the way we did to read list operation.

Create operation.

I have removed Product ID column from create because it’s an auto number field.

Update operation.

Delete operation.

Now we have created all the methods we want and you can see that in the External Content Type Operations

Now save the SharePoint Designer and it will automatically deploy the external content type into SharePoint server. If you go to the central administration site you can see the external content type has been deployed. Application Management – Manage Service Applications – Business Connectivity Services.

Now we go to the site and create an external list. Go to Site Actions – View All Site Content

Click Create button on top of the page.

Select External List and click Create button on the right.

It will open the external list creation page. Type the name and select the external content type from the dialog and click Create

Lets see how it will look like.

This is how item view looks like

Just check the create, update and delete operations.

Error:”The form has been closed” – InfoPath 2007

Here I got an error when I tried to attach custom InfoPath Initiation and Association forms to display in a SharePoint workflow. Depends on the IE session the error message differ. If you close the browser and reopened 2nd and 3rd error messages will appear.

Anyway this error message appear because of the xml statement in the workflow.xml. If you check your workflow.xml you can see the form URNs are written like this

<Association_FormURN>
    urn:schemas-microsoft-com:office:infopath:Test:-myXSD-2010-07-06T09-15-06
</Association_FormURN>
<Instantiation_FormURN>
    urn:schemas-microsoft-com:office:infopath:Test:-myXSD-2010-07-06T09-15-06
</Instantiation_FormURN>

To fix the problem, you have to remove the spaces between the xml tags like this. DO NOT keep any spaces between <> and </> tags.

<Association_FormURN>urn:schemas-microsoft-com:office:infopath:Test:-myXSD-2010-07-06T09-15-06
</Association_FormURN>
<Instantiation_FormURN>urn:schemas-microsoft-com:office:infopath:Test:-myXSD-2010-07-06T09-15-06
</Instantiation_FormURN>

Please add a comment if you use this code or it helped you. So I can make my posts better.

Error: “Cannot access the local farm” – PowerShell command error

When working with PowerShell I got an error. First I thought I am making some kind of a  mistake because I am new to this as well

So I was struggling to make it happen and finally figured it out. The solution was to have administrator privileges to run PowerShell. So when you want use PowerShell don’t just click on it. Right click and Run as Administrator

Bingo! Now you can use it.

Please add a comment if you use this code or it helped you. So I can make my posts better.

Error: “SQL server has an unsupported version” when installing SharePoint 2010

This error message came when I was installing SharePoint Server 2010. You might not get this error all the time so first I’ll explain my environment which I tried to install SharePoint 2010. My OS was Windows Server 2008 R2 Standard 64-bit (6.1, Build 7600) and SQL Server 2008 Standard x64. So I started with SQL server which was a smooth installation and then started the SharePoint Server 2010. The setup installation went smoothly. But when it comes to the SharePoint 2010 Products Configuration Wizard, it asked the database server and credentials. So I gave the same name and credentials which I installed before. Then the error came

So I download the SQL Server 2008 Service Pack 1 and installed, restarted and tried again. Then I got the same error but the version number was different.

Went through everything again and finally found the solution. It was to install the Cumulative update package 2 for SQL Server 2008 Service Pack 1 on SQL server 2008.

Here are the links

SQL Server 2008 Service Pack 1
http://www.microsoft.com/downloads/details.aspx?FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19&displaylang=en

Cumulative update package 2 for SQL Server 2008 Service Pack 1
http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=970315&kbln=en-us

Please add a comment if you use this code or it helped you. So I can make my posts better.

Calculate business days (weekdays) for a given two days using OOTB calculated field.

Recently I needed to calculate business days for a given start and end days. But I needed to do this using OOTB calculated field. This formula has to remove Saturdays and Sundays (Weekends) from the given start and end dates. Here I check if dates are inserted. If not I return an empty string. I was struggling with functions in calculated fields and I was not allowed to use SharePoint object model. I tried so many ways and finally came up with the correct formula. I need to thank Sujeewa, who helped me out with the formula. Here is the formula. Happy coding

=IF(ISERROR(DATEDIF(StartDate,EndDate,"d")),"",(DATEDIF(StartDate,EndDate,"d"))+1
-INT(DATEDIF(StartDate,EndDate,"d")/7)*2
-IF((WEEKDAY(EndDate)-WEEKDAY(StartDate))<0,2,0)
-IF(OR(AND(WEEKDAY(EndDate)=7,WEEKDAY(StartDate)=7),AND(WEEKDAY(EndDate)=1,WEEKDAY(StartDate)=1)),1,0)
-IF(AND(WEEKDAY(StartDate)=1,(WEEKDAY(EndDate)-WEEKDAY(StartDate))>0),1,0)
-IF(AND(NOT(WEEKDAY(StartDate)=7),WEEKDAY(EndDate)=7),1,0))

Please add a comment if you use this code or it helped you. So I can make my posts better.

Dropdown get to editable mode when more than 19 records in lookup fields

When you create a lookup field to reference data from another list, you will get a dropdown control to select data from that list. If you select “Allow multiple values” you will get a list box control with two buttons which you can select values. But have you ever seen an editable dropdown OOTB in SharePoint? I don’t think so. If you need to add an editable dropdown then you have to come up with your own custom control.

When you add more than 19 records to the lookup list, SharePoint automatically makes that dropdown list editable. This is because when you have lots of items in the dropdown, It’s very difficult to find an appropriate item. So SharePoint allows you to type in the dropdown and it’s automatically sorted. This is not a bug. This is a feature in SharePoint.

Let’s see some screenshots.

OK… I have two lists. “My Favorite Colours” has a lookup field to “My Colours”

“My Colours” has some colours in the list.

Added the lookup field to “My Favorite Colours” list

When “My Colours” has 19 records, “My Favorite Colours” list’s edit form shows usual dropdown box (Un editable)

But when we added more than 19 it’s automatically gets to editable. You can type and it’s automatically gets sorted according to the typed character.

I know what you think now. Users can type any value and save to the list without checking the lookup values. Don’t worry SharePoint automatically checks the value and show an error message.

Please add a comment if you use this code or it helped you. So I can make my posts better.

SharePoint 2010 VM in 32 bit host machine

I was trying to install SharePoint 2010 VM in 32 bit machine last week and couldn’t do it. After hours of searching the web I found a solution for that. This is a very good article that explains how you can install SharePoint 2010 VM in 32 bit host machine. Since SharePoint 2010 only comes in 64x version it’s worth reading. Thanks for perete for publishing this article.

http://comunidad.terra.es/blogs/moss/archive/2009/07/18/howtocreateasharepoint2010vmin32bitshostmachine1of5.aspx

SharePoint Server 2010 Beta

As you're all waiting for SharePoint 2010, Now is the chance to taste it. Click here to download your SharePoint Server 2010 Beta version now.

Custom Master pages on SharePoint

Few months ago I wanted to create a custom master page for a SharePoint portal and after some research I found a way of customizing master pages on SharePoint. But I couldn’t post a blog about it. Here I am attaching my custom master pages and I will update this as I develop it further in the future. I will explain the ways of customization soon.

	Intranet for Spring Global Services on MOSS 2007
	Intranet for another customer on MOSS 2007
	Hosted WSS site on WSS 3.0

Awesome SharePoint features in Visual Studio 2010

Few days ago MS has announced Visual Studio 2010. It contain many SharePoint project templates and grate features for SharePoint developers. Here is the short overview which Microsoft SharePoint Team Blog has given. Read more at Microsoft SharePoint Team Blog.

SharePoint Project and Project Item Templates

The following SharePoint specific project templates and project item templates are available in Visual Studio 2010:

Project Templates

· Empty SharePoint project
· Visual Web Part project
· Sequential and State Machine Workflow
· Business Data Connectivity Model
· Event Receiver
· List Definition
· Content Type
· Module Project
· Site Definition

Project Item Templates

· Empty Element
· Web Part
· User Control
· Application Page
· Association Form
· Initiation Form
· Business Data Connectivity Resource Item
· List Instance
· List Definition From Content Type
· Global Resources File

Besides above mentioned project templates, there are two import project templates for importing .WSP file contents and importing reusable workflows:

· Import Reusable Workflow
· Import SharePoint Solution Package

World Clock web part – SharePoint 2007

This is a clock web part shows digital clocks depends on the user needs. Thanks to anujpant for sharing this at codeplex. You can find the original source and wsp file here.

The original clock looks like this

Customized clock will looks like this

You can download the customized source code here what I edited for my requirement.

I customized this with VS 2008. Here I wanted to display the city/country name beside the clock rather than displaying under the clock. So I added a new cell into the same row.

Then I wanted to remove clock skins. The original clock can select 4 color skins as the user wants. After deploying the clock I realized that displaying seconds is disturbing the users. It’s worse when you added more than 5 clocks. So without removing the seconds I hid it.

Currency Converter web part – SharePoint 2007

Recently one of our customers wanted a currency converter on their SharePoint site. This can be done developing a custom web part. I developed a web part using VS 2008. So I thought of publishing it with the source then anyone can download and modify it as they want.

Here is what it looks like

You can download the source codes here.

First I want to thank Shalvin for the free currency converter web service. Here he has explained how we can use the web service in SharePoint. Now I am going to explain my web part with some screenshots.

Now open your VS2008 and create a new project using Web Part template.

We start adding the web service to our project. Right click you project in the solution explorer and click on Add Web Reference. Type our web service URL in the URL box and click Go. If you want you can change the Web Reference Name and click Add Reference.

Open your .cs file and define some variables and controls.

CurrencyConvertor.CurrencyConvertor cc = new CurrencyConvertor.CurrencyConvertor();
double dblConv;
double dblAmt;
DropDownList lstEndCurr;
DropDownList lstStartCurr;
Button btnSend;
Label lblConversion;
TextBox txtAmount;
Label lblConvert;
Label lblInto;
Label lblAmount;
Label lblDetails;

Make sure you have “WebControls” namespace in your class

using System.Web.UI.WebControls;

We are going to have two DropDownList controls for “From” and “To” fields. So we need to fill them with currency codes with description. So here I have wrote a function for that which will return a HashTable and you can directly bind that to the DropDownList. Here if you want you can directly call method from the web service and fill the DropDoenList.

private Hashtable setDDL()
{
    Hashtable hTable = new Hashtable();
    hTable.Add("AFA", "AFA-Afghanistan Afghani");
    hTable.Add("ALL", "ALL-Albanian Lek");
    hTable.Add("DZD", "DZD-Algerian Dinar");

    return hTable;
}

Then we need to add our controls to a collection. So we can call them when we render the page. So here we use enumeration type.

private enum WebPartControls
{
    lblAmount,
    txtAmount,
    lblConvert,
    lstStartCurr,
    lblInto,
    lstEndCurr,
    btnSend,
    lblConversion,
    lblDetails
}

Now you can add code for all your controls to the “CreateChildControls” method. Here I haven’t add all my codes in this post.

protected override void CreateChildControls()
{
    //Set properties for Amount label
    lblAmount = new Label();
    lblAmount.Text = "Amount :";
    lblAmount.Width = 60;
    this.Controls.Add(lblAmount);

    //Set properties for Amount textbox
    txtAmount = new TextBox();
    txtAmount.Width = 100;
    this.Controls.Add(txtAmount);

    //Set properties and items for from DropDownList
    lstStartCurr = new DropDownList();
    //Define a Hashtable and set the return Hashtable from setDDL function
    Hashtable hTableStart = new Hashtable();
    hTableStart = setDDL();
    lstStartCurr = new DropDownList();
    lstStartCurr.DataSource = hTableStart;
    lstStartCurr.DataTextField = "Value";
    lstStartCurr.DataValueField = "Key";
    lstStartCurr.DataBind();
    this.Controls.Add(lstStartCurr);

    //Set properties and event for convert button
    btnSend = new Button();
    btnSend.Click += new EventHandler(btnSend_Click); 
    btnSend.Text = "Convert";
    btnSend.Width = 75;
    this.Controls.Add(btnSend);

    ChildControlsCreated = true;
}

Next we can start writing the event for our convert button

void btnSend_Click(object sender, EventArgs e)
{
     //Creat two object from the currency web service
     CurrencyConvertor.Currency fromCurrency = new CurrencyConvertor.Currency();
     CurrencyConvertor.Currency toCurrency = new CurrencyConvertor.Currency();
     //Convert selected items from DropDownLists into currency objects
     fromCurrency = (CurrencyConvertor.Currency)Enum.Parse(typeof(CurrencyConvertor.Currency), 
this.lstStartCurr.SelectedValue.ToString());
     toCurrency = (CurrencyConvertor.Currency)Enum.Parse(typeof(CurrencyConvertor.Currency), 
this.lstEndCurr.SelectedValue.ToString());




     //Pass our from and to currency codes to web service and get the return value to a variable 
     dblConv = cc.ConversionRate(fromCurrency, toCurrency);
     lblConversion = new Label();
     dblAmt = Double.Parse(txtAmount.Text.ToString()) * dblConv;
     lblConversion.Text = dblAmt.ToString();
     this.Controls.Add(lblConversion);
     //Set the value for Details label with the return values 
     lblDetails = new Label();
     lblDetails.Text = "1.00 " + lstStartCurr.SelectedValue.ToString() + " = " + dblConv.ToString() 
+ " " + lstEndCurr.SelectedValue.ToString();
     lblDetails.Style["text-align"] = "center";
     this.Controls.Add(lblDetails);
}

Next we can start out render method. This is the method which will create UI in our web part. We can insert html break tags between controls to have space in-between.

protected override void Render(HtmlTextWriter writer)
{
     try
     {
          Controls[(int)WebPartControls.lblAmount].RenderControl(writer);
          Controls[(int)WebPartControls.txtAmount].RenderControl(writer);
          writer.Write("<BR>");
          writer.Write("<BR>");

          if (Controls.Count == 9)
          {
               Controls[(int)WebPartControls.lblDetails].RenderControl(writer);
          }
     }
     catch(Exception ex)
     {
}

Now we are done all coding things. Select your project and go to properties. It will open the project property window. Click on Debug and click on Start browser with URL. Type your site URL in the text box.

Now go to Build menu and select Deploy Solution. Visual studio will create our web part and deploy it into the SharePoint site.

Happy coding!

Session on Business Data Catalog with SharePoint 2007

Recently I did a session at ITPro forum about Business Data Catalog with SharePoint 2007. Here I wanted to focus all our techies that how we are able to connect LOB systems to SharePoint 2007 using BDC and no coding solutions with LOB systems on SharePoint 2007.

Below the agenda of my session.

• Introduction to Business Data Catalog
• Why you need BDC?
• Live Demo with Simple BDC Application

 
Screenshot of simple BDC application

• Live Demo on Configuring Search with BDC Application

 
Screenshot of search results

• Live Demo on Configuring InfoPath from with BDC Application

 
Screenshot of Selected record value passed to a from

• Q&A

Those who are couldn’t attend to this session can download the files here.

• Presentation (ppt)
• Application definition (xml)
• InfoPath form (xsn)
• Northwind database

Some memories from the session

Date    : 26th August 2009
Time    : 6 PM onwards
Venue   : Microsoft Sri Lanka Auditorium

Publishing InfoPath Form Templates to SharePoint 2007

First you have to design your InfoPath form and publish it. These are the steps.

First you have to make your form as web enabled form. For this, go to Tools ->Form Options. Form Options window will prompt. Select Compatibility from the left pane. Below window will prompt. Select first check box to make browser enable form.

Go to File menu and select Publish

Publishing wizard will prompt. Select the first option. Click Next

Next you type the URL where you want to publish the form. Eg. http://moss or http://moss/sites/departments and click Next

Select the Administrator-approved option and click Next

Type your local path to save the publishing file.

Here you can map your fields as form library columns. For this tutorial I am going to ignore this step since we are not going to map any columns. Click Next

Click Publish

Click Close once you get the success message.

Now we have finished the InfoPath side of work. Open your SharePoint central administration site.

Go to Application Management

Click on Manage form templates in InfoPath Form Services

Click on Upload form template in the Manage Form Templates window

Browse the form template you published earlier. Click Upload

Success message will appear. Click OK

Once you have done this, you will be redirected to Manage Form Templates window. Now you can see your form template in the list.

We have to activate the form template to access from the site collection. Click on the name of the form template and it will open a drop-down menu. Select Activate to a Site Collection to active the form template.

You will get the success message below and click OK.

At this point we have finished uploading the form template. Now we have to activate it from the site collection to enable for users. Go to Site Settings in the Site Action menu

Select Site Collection Features in the Site Collection Administration.

In the Site Collection Features window you can see your form. Click Activate to enable for users.

Now go back to the site and click View all site content -> Create to create a form library. Click on Form Library in Libraries to create.

Type a meaning full name and click Create.

You will be redirect to the newly created form library. Click Setting -> Form Library Settings to view library setting.

Click Advance settings in General Settings to change some settings to make the form view as web page.

Change Allow content types to yes and Opening browser enabled documents to Display as a web page. Click OK

Now come back to Form library settings page and this time it has changed. Click on Add from existing site content types to add your form template.

Select your form template from the left istbox and click Add. Click OK.

No go back to the Form library click New. It will prompt a menu with your form template. Click on it to view your form template.

This will be the final outcome of your form template.

Sneak Peek on SharePoint 2010

Hi Techies,

MS has released SharePoint 2010 Sneak Peek. There are 3 videos (Overview, IT Professional and Developer) that discusses some of the new features. Details on the other features yet to come