Saturday 27 August 2011

How to Match two database table data in a single query

Using below query we can compare two table two (Placed in two different database)

 select * from DB1Table1 where ID not in (select DBNAME2.dbo.DB2Table1.ID from DBNAME2.dbo.DB2Table1)

How to format amount in SQL Server using query?

select Convert(varchar,Convert(money,15000),1)

How to convert custom date format using Convert in SQL Server?

select Convert(varchar(10),getdate(),102)


101 - mm/dd/yyyy
102 - yyyy.mm.dd
103 - dd/mm/yyyy
104 - dd.mm.yyyy
105 - dd-mm-yyyy
106 - dd mon yyyy
107 - Mon dd, yyyy
108 -  HH:mm:ss
109 – MMM dd yyyy h:mm:ss:mssN                        ex: Aug 27 2011  3:40:15:577P
110 - mm-dd-yyyy
111 - yyyy/mm/dd
112 – yyyymmdd
113 – dd MMM yyyy HH:mm:ss:mss                       ex: 27 Aug 2011 15:42:38:123
114 – HH:mm:ss:mss                                                ex: 15:43:47:000

Tuesday 2 August 2011

Get last day of Previous/Current/Next Month in SQL Server

--Last day of previous month--
SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))

--Last Day of Current Month
SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))

--Last Day of Next Month
SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)))

Monday 25 July 2011

How to print page content using javascript?

<html>
<head id="Head1" runat="server">
<title>Print using Javascript</title>
<script type="text/javascript">
WinPrint.document.write(prtContent.innerHTML);
WinPrint.document.close();
WinPrint.focus();
WinPrint.print();
WinPrint.close();
function CallPrint(strid)

{
       var prtContent = document.getElementById(strid);
      var WinPrint = window.open('', '', 'left=0,top=0,width=300,height=300,toolbar=0,scrollbars=0,status=0');
      //prtContent.innerHTML = strOldOne;
}
</
script>
</head>
<body>
<form id="form1" runat="server">
<div>
<div id="divPrint">
           <asp:Label ID="Label1" runat="server" Text="UserName:"></asp:Label>
           <asp:TextBox ID="Textbox1" runat="server"></asp:TextBox><br />
           <asp:Label ID="Label2" runat="server" Text="Password: "></asp:Label>
           <asp:TextBox ID="Textbox2" runat="server"></asp:TextBox></div>
           <asp:Button ID="Button2" runat="server" Text="Print" OnClientClick="CallPrint('divPrint')" onclick="Button2_Click" />
</div>
</form>
</body>
</html>

Wednesday 6 July 2011

How to check /Un Check Gridview checkbox column based on header check box?

Client Side

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Grid View Checkbox column Example</title>   
    <script type="text/javascript">
        function SelectAll(id)
        {
            //get name of gridview control
            var grid_name = document.getElementById("<%= GridView1.ClientID %>");
           
            //specify checkbox column index in this cell variable
            var cell;

            if (grid_name.rows.length > 0)
            {
                //Get Check box column index and check/un check grid view check boxes
                for (i = 1; i < grid_name.rows.length; i++)
                {
                    //Get first column values
                    cell = grid_name.rows[i].cells[0];
                   
                    //Get childNodes in the cell checked/unchecked
                    for (j=0; j<cell.childNodes.length; j++)
                    {                           
                        if (cell.childNodes[j].type =="checkbox")
                        {                      
                            cell.childNodes[j].checked = document.getElementById(id).checked;
                        }
                    }
                }
            }
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div> 
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowDataBound="GridView1_RowDataBound">
            <Columns>
                <asp:TemplateField>
                    <HeaderTemplate>
                        <asp:CheckBox ID="SelectAll" runat="server" Text="Select All" />
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:CheckBox ID="SelectAll" runat="server" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="empname" HeaderText="Employee Name" />
                <asp:BoundField DataField="sal" HeaderText="Salary" />
            </Columns>
        </asp:GridView>   
    </div>
    </form>
</body>
</html>


Server side

using System.Data;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("eno");
            dt.Columns.Add("empname");
            dt.Columns.Add("sal");

            DataRow dr = dt.NewRow();
            dr[0] = "1";
            dr[1] = "Ravindran";
            dr[2] = "15000";
            dt.Rows.Add(dr);

            DataRow dr1 = dt.NewRow();
            dr1[0] = "2";
            dr1[1] = "Ramesh";
            dr1[2] = "7800";
            dt.Rows.Add(dr1);

            DataRow dr2 = dt.NewRow();
            dr2[0] = "3";
            dr2[1] = "Ganesh";
            dr2[2] = "9000";
            dt.Rows.Add(dr2);

            DataRow dr3 = dt.NewRow();
            dr3[0] = "4";
            dr3[1] = "Arun ";
            dr3[2] = "45000";
            dt.Rows.Add(dr3);

            DataRow dr4 = dt.NewRow();
            dr4[0] = "5";
            dr4[1] = "Allen";
            dr4[2] = "25000";
            dt.Rows.Add(dr4);

            DataRow dr5 = dt.NewRow();
            dr5[0] = "6";
            dr5[1] = "Michal";
            dr5[2] = "23000";
            dt.Rows.Add(dr5);


            //Binding datasource to Grid View control
            GridView1.DataSource = dt;
           GridView1.DataBind();
        }
    }
  
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        //Below code is used to check/uncheck grid view check boxes
        if (e.Row.RowType == DataControlRowType.Header)
        {
            //Find the checkbox control in header and add an attribute
            ((CheckBox)e.Row.FindControl("SelectAll")).Attributes.Add("onclick", "javascript:SelectAll('" + ((CheckBox)e.Row.FindControl("SelectAll")).ClientID + "')");
        }
    }
}

Output Shows like this


How to prevent copy contents from webpage using Javascript?

If you want to prevent copy whole content of web page then use on dragstart as false and on select start as false inside of body tag like below.

<body ondragstart="return false" onselectstart="return false">

// Your content placed here

</body>

If you want prevent cut, copy, paste text in your text box then try like this way

<asp:TextBox ID="TextBox1" runat="server" oncopy="return false" onpaste="return false"
oncut="return false"></asp:TextBox>

Friday 24 June 2011

How to get month name from from SQL Server datetime field?

1) Below query return month name (from January to December) based on your record date.

select DATENAME(month,fieldname) from tablename 

2) Below query is used to get only three charecter from the selected month
select Convert(varchar(3),DATENAME(month,empdate)) from empdt  (or)
select Cast(DATENAME(month,empdate) as varchar(3)) from empdt

Sending email in ASP.NET using SMTP configuration from web.config

Configure your email settings in Web.Config with your account details.
<system.net>
<mailSettings>
<smtp>
<network host="smtp.gmail.com" port="25" userName="youremail@gmail.com" password="yourpassword" defaultCredentials="false"/>
</smtp>          
</mailSettings>
</system.net>    

Send email with above SMTP settings like this
protected void Button1_Click(object sender, EventArgs e)
    {
        MailMessage msg = new MailMessage("frmid@yourdomain.com", "tomailid@anydomain.com");
        SmtpClient mailClient = new SmtpClient();
        //Assign your Mail Body text
        msg.Body = "test mail body";
        //Assign Subject of the e-mail
        msg.Subject = "subject";       
        mailClient.EnableSsl = true;
 //if you use yahoo then change mailClient.EnableSsl = false;
       //Send mail using Web.config setting
        mailClient.Send(msg);
    }