Wednesday, December 14, 2011

Embed Video in SharePoint blog

The rich text editor in the SharePoint blog strips out any HTML that isn't an image or anchor tag. My client wants to embed video in a blog post using an <EMBED> tag:


<EMBED src=http://intranet.companyname.com/styles/CompanynameMediaPlayer.swf width=640 height=384 type=application/x-shockwave-flash FlashVars="values=rtmpt://companyname.fcod.llnwd.net/a629/o1/int/IIG/IRPS/ibts_technology_123112_fk?ip=192.175.160.0/19&amp;h=cd7aac23fc8643ff6f299e5e178bf7a7&amp;amp;autoPlay=true&amp;amp;skinName=http://intranet.companyname.com/styles/CompanynameMediaPlayerSkinAS3.swf&amp;amp;playVideoButton=true&amp;amp;hideFSButton=true" allowScriptAccess="always"></EMBED>

The highlighted text above shows the actual location of the video.

However SharePoint will allow a <video> tag because it isn't valid HTML.

I enter the video into the blog post in this format:
<video>rtmpt://companyname.fcod.llnwd.net/a629/o1/int/IIG/IRPS/ibts_technology_123112_fk?ip=192.175.160.0/19&amp;</video>

So I created a jquery script that would replace the opening <video> tag with the text highlighted in pink above, and replace the </video> tag with the text highlighted in green above. How to implement the solution:
1. Create a document library on your site for scripts.

2. Upload a Jquery library to the scripts library.

3. Edit the following code in Notepad:

<script type="text/javascript" src="http://companyname.com/irps/home/video/Scripts/jquery.min.js"></script>
<script type="text/javascript">
$().ready(function() {

 $(".ms-PostBody").html(function(index, oldHtml) {
  var matchStr ="(&lt;video&gt;)(.*?)(&lt;/video&gt;)";
  var m = oldHtml.match(matchStr);

 $.each(m, function (index, value) {
  var regexp1 = new RegExp("(&lt;video&gt;)");
  var regexp2 = new RegExp("(&lt;/video&gt;)");
   oldHtml = oldHtml.replace(value, value.replace(regexp1, '<embed src=http://intranet.companyname.com/styles/CompanyNameMediaPlayer.swf width=640 height=384 type=application/x-shockwave-flash Flashvars="values=').replace(regexp2, 'autoplay=true&amp;skinName=http://intranet.companyname.com/styles/CompanynameMediaPlayerSkinAS3.swf&amp;playVideoButton=true&amp;hideFSButton=true" allowScriptAccess="always"></embed>'));
  });
  return oldHtml;

  });
});
4. After it is uploaded, copy the URL of the Jquery library from the script library on your site and paste it into the area highlighted above:

5. Save the file in Notepad as a file named videoBlog.js

6. Upload the videoBlog.js file to your scripts document library.

7.  Open your blog.  You need to add content editor web parts to two pages on your blog: 
/VideoBlog/default.aspx
and
/VideoBlog/Lists/Posts/Post.aspx

Enter the following urls to put the pages into Edit mode:
 /VideoBlog/default.aspx?ToolPaneView=2&PageView=Shared
 
8. Add a content editor webpart at the bottom of the page. 
 
9. Link to the videoBlog.js file you uploaded to the Scripts library in step 6 above.
 
10.  Set the chrome to display none
 
11. Save the page and repeat the steps 8-10 for the other page:
 
/VideoBlog/Lists/Posts/Post.aspx?ToolPaneView=2&PageView=Shared
 
 
Note:  The size of the video is hardcoded in the javascript code.



Wednesday, May 18, 2011

InfoPath 2007 - Validating whether a form has been edited by another user since it has been opened

We had a requirement to prevent users from overwriting another's form. Initially I tried to automate the check-in check-out function programmatially, but I ran into some obstacles.

It is possible to programmatically check-out the form on the form Loading event, but there is no form unloading event. If a user just closes the browser window, the form will remain checked out.

It's possible to check in a form from a SharePoint designer workflow, so I wanted to check-in the form automatically when the item was saved, but ironically, a SharePoint designer workflow won't run on an item that is already checked out.

