WITH block_name AS ( ), Final statement
includes Select statement with in query or join as needed treating each block as
table. Below are 2 example showing with block usage.
SELECT T1.Col1, T1.col2, T2.col3, T2.col4,
T2.col5 FROM Table1 T1 JOIN Table2 T2 ON Col6 = col7
WHERE col6 IN ( SELECT TA.COLA FROM TableA TA JOIN TableB TB ON TA.COLA1 = tb.ColB1 )
Now to have more organized way to get this query executed using WITH Block
WITH BLOCK1 AS (SELECT TA.COLA FROM
TableA TA JOIN TableB TB ON TA.COLA1 = tb.ColB1),
SELECT T1.Col1, T1.col2, T2.col3, T2.col4, T2.col5 FROM Table1 T1 JOIN Table2 T2 ON Col6 = col7 WHERE col6 IN
( --Note:with block added as select statement & can be used at multiple places.
SELECT block1.cola FROM BLOCK1
)
WITH BLOCK1
AS
(SELECT TA.COLA FROM TableA TA JOIN
TableB TB ON TA.COLA1 = tb.ColB1
),
--Note: Except last with block all with block end with ,
BLOCK2 AS (
SELECT T1.Col1, T1.col2, T2.col3, T2.col4, T2.col5 FROM Table1
T1 JOIN Table2 T2 ON Col6 = col7
)
--final statement only includes Black name.
SELECT * FROM Block2 B2, Block1 B1 WHERE B2.col6 = b1.COLA
Hope this help's you
----
start /w pkgmgr /iu:IIS-WebServerRole;IIS-WebServer;IIS-CommonHttpFeatures;IIS-StaticContent;IIS-DefaultDocument;IIS-DirectoryBrowsing;IIS-HttpErrors;IIS-ApplicationDevelopment;IIS-ASPNET;IIS-NetFxExtensibility;IIS-ISAPIExtensions;IIS-ISAPIFilter;IIS-HealthAndDiagnostics;IIS-HttpLogging;IIS-LoggingLibraries;IIS-RequestMonitor;IIS-HttpTracing;IIS-CustomLogging;IIS-ManagementScriptingTools;IIS-Security;IIS-BasicAuthentication;IIS-WindowsAuthentication;IIS-DigestAuthentication;IIS-RequestFiltering;IIS-Performance;IIS-HttpCompressionStatic;IIS-HttpCompressionDynamic;IIS-WebServerManagementTools;IIS-ManagementConsole;IIS-IIS6ManagementCompatibility;IIS-Metabase;IIS-WMICompatibility;WAS-WindowsActivationService;WAS-ProcessModel;WAS-NetFxEnvironment;WAS-ConfigurationAPI;WCF-HTTP-Activation;WCF-NonHTTP-Activation
using (SPSite osite = new SPSite(SPContext.Current.Web.Url))
{
using (SPWeb oweb = osite.OpenWeb())
{
//listName
SPList Samplelist = oweb.Lists["ListName"];
SPListItem ListItem= Samplelist.AddItem();
oweb.AllowUnsafeUpdates = true;
ListItem["Title"] = "Akshaya Blog Title";
SPFieldUrlValue HyperlinkURLVal = new SPFieldUrlValue();
HyperlinkURLVal.Url ="http://akshaya-m.blogspot.com";
HyperlinkURLVal.Description = "Akshaya Blog Click Here";
ListItem["MyURL"] = HyperlinkURLVal;
//Update List item
ListItem.Update();
}
}
using System.Runtime.InteropServices;
using System.DirectoryServices;
using (DirectoryEntry entry = new DirectoryEntry())
{
entry.Username = "DOMAIN\\LOGINNAME";
entry.Password = "PASSWORD";
DirectorySearcher searcher = new DirectorySearcher(entry);
searcher.Filter = "(objectclass=user)";
try
{
searcher.FindOne();
{
//Add Your Code if user Found..
}
}
catch (COMException ex)
{
if (ex.ErrorCode == -2147023570)
{
ex.Message.ToString();
// Login or password is incorrect
}
}
}
///save button functionlity
void btnChangePassword_Click(object sender, EventArgs e)
{
if (Validate(true))
{
try
{
string AppName = Membership.ApplicationName;
if (.Text.Trim().ToString() != .Text.Trim().ToString())
{.Text = " New and Confirm password Mismatch ! " ; }
else
{
if (Membership.Provider.ChangePassword(.Text, .Text, .Text))
{ //membership provider has Change Password checked in Web.Config
if (Membership.Provider.ChangePasswordQuestionAndAnswer(.Text, .Text, .Text, .Text))
{
//Message: " Your Password Has Been Sucessfully Changed.."
return;
}
}
catch (MembershipPasswordException ee)
{//Exception Message}
}
}
SPSecurity.RunWithElevatedPrivileges(
delegate()
{
try
{
WindowsImpersonationContext aspContext = null;
WindowsIdentity identity = WindowsIdentity.GetCurrent();
aspContext = identity.Impersonate();
ContextOptions o = ContextOptions.Negotiate;
PrincipalContext ctx = new PrincipalContext(ContextType.Domain, this._DomainName, this._DomainName Path);
UserPrincipal UPrinci = new UserPrincipal(ctx);
UPrinci = UserPrincipal.FindByIdentity(ctx, SPContext.Current.Web.CurrentUser.LoginName);
UPrinci.ChangePassword(_oldpassword.Text, _newpassword.Text);
_labelmsg.Text = "Password changed successfully!" ;
} //Try
catch (PasswordException ex)
{ //ex.Message; Catch exception.}
} //Delegate
2. Add the needed colors
3. Create a calculated field name Calendar text Insert formula :
=Color&"|||"&Title
4. Add a CEWP(Content Editor WebPart)
<script language="JavaScript">
var SEPARATOR = "|||";
var nodes, category;
nodes = document.getElementsByTagName("a");
for(var i = 0; i < nodes.length; i++)
{
if(nodes[i].innerText.indexOf(SEPARATOR) != -1)
{
UpdateCalendarEntryText(nodes[i]);
var foundNode = nodes[i];
var trap = 0;
while(foundNode.nodeName.toLowerCase() != "td") {
foundNode = foundNode.parentNode;
trap++;
if(trap > 10)
{
break; // don't want to end up in a loop
}
}
var colour = GetCalendarColour(category);
if(colour ! "")
foundNode.style.background = colour;
}
}
function UpdateCalendarEntryText(anchorNode)
{
var children = anchorNode.childNodes;
for(var i = 0; i < children.length; i++)
{
if(children[i].nodeType == 3 && children[i].nodeValue.indexOf(SEPARATOR)
!= -1)
{
var parts = children[i].nodeValue.split(SEPARATOR);
category = parts[0];
children[i].nodeValue = parts[1];
}
else
UpdateCalendarEntryText(children[i]);
}
}
function GetCalendarColour(desc)
{
var colour;
switch(desc.toLowerCase())
{
case "red":
colour = "#ff0000";
break;
case "blue":
colour = "#0000ff";
break;
case "yellow":
colour = "#ffff00";
break;
case "green":
colour = "#008000";
break;
case "orange":
colour = "#ff8040";
break;
default:
colour = "";
}
nbsp;
return colour;
}
</script>
Bingo
--------
Code
//inialization
private LiteralControl Literal1;
SPSiteoCurrentSite = null;
SPWeb oweb =null;
int count;
DataTable dt =new DataTable();
string strStruct;
In CreateChildControls()
Literal1 = new LiteralControl();
Literal1.Load += new EventHandler(Literal1_Load);
Controls.Add(Literal1);
-------
on load event of Literal Control i'll be calling my Core functionlity which will be binded with this literal control.
MyFunction()
{
try {
count = 0;
//oCurrentSite = new
SPSite("http://syngdcds0138:1111");
oCurrentSite = SPContext.Current.Site;
oweb = oCurrentSite.OpenWeb();
oweb.AllowUnsafeUpdates = true;
SPList oProjectName = oweb.Lists["Tasks"];
SPQuery Query1;
DataTable Dtable;
DataView Dview;
SPListItemCollection items1;
SPUser currentUser = oweb.CurrentUser;
string UNAme = currentUser.Name;
Query1 = new SPQuery();
Dtable = new DataTable();
Dview = new DataView();
Query1.Query = "<Where><Eq><FieldRef Name='AssignedTo' /><Value Type='User'>" +
currentUser.Name + "</Value></Eq></Where>";
items1 = oProjectName.GetItems(Query1);
strStruct = "<div>" + "<table>";
foreach (SPListItem objItem in items1)
{//Pending Completed Overdue
string picture = string.Empty;
if (objItem["Status"].ToString() == "Overdue")
{ picture = @"~/_LAYOUTS/Images/ico-task-overdue.jpg";
}
else if (objItem["Status"].ToString() == "Completed")
{ picture = @"~/_LAYOUTS/Images/ico-task-Completed.jpg";
}
else
{picture = @"~/_LAYOUTS/Images/ico-task-Pending.jpg";
}
string[] CreatedBy = objItem["Created By"].ToString().Split('#');
string AssignedBY = CreatedBy[1];
string DDt = Convert.ToDateTime(objItem["DueDate"].ToString()).ToShortDateString();
strStruct += "<tr>" + "<td colspan=2 align='left'>" + "<img src='" + ResolveUrl(picture) + "'
/> " + "<a style='text-align:right; font-size:12px;' href='" + oweb.Url +
"/Lists/" + oProjectName.Title + "/DispForm.aspx?ID=" + objItem["ID"].ToString() + "&Source=" + oweb.Url + "'>" + objItem["Title"].ToString() + "</a>" + "</td></tr>" + "<tr><td colspan=2 style= 'font-family:Arial; font-size:xx-small;'>Due Date:" + DDt + " Assigned By:"
+ AssignedBY + " </td></tr>";
}
strStruct += "<tr><td></td><td> <a href='" + oweb.Url + "/Lists/" + oProjectName.Title + "/Allitems.aspx'>More>></a>" + "</td></tr>" + "<tr>" + "<td><img
src='~/_LAYOUTS/images/ico-task-Completed.jpg'> Completed " + "<img
src='~/_LAYOUTS/images/ico-task-Pending.jpg'> Pending " + "<img
src='~/_LAYOUTS/images/ico-task-overdue.jpg'> Overdue </td>"
+ "</tr>" + "</table></div>";
Literal1.Text = strStruct;
}
catch (Exception ee) { //Error}
}
Revert your comments for this article
Here I have selected a task list in which I have added a calculated field named “Indicator” and these indicators are calculated from the priority field which is categorized (high, Medium & Low).
Here is the code snippet for calculated field.
="<DIV><IMG
src='/_layouts/images/kpipeppers-"&(3-RIGHT(LEFT(Priority,2),1))&".gif'
/></DIV>"
Now added the following script in content editor webpart
<script type="text/javascript">
var theTDs = document.getElementsByTagName("TD");
var i=0; var TDContent = " ";
Special thanks: Christophe
