SharePoint Expert: October 2012

Monday, October 29, 2012

Dynamic Content Editor Webpart

We can assign the Content link to a content editor webpart dynamically using contentlink property. Also we can create the html content dynamically and assign to a Content Editor Web part dynamically using Content property.

string siteURL = default(string);

try{
siteURL = SPContext.Current.Web.Url;ContentEditorWebpart1.ContentLink = siteURL +
"/ChairmanMessage.html";
XmlDocument xmlDoc = new XmlDocument();XmlElement xmlElement = xmlDoc.CreateElement(
"EditorContent1");xmlElement.InnerText =
"<HTML><iframe id=ifrm width=100% height=345 src='" + "http://google.com" + "' width='Size' height='Size' marginwidth=0 marginheight=0 hspace=0 vspace=0 frameborder=1 scrolling=no></iframe></HTML>";ContentEditorWebpart2.Content = xmlElement;
}

catch (Exception exp){

throw exp;}

Read All the files (including Sub Folder) in a List

Read the required list item directly using CAML query. You can search for a list item inside folders by setting Scope attribute to Recursive as follows:

SPWeb web = SPContext.Current.Web;

SPList list = web.Lists["Shared Documents"];

string content = string.Empty;

SPQuery query = new SPQuery();

query.ViewAttributes = "Scope=\"Recursive\"";

query.Query = "<Where><Eq><FieldRef Name='FileLeafRef' />

<Value Type='File'>NewProcess.js</Value></Eq></Where>";

SPListItemCollection items = list.GetItems(query);

SPListItem item = items[0];

Read File content from Document Library

The below code finds the file NewProcess.js in "Shared Document" document library and read the content (java script code) from the javascript file. It opens the file in Binary strean and assigns to a StreamReader Object.

SPWeb web = SPContext.Current.Web;

SPList list = web.Lists["Shared Documents"];

string content = string.Empty;

SPQuery query = new SPQuery();

query.Query = "<Where><Eq><FieldRef Name='FileLeafRef' />

<Value Type='File'>NewProcess.js</Value></Eq></Where>";

SPListItemCollection items = list.GetItems(query);

SPListItem item = items[0];

if (item.Name.Equals("NewProcess.js"))

{

using (System.IO.StreamReader reader =

new System.IO.StreamReader(item.File.OpenBinaryStream()))

{

content = reader.ReadToEnd();

}

Error : "The formula contains a syntax error or is not supported"

Recently I noticed one issue in Calculated list field. I have one big nested IF condition in the formula and I got the below error.

Error : “The formula contains a syntax error or is not supported. at Microsoft.SharePoint.Library.SPRequestInternalClass.UpdateField(String bstrUrl, String bstrListName, String bstrXML)
at Microsoft.SharePoint.Library.SPRequest.UpdateField(String bstrUrl, String bstrListName, String bstrXML)”

Resolution : We can have 8 levels of nested IF condition in MOSS 2007 calculated formula field. In my case it is around 10. The solution is split the formula into 2or 3 parts and save them in individual calculated columnes; then create another calculated column to concatenate all parts of formula back into one. This may not be the best solution, but it is a best workaround. Alternativerly, if the formula is really big use Excel

and push it up with excel services.

Tuesday, October 23, 2012

SQL Table and Stored Procedure List - Search using Keyword

SP List based on Keyword(table name, field name etc) Search

SELECT DISTINCT obj.Name SPName

FROM sys.syscomments sc

INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID

WHERE sc.TEXT LIKE '%' + 'Lead_Approver' + '%'

AND TYPE = 'P'

List tables based on column name search

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns

WHERE name like '%Lead%' )

SP List with Created date and Modified date

SELECT name, create_date, modify_date

FROM sys.objects

WHERE type = 'P'

List All Stored Procedure Modified in Last N Days

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 7

COALESCE Function with Realtime Example

Coalesce function will be helpful if we use it with tricky way.

Basically this function accepts n number of arguments and returns the first non null value, which is passed as parameter.

There are many situations we need to bring the multiple row values in a single string ( concatenated string). The following is the Example with necessary Screenshot and SQL Function to accomplish this.

The SQL for GetStudents function is as follows

CREATE function [dbo].GetStudents
(
@className varchar(max)
)
RETURNS VARCHAR(MAX)
AS
BEGIN--Main

DECLARE @x varchar(8000)

SELECT @x = COALESCE(@x+', ','')+ISNULL([StudentName],'')
FROM [BidSmartTemp1].[dbo].[ClassTable]
WHERE ClassName = @className
RETURN @x

END

Monday, October 22, 2012

SPFolder Rename Trick

The SPFolder object properties are mostly read-only, so I am afraid you are up poops creek sans paddle with that. The way that I have gotten accustomed to programmatically renaming things for files (like an SPFile object) is to call the name column:

file.Item["Name"] = string.Format("{0}", yourClassVar)

then doing a:

file.Item.Update();

For renaming a folder, it is kinda tricky, since it is read-only propreties. BUT, you can do a MoveTo()

Like this won't work:

SPFolder.Name = "NewFolderName";

But this will:

SPFolder.MoveTo("brandspakningnewSPFolder.Name");