So as a workaround, instead of using checkin/checkout, I am validating the last modified date on save. When the form is first opened, I populate the InitialLastModifiedDate. When I click the save button, I populate the CurrentModfiedDate. If the dates don't match, I raise an error.


Scenario:  User 1 opens form,
User 2 opens form
User 1 saves form
User 2 saves form ( raise error)

This solution requires code behind.

First add the following fields to the main data source.

InitialLastModified
CurrentLastModified
UpdateInitialLastModified
doSaveValidation
saveMessage

I made the InitialLastModified, CurrentLastModified and UpdateInitialLastModified fields text fields. It's not necessary to make them date fields, a string comparison was easier for me to work with.



Add the saveMessage field to the form. The saveMessage will display the actual validation message. 

Add a rule to the Save button on the form. The rule will update the saveMessage (I just add a period to it), and set doSaveValidation to "yes".  I only want to do the validation when saving, so the doSaveValidation doesn't get set until I click the Save button. By changing the saveMessage field, I initiate some code that runs on the saveMessage_Changed Event.




In the formloading event, initiate the fields:

populateLastModified(strIncidentNum, "/my:myFields/my:InitialLastModified");
xNav.SelectSingleNode("/my:myFields/my:CurrentLastModified", this.NamespaceManager).SetValue("");
//I'm setting the doSaveValidation to 'no' because I don't want the validation to run until the Save button is clicked.
xNav.SelectSingleNode("/my:myFields/my:doSaveValidation", this.NamespaceManager).SetValue("no");



The populateLastModified function actually does a CAML Query to get the last modified date from the SharePoint form library. I will do another CAML query in the saveMessage_Changed event to get the currentLastModified date when the form is being saved

public void populateLastModified(string strIncidentNum, string xPath)
{
SPQuery qry = new SPQuery();
qry.Query = "<Where><Eq><FieldRef Name='ICSIncidentNum'/><Value Type='Text'>" + strIncidentNum + "</Value></Eq></Where>";
qry.ViewFields = "<FieldRef Name='Editor'/>";

try
{
SPContext contxt = SPContext.Current;
string siteURL = contxt.Web.Url;
using (SPSite site = new SPSite(siteURL))
{
using (SPWeb web = site.OpenWeb())
{

SPList list = web.Lists["ListName"];
SPListItemCollection LIC = list.GetItems(qry);
// there should only be one
if (LIC.Count > 0)
{
SPListItem item = LIC[0];
XPathNavigator xNav = this.CreateNavigator();
xNav.SelectSingleNode(xPath, this.NamespaceManager).SetValue(item["Editor"].ToString());
}

}
}
}
catch (Exception ex)
{
logError("An error occurred in populateLastModified: " + ex.Message);
}

}

Create a validating event on the saveMessage field:


public void saveMessage_Validating(object sender, XmlValidatingEventArgs e)
{

XPathNavigator xnav = this.CreateNavigator();
string initialLastModified = getfieldValue("/my:myFields/my:InitialLastModified");
string currentLastModified = getfieldValue("/my:myFields/my:CurrentLastModified");
string strIncidentNum = getfieldValue("/my:myFields/my:IncidentNum");
string strDoSaveValidation = getfieldValue("/my:myFields/my:doSaveValidation");
if (strIncidentNum != "" && strDoSaveValidation == "yes")
{
if (initialLastModified != currentLastModified)
{
// you can switch to another view to display an error screen and display a validation error.
this.ViewInfos.SwitchView("SaveConflict");
// switch views
e.ReportError(xnav, false, "The incident has been saved since you opened it.", "Please cancel and re-open it");
}
else
{
// passed validation
}
}
}



Add a changedEvent to the saveMessage Field. This will populate the currentLastModifiedDate field and initiate the validating event on the saveMessage field.

public void saveMessage_Changed(object sender, XmlEventArgs e)
{
// populate current LastModified
string strIncidentNum = getfieldValue("/my:myFields/my:IncidentNum");
populateLastModified(strIncidentNum, "/my:myFields/my:CurrentLastModified");
this.CreateNavigator().SelectSingleNode("/my:myFields/my:doSaveValidation", this.NamespaceManager).SetValue("yes");

}
I frequently use this getFieldValue function:

