#48 Sharepoint RadGrid Export Affecting Subsequent Posts
To correct the issue where your export to Excel stops any further postbacks add the following code to
your image button or button that invokes the export.
OnClientClick=”_spSuppressFormOnSubmitWrapper = true; _spFormOnSubmitCalled = false;”
<asp:ImageButton ID="ibExcelExport" runat="server" CausesValidation="False" ImageUrl="../images/ExcelExport.jpg" onclick="ibExcelExport_Click" OnClientClick="_spSuppressFormOnSubmitWrapper = true; _spFormOnSubmitCalled = false;" />
#47 Simple T-SQL UDF Providing Leading Zeroes
This is simply a conversion of this post to an UDF that provides leading zero support on an int field & returns a varchar(20).
Create FUNCTION [dbo].[udf_LeadingZeroes]
(
@FieldValue int
, @LeadZeroCount int
)
RETURNS varchar(20) AS
BEGIN
Declare @ReturnString varchar(20)
Set @ReturnString = RIGHT(REPLICATE('0', @LeadZeroCount) + CONVERT(varchar(20), @FieldValue), @LeadZeroCount)
RETURN @ReturnString
END
-- Usage
Select dbo.udf_LeadingZeroes(ColName,4) as ColName_With_LeadingZeroes
From SomeTable
#46 Windows Platform Eclipse launch locking issue
If you see this launch error “Locking is not possible in the directory “{IM_HOME}/eclipse/configuration/org.eclipse.osgi”. A common reason is that the file system or Runtime Environment does not support file locking for that location. Please choose a different location, or disable file locking passing “-Dosgi.locking=”none” as a VM argument. ...”
cf. https://www-304.ibm.com/support/docview.wss?uid=swg21455334 “Change the permission for .fileTableLock file to 777" instructions for *nix
Translated for Windows (Win7) go to the {App Root} \configuration\org.eclipse.osgi\.manager directory
Right Click Props on .fileTableLock
Use Security Tab – Select {machine name]\Users and click on Edit button.
Allow full control – click OK
#45 Simple Powershell script to setup an email alert based on a DB query
This is a script destined for a scheduled task/cron that reads the DB, gets a count to alert via email if data is missing. (aka “Miner’s Canary”/ heartbeat)
Credits due:
http://weblogs.asp.net/soever/archive/2006/12/31/powershell-pitfalls-reading-text-from-file-using-get-content.aspx
### Create tempfile
$filename = [System.IO.Path]::GetTempFileName()
### Read DB to query Long table for SCADA data
Invoke-Sqlcmd2 -ServerInstance "DBserver\InstanceName" -Database "DBNAME" -Query "Select '[' + RTRIM(COUNT(*)) +']' from TargetTable Where LogDate = getdate();" | Format-List | Out-File $filename;
### read tempfile as string
$a = [string]::join([environment]::newline, (get-content $filename))
### SMTP call you can put in an IP or FQDN for the SMTP server
if ($a.Contains("[0]"))
{
$smtp = New-Object System.Net.Mail.SMTPClient -ArgumentList smtpservername
$smtp.Send('from@mail.com', 'toaddress@mail.com', 'subject line', "Message Body")
}
### Delete temp file
Remove-Item $filename
### Simple utility to connect to SQL without frills
function Invoke-Sqlcmd2
{
param(
[string]$ServerInstance,
[string]$Database,
[string]$Query,
[Int32]$QueryTimeout=30
)
$conn=new-object System.Data.SqlClient.SQLConnection
$conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $ServerInstance,$Database
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables[0]
}
#44 SQL Reporting Blank Row, avg() & min() For Zero Values
Often just setting your formatting to #.## will make sure you display an empty cell (display nothing) for zero value. If you need a different format mask for whatever reason you can use this routine.
Report Props – select Code and drop this vbs function in. It is referenced in a cell via the prefix Code.
i.e. =Code.BlankZeroRow(Fields!FieldName.Value, “datasetName”),4)
Average & Min values can check for numbers less than reporting precision and not count them so as not to report zero values as minimum and skew average values by inflating the count with zero values collected:
=Min(iif(Fields!fieldName.Value > 0.009, Fields!fieldName.Value, Nothing))
=Sum(Fields!fieldName.Value) / IIf(Count(iif(Fields!fieldName.Value > 0.009, 1, Nothing)) = 0, 1, Count(iif(Fields!fieldName.Value > 0.009, 1, Nothing)))
The precision check is for both formatting decimal places and counting values less than the presented precision to be zero. The formatting can be set on the cell (Text Box Props) so may not be necessary. You can probably better use the MOD function for the minimal value check but I have not tested that out yet.
Function BlankZeroRow(VALUE as Double, Precision as Integer) Dim sRETURN as string sRETURN = "" ' default blank Select Case Precision ' Test Precision Case 0 If (Value > 0.9) then sRETURN = Format(VALUE,"##0") End If Case 1 If (Value > 0.09) then sRETURN = Format(VALUE,"##0.0") End If Case 2 If (Value > 0.009) then sRETURN = Format(VALUE,"##0.00") End If Case 3 If (Value > 0.0009) then sRETURN = Format(VALUE,"##0.000") End If Case 4 If (Value > 0.00009) then sRETURN = Format(VALUE,"##0.0000") End If Case Else If (Value > 0.009) then sRETURN = Format(VALUE,"##0.00") End If End Select BlankZeroRow = sRETURN End Function
Cell definitions for Min/Max/Average/Sum when receiving nulls:
=Min(Fields!Value_Col14.Value) =Max(Fields!Value_Col14.Value) =Avg(iif(IsNumeric(Fields!Value_Col14.Value), CDbl(Fields!Value_Col14.Value), Nothing)) =Sum(iif(Fields!Value_Col14.Value > 0, CDbl(Fields!Value_Col14.Value), Nothing))
#43 Simple Grid bind error ‘These columns don’t currently have unique values’
This is caused usually by untrimmed data – run an update on all selected rows with = ltrim(rtrim([field]))
Telerik’s comments on this :
http://www.telerik.com/help/aspnet/grid/grderrormessages.html
UDF :
http://blog.sqlauthority.com/2008/10/09/sql-server-2008-trim-function-user-defined-function/
#42 Deltek Seq Field in a SPROC
The limitation of not being able to put a non-deterministic rand() call in a SQL User Defined Function forces usage of code like this in a SPROC to generate the SEQ Number that creates uniqueness in Deltek Grid. If anyone has more info and / or a better way please comment.
Apparently you can get around this limitation and create a UDF see this post: http://www.dbtechie.com/sql-server/user-defined-functions-udf/random-thoughts-on-sql-server/
Here is what I put in my custom grid insert. I had to create the rand number in 2 parts otherwise it would use the exponent notation.
Results as 20 char Seq # :: XXXXXX1110300245842
, @ModUser + '1' -- Username 6 chars + Convert(nvarchar(255),100000 + (100000*RAND())) + Convert(nvarchar(255),100000 + (100000*RAND()))
Whole SPROC used for custom grid (Username is ModUser):
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[LoadFunding]
(
@WBS1 varchar(30)
, @Username varchar(6)
, @custFundingRequestedAmount decimal(19,5)
, @custFundingNotes varchar(1000)
, @custFund varchar(255)
, @custFundingReceivedAmount decimal(19,5)
)
as
INSERT INTO [Projects_Funding]
([WBS1]
,[WBS2]
,[WBS3]
,[Seq]
,[custFundingRequestedAmount]
,[custFundingNotes]
,[custFund]
,[custFundingReceivedAmount]
)
VALUES
(
@WBS1
,' '
,' '
, @Username + '1' -- Create SEQ# (20 chars i.e. xxxxxx1262113234429)
+ Convert(nvarchar(255),100000 + (100000*RAND()))
+ Convert(nvarchar(255),100000 + (100000*RAND()))
, @custFundingRequestedAmount
, @custFundingNotes
, @custFund
, @custFundingReceivedAmount
)
#41 Breadcrumb Control based on a SPList
Based on the prior post #40 You can plumb in a SPList to feed the urls to match on and decide to ignore, replace or substitute 2 nodes for a directory path. For help on SPMetal & SP Linq see: http://socialsp.com/2009/12/11/having-fun-with-the-new-linq-to-sharepoint-on-sharepoint-2010-sp2010/
SP List to Feed URL matching (done using a simple switch() in the prior post)
using System;
using System.Collections.Specialized;
using System.IO;
using System.Text;
using System.Web.UI;
using System.Linq;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Linq;
namespace SPBreadCrumb.ControlTemplates.SPBreadCrumb
{
public partial class BWBreadcrumb : UserControl
{
private const string siteURL = @"http://Sharepoint/Site";
protected SPMetalDataContext TT = new SPMetalDataContext(siteURL);
StringBuilder sbResult = new StringBuilder(); // Holds the Breadcrumb HTML.
StringBuilder sbBcUrl = new StringBuilder(); // Holds the URL of the breadcrumb.
// Dir are appended in succession to the root.
private HybridDictionary labels = new HybridDictionary(); // Holds the "friendly" directory names.
private string Separator = "> ";
protected void Page_Load(object sender, EventArgs e)
{
string strHostUrl = "http://" + Page.Request.ServerVariables["HTTP_HOST"] + "/";
sbBcUrl.Append(strHostUrl);
string scriptName = Page.Request.ServerVariables["SCRIPT_NAME"];
string strScriptDir = Path.GetDirectoryName(scriptName);
bool bHasExtension = Path.HasExtension(scriptName);
strScriptDir = strScriptDir.Substring(1);
string[] strDirs = strScriptDir.Split('\\');
int nNumDirs = strDirs.Length;
// Node setup on Breadcrumb
bool FirstNode = true;
foreach (string strDirName in strDirs)
{
sbBcUrl.Append(strDirName + "/"); // URL SETUP
sbResult.Append(buildLinkNode(FirstNode, sbBcUrl.ToString(), strDirName));
FirstNode = false;
}
sbResult.Append(Path.GetFileName(scriptName)); // Show File Name
lblBC.Text = sbResult.ToString();
}
private string buildLinkNode(bool FirstNode, string URL, string DirName)
{
string sResults = "";
try
{
// Run SPMetal utility to get object to reference site
using (SPMetalDataContext ctx = new SPMetalDataContext(SPContext.Current.Web.Url))
{
ctx.ObjectTrackingEnabled = false;
// SPList Reference Here<>
EntityList<BWInternal_BreadcrumbItem> BC = ctx.GetList<Internal_BreadcrumbItem>("BWInternal_Breadcrumb");
var query = from c in BC.ToList()
select c;
foreach (var bc in query)
{
if (bc.ReadURL.ToString().ToLower() == URL.ToLower())
sResults = setupBCNode(FirstNode, bc.Node1URL, bc.Node1Label, bc.Node2URL, bc.Node2Label);
}
}
}
catch {}
return(sResults);
}
private string setupBCNode(bool FirstNode, string URL1, string Label1, string URL2, string Label2)
{
string s = "";
if (!FirstNode)
s += Separator;
s = "<a href='" + URL1 + @"' > " + Label1 + @"</a> ";
if (URL2 == null || URL2.Trim().Length > 0)
s += Separator + " <a href='" + URL2 + @"' > " + Label2 + @"</a> ";
return (s);
}
}
}
#40 Taking Control of the Sharepoint 2010 Breadcrumb
cf. Linking This BreadCrumb control to a SPList in Post #41
Working with the default breadcrumb in SP2010 I found the following issues: If you have site pages and want to navigate from one to the other you may want to show a hierarchy that does not physically exist in SP (i.e. pages all in site pages). In addition, certain directories in the path used by SP may lead the user to nowhere useful. Below I have code for a user control that I have added to the v4.masterpage. It is deployed as a feature to ControlTemplates (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\CONTROLTEMPLATES). In VS2010 add blank Sharepoint 2010 project and add new item: Usercontrol.
First the User Control. This is based on an older project http://www.codeproject.com/KB/aspnet/breadcrumbs.aspx. I use a simple switch statement to match URL and decide on how to present the node and target URL but hope to read a SPList or a SiteMap for a more manageable solution going forward.
The result of this is a breadcrumb looking like (& linked to) whatever you want. You can ignore a virtual directory, define the label & link or even define multiple ‘nodes’ on your breadcrumb for each directory in the path.
using System;
using System.Collections.Specialized;
using System.IO;
using System.Text;
using System.Web.UI;
namespace SPBreadCrumb.ControlTemplates.SPBreadCrumb
{
public partial class Breadcrumb : UserControl
{
StringBuilder sbResult = new StringBuilder(); // Holds the Breadcrumb HTML.
StringBuilder sbBcUrl = new StringBuilder(); // Holds the URL of the breadcrumb.
// Dir are appended in succession to the root.
private HybridDictionary labels = new HybridDictionary(); // Holds the "friendly" directory names.
private bool ShowFileName = true;
private string Separator = ">";
private string RootUrl = @"/";
private string RootName = "Home ";
protected void Page_Load(object sender, EventArgs e)
{
// Create the root breadcrumb (corresponds to the root directory).
//
sbResult.Append("<a href=\"" + RootUrl + "\">" + RootName + "</a>");
//
// Get the site URL. Use a StringBuilder to hold the URL so that we can append
// directory names in succession.
//
string strHostUrl = "http://" + Page.Request.ServerVariables["HTTP_HOST"] + "/";
sbBcUrl.Append(strHostUrl);
//
// Break up the path parts into an array (directory name(s) and/or file name).
//
string scriptName = Page.Request.ServerVariables["SCRIPT_NAME"];
string strScriptDir = Path.GetDirectoryName(scriptName);
bool bHasExtension = Path.HasExtension(scriptName);
//
// Create breadcrumb HTML for the directory name(s).
// *** Note: Remove the first "\"; otherwise, when you split the string, the first item in the
// array is an empty string.
//
strScriptDir = strScriptDir.Substring(1);
string[] strDirs = strScriptDir.Split('\\');
int nNumDirs = strDirs.Length;
//
// Splitting the string "\" (root directory) produces an array with an empty string as its only element. If there is
// only one element and it is the empty string, then we are in the root directory. If the user has chosen
// to display the file name, then the separator and the file name will be appended to the Web site root
// Name/URL.
//
// If, however, there is one element and it is NOT the empty string, then we are one directory deep.
// The else statement is executed and the separator character is inserted.
//
string strSeparator = "";
if (1 == nNumDirs && (strDirs[0] == ""))
{
strSeparator = "";
}
else
{
strSeparator = String.Format(" {0} ", Separator);
}
// Node setup on Breadcrumb
foreach (string strDirName in strDirs)
{
sbBcUrl.Append(strDirName + "/"); // URL SETUP
// LABELD BC LINK SETUP
sbResult.Append(buildLinkNode(sbBcUrl.ToString(), strDirName));
}
//
// If the user wants to display file names, do it now.
//
if (false != ShowFileName)
{
sbResult.Append(String.Format(" {0} {1}", Separator, Path.GetFileName(scriptName)));
}
lblBC.Text = sbResult.ToString();
}
/// <summary>
/// comes back with "> <a href='TranslatedURL'>NodeName</a>"
/// </summary>
/// <param name="URL"></param>
/// <param name="DirName"></param>
/// <returns></returns>
private string buildLinkNode(string URL, string DirName)
{
string sReturn = "";
// http://serverName/teamtest/SitePages/Home.aspx
switch (URL.ToLower().Trim())
{
case @"http://serverName/teamtest/sitepages/":
sReturn += setupDoubleNode(@"http://www.google.com", "Node A", @"http://www.cnn.com", "Node B");
break;
case @"http://localhost:49169/intranet30/":
sReturn += setupSingleNode(@"http://www.bing.com", "Node (Intranet)");
break;
case @"http://serverName/teamtest/":
sReturn += setupSingleNode(@"http://intranet", "Node (Root)");
break;
default:
sReturn += setupSingleNode(@"http://intranet", "Node (" + URL.ToLower().Trim() + ")");
break;
}
return (sReturn);
}
private string setupSingleNode(string URL, string LINK)
{
return (Separator + " <a href='" + URL + @"' > " + LINK + @"</a> ");
}
private string setupDoubleNode(string URL1, string LINK1, string URL2, string LINK2)
{
string s = Separator + " <a href='" + URL1 + @"' > " + LINK1 + @"</a> ";
s += Separator + " <a href='" + URL2 + @"' > " + LINK2 + @"</a> ";
return (s);
}
}
}
9/21/10 Note: By changing the visible=false attribute you can run into an odd error
“You must specify a value for this required field” See this link for details for a css workaround.
The Masterpage requires a User Control reference, placement of the actual control and visible=’false’ on existing breadcrumbs.
<%@ Register TagPrefix="wssuc" TagName="Breadcrumb" src="~/_controltemplates/SPBreadcrumb/BWBreadcrumb.ascx" %>
Place the control in the banner – use SPD in split mode to navigate and turn off visibility on standard controls until you see what you like.
<td class="s4-titletext"><wssuc:BreadCrumb ID="Test1" runat="server" /> ... <SharePoint:SPLinkButton Visible="false" ... <SharePoint:ClusteredDirectionalSeparatorArrow Visible="false" runat="server"/>
#39 Link External Help PDF to Deltek 6.1 Page
This is an extension of a prior post: http://dmgorman.wordpress.com/2009/07/15/29-popup-under-deltek-6-x-smart-client/ The difference here is that we want to put a blue ? icon on a page and have that launch a pdf or some other resource like an excel file on the onclick event. It will not launch the pdf directly so we setup a simple redirect page that grabs the inbound pdf URL as a query string variable and launches the target pdf. To clean up things this redirect page is closed out.
Step One: Create HTML Redirection Page on a webserver. I use basic javascript but you probably can find some slick jQuery code out there.
redir.htm
<html>
<head>
<base target="_self" />
<script>
function launch() {
var resourceURL = getQueryVariable("r");
window.open(resourceURL);
}
// A U T O M A T I C A L L Y C L O S E P A G E
// js credit due: http://bytes.com/topic/javascript/answers/145577-self-close-window-without-dialog-close-yes-no
var howLong = 1000; // one second
var t = null;
function closeMe(){
t = setTimeout("self.close()",howLong);
}
// G R A B T A R G E T U R L
// js credit due: http://www.groupsrv.com/dotnet/post-428830.html
function getQueryVariable(variable)
{
var query = window.location.search.substring(1);
var vars = query.split("&");
for (var i=0;i<vars.length;i++)
{
var pair = vars[i].split("=");
if (pair[0] == variable)
{
return pair[1];
}
}
}
</script>
</head>
<body onload="launch();closeMe();">
<!-- Link To Close But Page Should Close Within 1 second via closeMe() -->
<a href="javascript:self.close()">click here to close</a>
</body>
</html>
STEP TWO: Add a Blue Help button on the target page.
Change the Help Text properties by Control-V paste
do not click on the ellipsis … and paste in the following
as a single line (no line breaks):
<script>javascript:popup_window=window.showModalDialog("http://MyWebserver/redir.htm?r=http://myHelpFile.pdf",'_parent', "dialogHeight: 100px;");</script>
The onclick event will launch 2 windows and then the target resource url in the ‘r’ querystring variable. The second page redir.htm should self close in a 1 second. I have not been successful automatically removing the base window that is launched – it remains to be manually closed by the user. Please post a comment if you have found a solution to that issue.
