Image Gallery for each List Item in SP 2010

We were using a SharePoint 2010 List to store all details of various office events. One of the requirements was to store images (multiple) for each event / list item. We also needed to display all images for an event in the Display Form. The following approach was used to implement this.

We created a Picture Library to store the images related to all the List Items and added a Lookup Column for the Title of the List and an associated column to store the ID of the List Item. The lookup column of Title was added, so that users can associate the photos uploaded in the Picture Library by selecting the Title text, and do not have to go back to fetch the ID. Selecting the Title lookup, SharePoint 2010 will automatically populate the ID field.

COlumn

Now, we created a custom display form for our Event List and added a XSLTListViewWebPart for the Picture Library. If you closely look the URL of the page which displays the List Item, SharePoint 2010 passes the ID field of the List Item as query string. We planned to filter the Picture Gallery in the Display form using the ID field from the query string. For this, you need to do the following :

  1. Open the Display Form in SharePoint Designer 2010
  2. Select the XSLTListViewWebPart and click on Options > Parameters and add a new parameter with type Query String and Query String Variable ID DataViewParameter
  3. Next, click on Filter and add a Filter Criteria with the EventTitle:ID fields equal to the [ID] parameter added in Step 2 (above)Filter

Thats all we need to display the pictures associated with a List Item in the Display form. We need to ensure the Event Title field is populated correctly for all the images uploaded to the Photo Library.

Featured Content Web Part

We had a requirement to display a featured content web-part in the home page of their SharePoint 2010 website. The also required admin should have the option to change the featured content without any html changes.

We created a custom list with the following columns

Column Name Type
Title Single Line of Text
Description Multiple lines of Text
Link URL Hyperlink or Picture
Image Hyperlink or Picture

Next we added a Content Query Web part in the relevant section of the home-page and set the Source to the custom list defined above. The layout of the contents of the featured content was similar to the ‘Image on Right’ item style (pre-defined), however we required a ‘More’ link in the web-part which will be a hyperlink to the actual article for the featured item.

For this we needed to modify the ‘Style Library/XSL Style Sheets/ItemStyle.xsl’ file by opening the site in SharePoint Designer 2010 and defining a custom item style as below. A new xsl:Template tag  wih name=‘CustomFeaturedCotnentItemStyle’ was added by copying from the xsl:Template name= ‘ImageRight’. In order to display the ‘More>>’ link, the section <br/><div><a href=”{$SafeLinkUrl}”>More >></a></div> was added inside the node div class=’Description’

<xsl:template name="CustomFeaturedContentItemStyle" match="Row[@Style='KCCIFeaturedContentItemStyle']" mode="itemstyle">
  <xsl:variable name="SafeLinkUrl">
    <xsl:call-template name="OuterTemplate.GetSafeLink">
      <xsl:with-param name="UrlColumnName" select="'LinkUrl'"/>
    </xsl:call-template>
  </xsl:variable>
  <xsl:variable name="SafeImageUrl">
    <xsl:call-template name="OuterTemplate.GetSafeStaticUrl">
      <xsl:with-param name="UrlColumnName" select="'ImageUrl'"/>
    </xsl:call-template>
  </xsl:variable>
  <xsl:variable name="DisplayTitle">
    <xsl:call-template name="OuterTemplate.GetTitle">
      <xsl:with-param name="Title" select="@Title"/>
      <xsl:with-param name="UrlColumnName" select="'LinkUrl'"/>
    </xsl:call-template>
  </xsl:variable>
  <div class="item">
    <xsl:if test="string-length($SafeImageUrl) != 0">
      <div class="image-area-right">
        <xsl:if test="$ItemsHaveStreams = 'True'">
          <xsl:attribute name="onclick">
            <xsl:value-of select="@OnClickForWebRendering"/>
          </xsl:attribute>
        </xsl:if>
        <xsl:if test="$ItemsHaveStreams != 'True' and @OpenInNewWindow = 'True'">
          <xsl:attribute name="onclick">
            <xsl:value-of disable-output-escaping="yes" select="$OnClickTargetAttribute"/>
          </xsl:attribute>
        </xsl:if>
        <img class="image" src="{$SafeImageUrl}" title="{@ImageUrlAltText}">
          <xsl:if test="$ImageWidth != ''">
            <xsl:attribute name="width">
              <xsl:value-of select="$ImageWidth" />
            </xsl:attribute>
          </xsl:if>
          <xsl:if test="$ImageHeight != ''">
            <xsl:attribute name="height">
              <xsl:value-of select="$ImageHeight" />
            </xsl:attribute>
          </xsl:if>
        </img>
      </div>
    </xsl:if>
    <div class="FeatureTitle">
      <xsl:call-template name="OuterTemplate.CallPresenceStatusIconTemplate"/>
      <xsl:if test="$ItemsHaveStreams = 'True'">
        <xsl:attribute name="onclick">
          <xsl:value-of select="@OnClickForWebRendering"/>
        </xsl:attribute>
      </xsl:if>
      <xsl:if test="$ItemsHaveStreams != 'True' and @OpenInNewWindow = 'True'">
        <xsl:attribute name="onclick">
          <xsl:value-of disable-output-escaping="yes" select="$OnClickTargetAttribute"/>
        </xsl:attribute>
      </xsl:if>
      <xsl:value-of select="$DisplayTitle"/>
    </div>
    <br/>
    <div class="description">
      <xsl:value-of select="@Description" />
      <br/>
      <div>
        <a href="{$SafeLinkUrl}">More >></a>
      </div>
    </div>
  </div>