private string getfieldValue(string xPath)
{
XPathNavigator xnDoc = this.MainDataSource.CreateNavigator();
XPathNavigator xnMyField = xnDoc.SelectSingleNode(xPath, this.NamespaceManager);
if (xnMyField != null)
{
return (string)xnMyField.ValueAs(typeof(String));
}
else
{
return string.Empty;
}

}

There is no way to clear the error. The user will have to close and re-open the form to continue.

Thursday, January 20, 2011

InfoPath Error: Cannot Complete This Action. Please Try Again

I'm working on an InfoPath form that has code behind. I execute a button and it is supposed to do a SharePoint CAML Query and populate some fields on the InfoPath form based on data returned from the query.

When I click the button, I get an exception "Cannot Complete This Action. Please Try Again".  I looked in the SharePoint 2007 ULS logs and I saw the following error:

Unexpected query execution failure, error code 282. Additional error information from SQL Server is included below. "The 'proc_GetTpWebMetaDataAndListMetaData' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead." Query text (if available): "{?=call proc_GetTpWebMetaDataAndListMetaData(?,'ED02FBB9-7901-4CD2-9E42-7E5E1F690C37',?,NULL,1,?,?,6187)}"     
It turned out my CAML query was malformed. I was missing some close element tags. 

I changed <FieldRef Name = "Title"> to <FieldRef Name="Title"/> and that resolved the problem.

Hopefully this will help someone as the error message is pretty cryptic.

Tuesday, January 4, 2011

Boolean Search Web Part

We had a requirement to do SQL type queries on the data in a SharePoint library.  Out of the box, SharePoint supports keyword search, but you can't specify the column in which to search.  I created managed properties to allow searching in specific columns, but the advanced search screen doesn't support wildcard searches.  In order to allow Boolean searches on specific columns with wildcards, I created a webpart to use the SharePoint's FullTextSQLQuery search capability.

The users wanted to be able to use a query-builder user interface - where they could select the search terms and operators with buttons.  They also wanted to be able to enter search criteria for any column in the library.

I created a scope with a rule that only included the documents in a specific document library.
I created managed properties for each of the columns in the document library. 

In the metadata property mappings view, Central Administration > Shared Services Administration > Search Administration > Metadata Properties , click on Crawled Properties




Make sure the included In Index column is set to Yes for each crawled property that is mapped to a managed property.
After the managed properties are set up, a full crawl of the index is required before they are searchable.


There are two ways to search.  You can use the quick search at the top, or the query builder at the bottom.  

The search results are displayed in an SPGridView with paging:


The paging was tricky.  I found this little nugget on MSDN: 

"If the GridView control is bound to a data source control that does not support the paging capability directly, or if the GridView control is bound to a data structure in code through the DataSource property, the GridView control will perform paging by getting all of the data records from the source, displaying only the records for the current page, and discarding the rest. This is supported only when the data source for the GridView control returns a collection that implements the ICollection interface (including datasets)."

So every time the page index changes, the query has to be re-executed because the gridview control discards the records that aren't displayed on the current page.

Now for some code. First I CreateChildControls.  I am also using RenderContents to add the HTML Buttons and TextArea at the bottom of the webpart.  The buttons are executing Javascript so I can add text to the search critera text area without doing a postback.  I registered the javascript. You can see more details about using javascript in a web part on MSDN.

