Joining Two Text Columns in Excell

How can he merge First Name and Last Name into a single column?



Start with a basic formula of  =A2&B2 This will give you the result shown in C2 below:



If you want to change the name to proper case, use the =PROPER() function.  =PROPER(A2&" "&B2)



When your mouse pointer is near the dot, the mouse pointer changes to a cross like this.

Double click and the formula will be copied down to all of the cells in the range.



The TEXT function requires two arguments. (To display Date in formula)

Encoding and Decoding in ASP.net

Encoding: 

encodedUrl = Server.UrlEncode(urlToEncode)

Decoding:

decodedUrl = Server.UrlDecode(urlToDecode)

Force validation in ASP.Net on server side

Below code is used to apply force validation on server side.
First use anyone from below function:

Page_ClientValidate("ValidationGroupName") to validate particular group 
or 
Page_ClientValidate() to check all validator
or
Page.Validate()

Then you can just make use of this If condition

if (PageIsValid) {

  //postback only occurs when ALL validations pass.
  // You can write your further code here.

}

ISDATE in SQL

Syntax : ISDATE ( expression )


Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
This function can be used in SQL Query to validate date. 

Add JavaScript programmatically using RegisterStartupScript during an Asynchronous postback

You can use below code to call JavaScript from your script page:
ScriptManager.RegisterStartupScript(Me.GetType(), "AlertMe", "alert('test');",False)
btnPostback is control, you can also give "this" for general javgaScript.


Also if you do not have ScriptManager, then you can use below code:

Dim someScript As String = "alertMe"

If (Not ClientScript.IsStartupScriptRegistered(Me.GetType(), someScript)) Then

   ClientScript.RegisterStartupScript(Me.GetType(), someScript, "alert('I was called from Content page!')", True)

End If

To get Master Page object in your page.

Use below code in your page to get Master Page object which can be used to access master page property

Put this code on top of your page:
<%@ MasterType VirtualPath="//Master Page Path" %>

No you can use master page object as
Master.Username = "test"
//Here Username is property of Master Page. Similarly you can access methods also.

Group By and Aggregates in .NET DataTable

The function first gets distinct values for group-by column, by creating a data view from source data table and using DataView’s "ToTable" method. It then loops thru these distinct values performing aggregate function on the source table using DataTable’s "Compute" method - Count in this case, but it can easily be replaced with other aggregates:



Function GroupBy(ByVal i_sGroupByColumn As String, ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable
       Dim dv As New DataView(i_dSourceTable)
       'getting distinct values for group column
       Dim dtGroup As DataTable = dv.ToTable(True, New String() {i_sGroupByColumn}) 
       'adding column for the row count
       dtGroup.Columns.Add("Count", GetType(Integer)) 

       'looping thru distinct values for the group, counting
       For Each dr As DataRow In dtGroup.Rows
            dr("Count") = i_dSourceTable.Compute("Count(" & i_sAggregateColumn & ")", i_sGroupByColumn & " = '" & dr(i_sGroupByColumn) & "'")
       Next       
       'returning grouped/counted result
       Return dtGroup
End Function

Page Transition Effect in Internet Explorer.

Adding this code on your .html or .aspx page can generate fadding effect on every postback

try this add on of the page:

<meta http-equiv="Page-Exit" content="progid:DXImageTransform.Microsoft.Fade(duration=.5)" >

Difference between include() and include_once() in PHP

function.php

function foo(){ 
echo 'some code'; 
} 
?>
Global.php

include('FUNCTIONS.PHP');
foo();
?>

Header.php

include('FUNCTIONS.PHP');
include('GLOBALS.PHP');
foo();
?>

now if you try to open HEADER.PHP you will get an error becauseglobal.php includes function.php already. you will get an error saying that function foo() was already declared in global.php, and i also included inHeader.php - which means i have included function.php two times. 

so to be sure i only include function.php only ONE time, i should use the include_once() function, so my Header.php should look like this: 

Header.php

include_once('FUNCTIONS.PHP');
include('GLOBALS.PHP');
?>

now when i open Header.php, i will not get an error anymore because PHP knows to include the file function.php only ONCE  

Handle all javascript errors

Placing this code manage all javascript error from one place

window.onerror=errorhandle
function errorhandle()
{
alert('error occured');
return true
}

Add option to dropdownlist using JavaScript.

//Use following JavaScript to Add option to dropdownlist
function addDropDownOption(Dropdown,text,value)
{
var sportOption = document.createElement(“option”);
sportOption.text = text;
sportOption.value = value;
Dropdown.options.add(sportOption);
}

Export Gridview to Excel File.

By calling following functions, we get Formatted Excel File from GridView. Here gvTestData is Gridview & TestExcel.xls is output excel file.

ExportFormattedGridExcel(gvTestData,”TestExcel.xls”)

public static void ExportFormattedGridExcel(GridView gv,string FileName)
{
foreach (TableCell tc in gv.HeaderRow.Cells)
{
tc.BackColor = System.Drawing.ColorTranslator.FromHtml(“#87102C”); // maroon type
tc.Font.Name = “Trebuchet MS”;
tc.Font.Size = FontUnit.Point(10);
tc.ForeColor = System.Drawing.Color.White;
tc.BorderColor = System.Drawing.Color.White;
tc.BorderWidth = Unit.Point(1);
tc.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
tc.Wrap = false;
tc.Font.Bold = true;
}
foreach (GridViewRow tr in gv.Rows)
{
foreach (TableCell tc in tr.Cells)
{
tc.Font.Name = “Trebuchet MS”;
tc.Font.Size = FontUnit.Point(9);
tc.ForeColor = System.Drawing.ColorTranslator.FromHtml(“#424242″);
tc.BorderColor = System.Drawing.Color.LightGray;
tc.BorderWidth = Unit.Point(1);
tc.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
tc.Wrap = false;
tc.Font.Bold = true;
}
}

HtmlForm form1 = new HtmlForm();
form1.Controls.Clear();
form1.Controls.Add(gv);
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
PrepareControlForExport(gv);
gv.RenderControl(htmlWrite);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(“content-disposition”, “attachment;filename=”+FileName);
HttpContext.Current.Response.ContentType = “Application/x-msexcel”;
//HttpContext.Current.Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;
HttpContext.Current.Response.Write(stringWrite.ToString());
HttpContext.Current.Response.End();

}

/// Prepare rendering of control before export
private static void PrepareControlForExport(Control control)
{

for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is HiddenField)
{
control.Controls.Remove(current);
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}

if (current.HasControls())
{
PrepareControlForExport(current);
}
}
}

Join table data from another Database in Same Instance in SQl Server

Passing dbname in current procedure will display join data of current database table1 data and another database of table2 data.

CREATE PROCEDURE [dbo].[JoinDataFromAnotherDB]
@dbName VARCHAR(200)
AS
DECLARE @sql VARCHAR(max)
SET @sql = ‘SELECT t1.*,t2.*
FROM table1 t1 INNER JOIN ‘+@dbName+’.dbo.table2 t2 ON t1.ID = t2.ID’
EXEC (@sql)

Get screen resolution and set style from javascript

function setLogoOnLowScreenRes()
{
if ((window.screen.width<=800))
{
//you can apply styling for screen resolution 800
document.getElementById("wrapper").style.marginLeft = "58px";
}
else if((window.screen.width<=1024))
{
//you can apply styling for screen resolution 1024
document.getElementById("wrapper").style.marginLeft = "69px";
}
}

To get DataSet from Excel file.

By calling following functions returns dataset

string strErrorMessage = “”;
DataSet ds = GetExcel(path,ref strErrorMessage);

using System.Data.OleDb;
public DataSet GetExcel(string strFileName, ref string strErrorMessage)
{
string strConnection = GetExcelConnectionString(strFileName);
DataTable dtNew = null;
OleDbConnection cnExcelConnection = new OleDbConnection(strConnection);
OleDbCommand cmd = new OleDbCommand();

DataSet ds = new DataSet();
try
{
cnExcelConnection.Open();
dtNew = cnExcelConnection.GetSchema("Tables");

if (dtNew.Rows.Count > 0)
{
foreach(DataRow dr in dtNew.Rows)
{
ds.Tables.Add(ReadExcelData(dr["TABLE_NAME"].ToString(), strConnection, ref strErrorMessage));
}
}
else
{
return null;
}
}
catch (Exception ex)
{
strErrorMessage = ex.Message.ToString();
return null;
}
finally
{
if (!(cnExcelConnection.State == ConnectionState.Closed))
{
cnExcelConnection.Close();
}
cnExcelConnection.Dispose();
}
return ds;
}

public string GetExcelConnectionString(string strFileName)
{
string strConnection = "";
string strFileType = GetFileExtention(strFileName, true);

if (strFileType == ".xlsx")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + (strFileName) + ";" + "Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";

if (strFileType == ".xls")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + (strFileName) + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
return strConnection;
}

public string GetFileExtention(string strFileName, bool blExtensionRequired)
{
if (strFileName == "")
return strFileName;
string[] slots = strFileName.Split('.');
if (blExtensionRequired)
{
return "." + slots[slots.Length - 1];
}
else
{
return slots[slots.Length - 1];
}
}

public DataTable ReadExcelData(string strSheetName, string strConnection, ref string strErrorMessage)

{

OleDbConnection cnExcelConnection = new OleDbConnection(strConnection);

try

{

cnExcelConnection.Open();

DataTable dtImportedData = new DataTable();

dtImportedData.TableName = strSheetName;

OleDbDataAdapter myData = new OleDbDataAdapter("SELECT * FROM [" + strSheetName + "]", cnExcelConnection);

myData.Fill(dtImportedData);

return dtImportedData;

}

catch (Exception ex)

{

strErrorMessage = ex.Message.ToString();

return null;

}

finally

{

if (!(cnExcelConnection.State == ConnectionState.Closed))

{

cnExcelConnection.Close();

}

cnExcelConnection.Dispose();

}

}

Read Web Page Content from ASP.Net

Below code can be used to Read HTML source of any Website in ASP.Net

string strURL = “http://www.yoururl.com/”; 
Uri uri = new Uri(strURL); 
WebRequest request = HttpWebRequest.Create(uri); 
request.Method = WebRequestMethods.Http.Get; 
WebResponse response = request.GetResponse(); 
StreamReader reader = new StreamReader(response.GetResponseStream()); 
string temp = reader.ReadToEnd(); 
response.Close(); 
Response.Write(temp);

Find IPAddress from host name or URL

Below C# code can be used to get IP Address from Host name or UR

string IPs = string.Empty;
try
{
//performs the DNS lookup
IPHostEntry he = Dns.GetHostByName(“yoururl.com”);
IPAddress[] ip_addrs = he.AddressList;
foreach (IPAddress ip in ip_addrs)
{
IPs += ip + ” “;
}
}
catch (System.Exception ex)
{
}