</xsl:template>

Some Office 2010 Tips

As I am doing a lot of “creative” technical writing over the last few weeks, and learning some quick tips on Office 2010 – Word, Excel, etc. Just wanted to put all of those in a quick post for future reference

Put a “tick” mark symbol in Word

Select insert -> symbol
Select the font as a wingdings, from that give the character code has 252 – you will get the tick mark symbol.

Split table cell diagonally

In the Menu bar, click on Table > Draw Table. The cursor changes to a pencil symbol. With that click (near) one corner of the cell you want to split diagonally, drag to (near) the other corner, release, and done !

Update User Profile information in SharePoint 2010 from .NET Code

We can use the following code-snippet to update user profile information in SharePoint 2010 directly from code. You need to add reference to the Microsoft.Office.Server.UserProfiles namespace for the UserProfileManager class. Since, we were using Form Based authentication, we had to append the MembershipProvider name to the User Account Name

using (SPSite site = new SPSite(“http://sp2010:1004&#8221;))
{
using (SPWeb web = site.OpenWeb())
{
SPServiceContext serviceContext = SPServiceContext.GetContext(site);
UserProfileManager profileManager = new UserProfileManager(serviceContext);

string sAccount = “i:0#.f|fbamembershipprovider|aritra”;
UserProfile u = profileManager.GetUserProfile(sAccount);
u[PropertyConstants.WorkPhone].Value = “6612 1000”;
u[PropertyConstants.AboutMe].Value = “Test aboutme at 1405”;
u[PropertyConstants.HomePhone].Value = “1234”;
u[PropertyConstants.CellPhone].Value = “5678”;
u.Commit();
}
}

Display Logged in User Name in Share Point 2010 Master Page for FBA using Javascript

We tried to display the user name of the logged in user in a SharePoint 2010 Master Page using Asp:LoginView control, but it always displays as “ i:0#.f|acaspnetsqlmembershipprovider|aritra”. Our site was set up for Form Based Authentication.

Following is the solution which worked fine for us, and it uses JavaScript only for this:-

1.            Open the web.config of the SharePoint 2010 site and add the following in the configuration/SharePoint/PageParserPaths configuration section:

<PageParserPaths>

<PageParserPath VirtualPath=”~/_layouts/masterpages/myCustomMaster.master” CompilationMode=”Always” AllowServerSideScript=”true” />

</PageParserPaths>

This is because we are adding inline code in a master-page

2.            Insert the following markup in the appropriate section of your Master Page

Welcome, <script type=”text/javascript”>var loginName = “<%= SPContext.Current.Web.CurrentUser.Name %>”;document.write(loginName);</script>

How to use Excel VBA to add data to SQL DB Table

Steps to use Excel VBA / Macro to insert records into SQL Database Table.
 
1. Launch the Visual Basic Editor from Excel and add reference to the "Microsoft ActiveX Data Objects Library" from Tools > References
 
2. In the Editor window, create a new module and add the following code to create the connection settings for the SQL Server
Dim oConn As ADODB.Connection
Private Sub ConnectDB()
    Set oConn = New ADODB.Connection
    oConn.Open "Provider=SQLOLEDB; " & _
            "Data Source=DBServerName; " & _
            "Initial Catalog=DBInstanceName;" & _
            "User ID=sa;
Password=sa;"
End Sub
3. Add the following code, to use the connection object created in Step2 and generate the sql insert statement to add records to the SQL Database table
Dim rs As ADODB.Recordset

Public Sub InsertData()
    ConnectDB
    Set rs = New ADODB.Recordset
   
    Dim strSql As String    
    strSql = "insert into table1 values (‘a’,’b’)"     ‘Put your SQL Insert statement here
    rs.Open strSql, oConn, adOpenDynamic, adLockOptimistic

   
    CloseDBConn

End Sub

Private Sub CloseDBConn()
    oConn.Close
End Sub

Thats all we need here ! 

Add VBA Code and References to Excel Document

 

We can add VBA Code as
well as References (required by the VBA code to compile) to any Excel Document
using .NET Code.

Method to Add VBA Code

public
static void
AddVBACode(Workbook Workbook,
String VBCode, String
ModuleName)

{

Microsoft.Vbe.Interop.VBProject
Project = Workbook.VBProject;

     
Microsoft.Vbe.Interop.
VBComponent Module =
Project.VBComponents.Add(Microsoft.Vbe.Interop.
vbext_ComponentType.vbext_ct_StdModule);

     
Module.Name = ModuleName;

     
Microsoft.Vbe.Interop.
CodeModule Code =
Module.CodeModule;

     
Code.AddFromString(VBCode);

}

(We can change
the Module Type to Standard Module or Class Module, by changing the
Microsoft.Vbe.Interop.vbext_ComponentType enum in the add method)

Code Snippet to
add the references

The references
can be added using either the file path or the GUID.
Its always recommended to
check, if the Reference is already added in the target document, before
attempting to add it programmatically. The collection
Workbook.VBProject.References collection contains the
References already added in the Workbook document object.

Workbook.VBProject.References.AddFromFile("C:\\Program Files\\Microsoft
Office\\Office12\\XLSTART\\my-Add-In.xla"
);

Workbook.VBProject.References.AddFromGuid("{420B2830-E718-11CF-893D-00A0C9054228}", 1, 0);

To
retrieve the GUID’s for the References to be added, we can add the references
to any Excel Workbook and run the following macro :-

Option
Explicit

Sub ListGUID()
    Dim Ref As Object, N&
    Sheets.Add
    Application.ScreenUpdating = False
    Cells.Font.Size = 8
    With Rows("1:1")
          .Font.Size = 9
          .Font.Bold = True
          .Font.ColorIndex = 9
          .Font.Underline = xlUnderlineStyleSingle
    End With
    [A1:D1] = _
    Array("Description", "Name", "Use:
ThisWorkbook.VBProject.References.AddFromGuid", "Path")
    For Each Ref In ActiveWorkbook.VBProject.References
          [A65536].End(xlUp).Offset(1, 0) =
Ref.Description
          [B65536].End(xlUp).Offset(1, 0) = Ref.Name
          [C65536].End(xlUp).Offset(1, 0) =
"""" & Ref.GUID & """" &
", " & Ref.Major & ", " & Ref.minor
          [D65536].End(xlUp).Offset(1, 0) =
Ref.FullPath
    Next
    Columns("A:D").EntireColumn.AutoFit
    Set Ref = Nothing
End Sub

Check if a Database Object is being used in Stored Procedure

In order to find if a database object (table name, view, linked server, etc.) is used across in any of the stored procedure in a database, we can use the following query :-

select * from sys.objects
where object_id in(
select object_id from sys.all_sql_modules
where object_id in(select  object_id from sys.procedures)
and definition like ‘%myObject%’)
order by name

Please replace the myObject string with your required object name / string.

Call VBA Function directly from Ribbon

To call a VBA function directly from the VSTO Ribbon, we can amend the Ribbon xml file as follows :-

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"&gt;
  <ribbon startFromScratch="true">
    <tabs>
      <tab id="CustomTab" label="My Tab">
        <group id="SimpleControls" label="My Group">
          <button id="Button1" imageMso="HappyFace" size="large"
            label="Large Button"
            onAction="ThisWorkbook.MyMacro" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Miscellaneous VSTO Items

The Office Add In project which I have been working upon in the last few months, is nearing completion and I am putting in this post some of the slightly trivial VSTO Technical issues which I managed to resolve.
 

Get Page Number of Word Range Object 

int num = (int)range.get_Information(WordInterop.WdInformation.wdActiveEndPageNumber); 

 

Get the Line Number of the first line in the Word Range Object 

int num = (doc.Range(ref zero, ref so)).ComputeStatistics(Microsoft.Office.Interop.Word.WdStatistic.wdStatisticLines);

           

Get the maximum mumber of lines in a Word document object

 

doc.Activate(); 

object oPage = "\\page"; /* \page is a predefined bookmark */

 

int num = (doc.Bookmarks.get_Item(ref oPage).Range).ComputeStatistics(Microsoft.Office.Interop.Word.WdStatistic.wdStatisticLines);

 

int no_of_pages = doc.ComputeStatistics(Microsoft.Office.Interop.Word.WdStatistic.wdStatisticPages, ref missing);

 

oPage = null;

int max_lines =  num * no_of_pages;

 

 

Close any of the Word 2007 built-in Task Panes

 

foreach (WordInterop.TaskPane tp in Globals.WordAddIn.Application.TaskPanes)

            {

                tp.Visible = false;

            }

Create your own website, free on WordPress.com.Create your own website.
Start free