public class ICSSearch : System.Web.UI.WebControls.WebParts.WebPart
{
// strings
public string DestinationPage;
private const string LeftParenScriptKey = "myLeftParenScriptKey";
//add the left paren to the text box - where the cursor is
//get textbox name

/*
* courtesy of http://alexking.org/blog/2003/06/02/inserting-at-the-cursor-using-javascript
* function insertAtCursor(myField, myValue) {
//IE support
if (document.selection) {
myField.focus();
sel = document.selection.createRange();
sel.text = myValue;
}
else if (myField.selectionStart || myField.selectionStart == '0') {
var startPos = myField.selectionStart;
var endPos = myField.selectionEnd;
myField.value = myField.value.substring(0, startPos)
+ myValue
+ myField.value.substring(endPos, myField.value.length);
} else {
myField.value += myValue;
}
*/

private string EmbeddedScriptFormat = " ";

//labels
Label lblID;
// CompareValidator checkID;
Label lblName;
Label lblProduct;
Label lblModel;
Label lblResults;

// Text boxes
TextBox cmpyName;
TextBox EquipID;
TextBox ProductID;
TextBox ModelID;

TextBox queryText;
// Buttons
Button cmdSearch;

Table table;
SPGridView gridView;
DataTable resultsDataTable;
DataTable dt;
int gridViewPageIndex =0;
// store the dataset between pages
DataTable dtPaging;
string strQuery;


public ICSSearch()
{
this.PreRender += new EventHandler(ICSSearch_PreRender);
}

//Client Script registration event
private void ICSSearch_PreRender(object sender, System.EventArgs e)
{
RegisterCommonScript();
}

// this function will register the embedded script
private void RegisterCommonScript()
{
// string location = null;

// Make sure that the script was not already added to the
//page.
EmbeddedScriptFormat = EmbeddedScriptFormat.Replace("[queryTextClientId]", queryText.ClientID);
ClientScriptManager csm = Page.ClientScript;
if (!csm.IsClientScriptBlockRegistered(LeftParenScriptKey))
csm.RegisterClientScriptBlock(GetType(), LeftParenScriptKey,
EmbeddedScriptFormat);

}



Here is the CreateChildControls
protected override void CreateChildControls()
{
//Create a table control to use for positioning the controls

table = new Table();
table.Width = Unit.Percentage(100);
for(int i =0; i<10; i++) { TableRow row= new TableRow(); TableCell cell = new TableCell(); row.Cells.Add(cell); table.Rows.Add(row); } //Create the controls for EquipmentID input lblID = new Label(); lblID.Text = "Equipment ID is:"; lblID.Width = Unit.Pixel(150); EquipID = new TextBox(); EquipID.ID = "EquipID"; //Add the controls for the EquipmentID to the table table.Rows[0].Cells[0].Controls.Add(lblID); table.Rows[0].Cells[0].Controls.Add(EquipID); table.Rows[0].Cells[0].Height = Unit.Pixel(30); //Create the controls for Company Name input. lblName = new Label(); lblName.Text = "Company Name contains:"; cmpyName = new TextBox(); //Add the controls for the Company Name to the table table.Rows[1].Cells[0].Controls.Add(lblName); table.Rows[1].Cells[0].Controls.Add(cmpyName); table.Rows[1].Cells[0].Height = Unit.Pixel(30); // add controls for Model lblModel = new Label(); lblModel.Text = "Model is: "; ModelID = new TextBox(); ModelID.ID = "Model"; // add the controls for Model to the table table.Rows[2].Cells[0].Controls.Add(lblModel); table.Rows[2].Cells[0].Controls.Add(ModelID); table.Rows[2].Cells[0].Height = Unit.Pixel(30); // add controls for Product lblProduct = new Label(); lblProduct.Text = "Product is: "; ProductID = new TextBox(); ProductID.ID = "Product"; // add the controls for Product to the table table.Rows[3].Cells[0].Controls.Add(lblProduct); table.Rows[3].Cells[0].Controls.Add(ProductID); table.Rows[3].Cells[0].Height = Unit.Pixel(30); //Create the search button and add to the table control cmdSearch = new Button(); cmdSearch.ID = "cmdSearch"; cmdSearch.Click += new EventHandler(cmdSearch_Click); cmdSearch.Text = "Search Incidents"; table.Rows[4].Cells[0].Controls.Add(cmdSearch); table.Rows[4].Cells[0].Height = Unit.Pixel(30); //Create a label to display the search message lblResults = new Label(); table.Rows[5].Cells[0].Controls.Add(lblResults); table.Rows[5].Cells[0].Height = Unit.Pixel(30); gridView = new SPGridView(); gridView.AutoGenerateColumns = false; gridView.ID = "GRIDID"; gridView.AllowPaging = true; gridView.AllowSorting = true; gridView.PagerTemplate = null; gridView.PageIndexChanging += new GridViewPageEventHandler(gridView_PageIndexChanging); // table.Rows[6].Cells[0].Controls.Add(gridView); Label queryTextLbl = new Label(); queryTextLbl.Text = "Search Criteria:"; queryText = new TextBox(); queryText.ID = "queryText"; queryText.Columns = 80; queryText.Rows = 3; // table.Rows[6].Cells[0].Controls.Add(queryTextLbl); // table.Rows[7].Cells[0].Controls.Add(queryText); BoundField colTitle = new BoundField(); colTitle.DataField = "CompanyName"; colTitle.HeaderText = "Company"; gridView.Columns.Add(colTitle); BoundField colEquip = new BoundField(); colEquip.DataField = "EquipmentID"; colEquip.HeaderText = "Equipment ID"; gridView.Columns.Add(colEquip); BoundField colModel = new BoundField(); colModel.DataField = "ModelNum"; colModel.HeaderText = "Model"; gridView.Columns.Add(colModel); BoundField colProduct = new BoundField(); colProduct.DataField = "Product"; colProduct.HeaderText = "Product"; gridView.Columns.Add(colProduct); HyperLinkField colLink = new HyperLinkField(); colLink.HeaderText = "Path"; string[] pathFields = { "Path" }; colLink.DataNavigateUrlFields = pathFields ; colLink.DataTextField = "LinkText"; gridView.Columns.Add(colLink); //Add the table to the controls collection this.Controls.Add(table); this.Controls.Add(gridView); this.Controls.Add(queryTextLbl); this.Controls.Add(queryText); gridView.PagerTemplate = null; base.CreateChildControls(); }


Here is the RenderContents:


Here are the actions for the Search button and the paging buttons:

Search button:
void cmdSearch_Click(object sender, EventArgs e)
{
string strName = cmpyName.Text;
string strID = EquipID.Text;
string strProductID = ProductID.Text ;
string strModelID = ModelID.Text;
string strCriteria = queryText.Text;
/*
Validate that the user entered something.
If not, prompt the user to enter an ID or search term.
*/
if (strName == "" && strID == "" && strProductID == "" && strModelID =="" && strCriteria=="")
{
lblResults.Text = "You must enter a criteria for the Search.";

}
else
{
if (strCriteria == "")
{
// if no criteria string was entered, use this :
returnResults(buildSQL(strName, strID, strModelID, strProductID));
}
else
{
returnResults(parseCriteria(strCriteria));

}

}
}
Paging button:

void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
string strName = cmpyName.Text;
string strID = EquipID.Text;
string strProductID = ProductID.Text;
string strModelID = ModelID.Text;
string strCriteria = queryText.Text;

/*
Validate that the user entered something.
If not, prompt the user to enter an ID or search term.
*/
if (strName == "" & strID == "" & strProductID == "" & strModelID == "")
{
lblResults.Text = "You must enter a criteria for the Search.";

}
else
{
if (strCriteria == "")
{
// if no criteria string was entered, use this :
returnResults(buildSQL(strName, strID, strModelID, strProductID));
}
else
{
returnResults(parseCriteria(strCriteria));

}
}

gridViewPageIndex = e.NewPageIndex;
gridView.DataSource = dt;
gridView.PagerTemplate = null;
gridView.DataBind();
gridView.PageIndex = e.NewPageIndex;
}

Here is the code that builds the SQL:
I noticed that the wildcard characters didn't work with the CONTAINS predicate unless the term was enclosed in double quotes.

CONTAINS(CompanyName, 'First*') did not work.

CONTAINS(CompanyName, '"First*"') did work.

private string buildSQL(string stName, string stID, string stModel, string stProduct)
{

//TODO add all fields on form
//TODO Check to see why CONTAINS isn't working for Equipment

string BDCscopeID = "Company Client Support";

//Use the StringBuilder class for the syntax string
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append("SELECT CompanyName,EquipmentID,ModelNum,Product,Path FROM Scope() WHERE (\"Scope\"='");
sbSQL.Append(BDCscopeID);
sbSQL.Append("')");
if (stName != "")

{
//wildcard search doesn't work unless the search term is enclosed in double quotes!
sbSQL.Append(" AND CONTAINS(CompanyName,'\"");
sbSQL.Append(stName);
sbSQL.Append("\"')");
}
if (stID != "")
{
sbSQL.Append(" AND CONTAINS(EquipmentID,'\"");
sbSQL.Append(stID);
sbSQL.Append("\"')");
}
if (stModel != "")
{
sbSQL.Append(" AND CONTAINS(ModelNum,'\"");
sbSQL.Append(stModel);
sbSQL.Append("\"')");
}
if (stProduct != "")
{
sbSQL.Append(" AND CONTAINS(Product,'\"");
sbSQL.Append(stProduct);
sbSQL.Append("\"')");
}
return sbSQL.ToString();
}


Here is the code that returns the results and populates the gridview. The library contains InfoPath forms, so I changed the URL in the search results so that it would open the form in the browser. I would like to put the search results at the bottom of the web part below the criteria builder, but the RenderContents puts everything in the bottom. I'm still working on that.

private void returnResults(string strSQL)
{
FullTextSqlQuery sqlQuery = new FullTextSqlQuery(SPContext.Current.Site);
try
{
strQuery = strSQL;
//Specify result type to return
sqlQuery.ResultTypes = ResultType.RelevantResults;
//Specify the full text search query string
sqlQuery.QueryText = strSQL;
sqlQuery.TrimDuplicates = true;
//Return the search results to a ResultTableCollection
ResultTableCollection results = sqlQuery.Execute();
//Create a ResultTable for the relevant results table

ResultTable relResults = results[ResultType.RelevantResults];

int x = relResults.RowCount;
if (x != 0)
{
resultsDataTable = new DataTable();
resultsDataTable.TableName = "Result";

resultsDataTable.Load(relResults, LoadOption.OverwriteChanges);
// walk through results table and look at the rows

string[] cName = new String[1];
string equip;
string path;
string[] product = new String[1];
string[] modelNum = new String[1];

dt = new DataTable();

// read results into a datatable

dt.Columns.Add("CompanyName");
dt.Columns.Add("EquipmentID");
dt.Columns.Add("ModelNum");
dt.Columns.Add("Product");
dt.Columns.Add("Path");
dt.Columns.Add("LinkText");


foreach (DataRow row in resultsDataTable.Rows)

{
if (row["CompanyName"] != null && row["CompanyName"] != DBNull.Value )
{
// this column returns a string array
cName = (string[])row["CompanyName"];
}
else
{
cName[0] = String.Empty;
}
if (row["EquipmentID"] == null)
{
equip = String.Empty;
}
else
{
equip = row["EquipmentID"].ToString();
}
if (row["Path"] == null)
{
path = String.Empty;
}
else
{
path = row["Path"].ToString();
}
// this column returns a string array
if (row["ModelNum"] != null && row["ModelNum"] != DBNull.Value)
{
modelNum = (string[])row["ModelNum"];
}
else
{
modelNum[0] = String.Empty;
}
// this column returns a string array
if (row["Product"] != null && row["Product"] != DBNull.Value)
{
product = (string[])row["Product"];

}
else
{
product[0] = String.Empty;

}
string currentUrl = SPContext.Current.Site.Url;

dt.Rows.Add(cName[0], equip, modelNum[0], product[0], currentUrl + "/_layouts/FormServer.aspx?XmlLocation=" + path + "&DefaultItemOpen=1", path);

}

gridView.DataSource = dt.DefaultView;
dtPaging = dt;

//Count the number of rows in the table; 0 = no search results

lblResults.Text = x.ToString() + " item(s) found";

}
}
catch (Exception ex1)
{
lblResults.Text = ex1.ToString();
}
finally
{

// sqlQuery.Dispose();
}

}