Select Statement in SQL

Basic SQL Syntax

SELECT Statement:

        Select Statement used to retrieve the data from database (table)

and the results are displays in  table format, called Result-set

Syntax for retrieving all data from table:

        SELECT * FROM table_name;

Syntax for retrieving Selected Column data from table:

        SELECT Columnname(s) FROM table_name;

Examples:

The Table “Contact” has following data,

ID	NAME	DESIGNATION	CITY	MOBILE
1	venkat	CEO	Kodambakkam	999989845
2	Santhosh	manager	Puddukottai	9999898978
3	Kousilaya	Asst.manager	Trichy	9659315754
4	Ananth	CEO	Chennai	9999999945
5	Sajil	MD	Surandai	6677888997

We use following statement,

SELECT * FROM Contact;

The Result-set is,

ID	NAME	DESIGNATION	CITY	MOBILE
1	venkat	CEO	Kodambakkam	999989845
2	Santhosh	manager	Puddukottai	9999898978
3	Kousilaya	Asst.manager	Trichy	9659315754
4	Ananth	CEO	Chennai	9999999945
5	Sajil	MD	Surandai	6677888997

We use following statement,

SELECT Name,Designation,Mobile FROM Contact;

The Result-set is,

NAME	DESIGNATION	MOBILE
venkat	CEO	999989845
Santhosh	manager	9999898978
Kousilaya	Asst.manager	9659315754
Ananth	CEO	9999999945
Sajil	MD	6677888997

Note that, SQL is not Case-Sensitive.

Javascript to Validate Past and Future Date

<script type="text/javascript" language="javascript">

        function CheckForPastDate(sender, args) {
            var selectedDate = new Date();
            selectedDate = sender._selectedDate;
            var todayDate = new Date();
            if (selectedDate.getDateOnly() < todayDate.getDateOnly()) {
                sender._selectedDate = todayDate; sender._textbox.set_Value(sender._selectedDate.format(sender._format));
                alert("Date Cannotbe in the past");
            }
        }


        function CheckForFutureDate(sender, args) {
            var selectedDate = new Date();
            selectedDate = sender._selectedDate;
            var todayDate = new Date();
            if (selectedDate.getDateOnly() > todayDate.getDateOnly()) {
                sender._selectedDate = todayDate; sender._textbox.set_Value(sender._selectedDate.format(sender._format));
                alert("Date Cannot be in the Future");
            }
        }     
      
      
    </script>

Session Variable in asp.net

ASP.NET session state enables you to store and retrieve values for a user as the user navigates ASP.NET pages in a Web application. HTTP is a stateless protocol. This means that a Web server treats each HTTP request for a page as an independent request. The server retains no knowledge of variable values that were used during previous requests. ASP.NET session state identifies requests from the same browser during a limited time window as a session, and provides a way to persist variable values for the duration of that session. By default, ASP.NET session state is enabled for all ASP.NET applications.

Example:

Session["FirNam"] = FirstNameTextBox.Text;
Session["LasNam"] = LastNameTextBox.Text;

we can retrieve this values in any page as

 string Firstname=Session["FirNam"].Tostring();

 string Lastname=Session["LasNam"].Tostring();

Send Mail From ASP.net and C#

using System.Net.Mail;

 protected void Btn_SendMail_Click(object sender, EventArgs e)
    {
        try
        {
         
           MailMessage mailObj = new MailMessage(
                        txtFrom.Text, txtTo.Text, txtSubject.Text, txtBody.Text);
            SmtpClient SMTPServer = new SmtpClient(ser);
            SMTPServer.Credentials = new System.Net.NetworkCredential(usnam, pass);
            SMTPServer.Send(mailObj);
            Label1.Text = "Mail Sent successfully";
        }
        catch (Exception)
        {
            Label1.Text = "Check Your InternetConnection or email Configuration";
        }
    }
}
ser -> Smtp server Name.

You Can Also Try This Code,Using Attachment in Mail


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Net.Mail;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data;
using System.Net;

public partial class sendmail : System.Web.UI.Page
{
    MySqlConnection con = new MySqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());
    MySqlCommand cmd = new MySqlCommand();
    MySqlDataReader rd;
    string ser = "", usnam = "", pass = "", Client = "";
    MailMessage newmsg;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack == true)
        {


        }
        con.Open();
        cmd = new MySqlCommand("select Server,Username,Password,fromadd,toadd,Client_id from email_confi", con);
        rd = cmd.ExecuteReader();
        while (rd.Read())
        {
            ser = rd[0].ToString();
            usnam = rd[1].ToString();
            pass = rd[2].ToString();
            txtFrom.Text = rd[3].ToString();
            txtTo.Text = rd[4].ToString();
            Client = rd[5].ToString();
        }
        rd.Close();
        con.Close();


    }
    protected void Btn_SendMail_Click(object sender, EventArgs e)
    {
        try
        {
            MailAddress mailfrom = new MailAddress(txtFrom.Text);
            MailAddress mailto = new MailAddress(txtTo.Text);
            newmsg = new MailMessage(mailfrom, mailto);

              newmsg.Body = txtSubject.Text;
               string path = Server.MapPath("~/Excel/");
               FileUpload1.SaveAs(path + FileUpload1.FileName);
            Attachment att = new Attachment(path + FileUpload1.FileName);
       
            newmsg.Attachments.Add(att);
            SmtpClient smtp = new SmtpClient(ser, 587);
            smtp.UseDefaultCredentials = false;
            smtp.Credentials = new NetworkCredential(usnam, pass);
            smtp.EnableSsl = true;
            smtp.Send(newmsg);
            lblStatus.Text = "Sent email (" + txtSubject.Text + ") to " + txtTo.Text;

        }
        catch (Exception EX)
        {
            lblStatus.Text = "Check Your InternetConnection or email Configuration" + EX;
        }
    }
}
 

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

<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="ajax" %> 

<!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>

<link rel="Stylesheet" href="css/tabcss.css" />

    <title></title>

</head>

<body bgcolor="#ffff99">

    <form id="form1" runat="server">

      <ajax:ToolkitScriptManager ID="sc" runat="server" ></ajax:ToolkitScriptManager>    

  <center>

<ajax:RoundedCornersExtender runat="server" ID="RCE" TargetControlID="Panel2" 

             Radius="15" Enabled="True" ></ajax:RoundedCornersExtender>

             <asp:Panel runat="server" ID="Panel2"  BackColor="Brown"

            Width="750px" >

            <asp:Label ID="Label6" runat="server" Text="Send Mail" ForeColor="White" 

                     Font-Bold="True" Font-Size="Larger" ></asp:Label>

            </asp:Panel>

            </center>         

   <%-- <div style="font-family: Verdana; font-size: small; font-weight: bold; font-style: normal; font-variant: normal; text-transform: none; color: #000000;  text-align: left; text-indent: inherit; white-space: normal; word-spacing: normal; letter-spacing: normal; width: 910px;background-color: #FFFFFF">

        <img src="images/send_mail.PNG" width="100%" height="30px" />

        </div>--%>

        <br />

        <div>

        <center>

   <ajax:RoundedCornersExtender runat="server" ID="RoundedCornersExtender1" TargetControlID="Panel3" 

             Radius="15" Enabled="True" ></ajax:RoundedCornersExtender>

             <asp:Panel runat="server" ID="Panel3"  BackColor="Brown"

            Width="750px" >

          

          <center>

    <table>

    <tr>

    <td  align="left">

       <asp:Label ID="Label2" runat="server" Text="MessageTo" CssClass="label"></asp:Label>

    </td>

    <td  align="left">

    <asp:textbox id="txtTo" runat="server" ReadOnly="True" Width="216px" 

            BackColor="#FFFFCC"></asp:textbox>

    </td>

    </tr>

     <tr>

    <td  align="left">

       <asp:Label ID="Label3" runat="server" Text=" Message from" CssClass="label"></asp:Label>

    </td>

    <td  align="left">

    <asp:TextBox runat="server" ID="txtFrom" ReadOnly="True" 

            Width="215px" BackColor="#FFFFCC"></asp:TextBox>

    </td>

    </tr>

     <tr>

    <td  align="left">

       <asp:Label ID="Label4" runat="server" Text="Subject" CssClass="label"></asp:Label>

    </td>

    <td  align="left">

     <asp:TextBox runat="server" ID="txtSubject" Width="366px"></asp:TextBox>

    </td>

    </tr>

     <tr>

    <td  align="left">

        <asp:FileUpload ID="FileUpload1" runat="server" /> 

      </td>

    <td align="left">

        <asp:Label ID="Label7" runat="server" Text="Label"></asp:Label>

        </td>

    </tr>

    </table>

    <asp:Button ID="Btn_SendMail" runat="server" onclick="Btn_SendMail_Click"

            Text="Send Email" />

        <br />

        <br />

        <asp:Label ID="lblStatus" runat="server" CssClass="label" ForeColor="#ccffcc"></asp:Label>

        </center> 

            </asp:Panel>

   </center>   

   <br />       

    </div>

    

    </form>

</html>

</body>

Datatable Values in Excel Report

protected void btnreport_Click(object sender, EventArgs e)
    {
        try
        {
            bind();
            DataView dv = new DataView(dtt);
         
            System.Text.StringBuilder builder = new System.Text.StringBuilder();
            System.Data.DataTable dt = dv.Table;
            string headerToExport = string.Empty;
            foreach (DataColumn col in dt.Columns)              
               
                headerToExport += (char)34 + col.ColumnName + (char)34 + (char)44;
            headerToExport.Remove(headerToExport.Length - 1, 1);
            headerToExport = headerToExport + Environment.NewLine + Environment.NewLine;
            builder.Append(headerToExport);
            string body = string.Empty;
            foreach (DataRow row in dt.Rows)
            {
                foreach (object obj in row.ItemArray)
                    body += (char)34 + obj.ToString() + (char)34 + (char)44;
                body.Remove(body.Length - 1, 1);
                body = body + Environment.NewLine;
            }
            builder.Append(body);
            builder.Append(Environment.NewLine);
            builder.Append(Environment.NewLine);
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment;filename=Report.csv");
            Response.Charset = "";
            Response.Write(builder.ToString());
            Response.Flush();
            Response.End();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally { }
    }
}

You Can Also Use This Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using System.Data;

public partial class TransferedFundExcel : System.Web.UI.Page
{
 
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

            Bind();
        }
    }
    protected void BtnGenerateExcel_Click(object sender, EventArgs e)
    {
        try
        {
            Bind();
            ExportToExcel((DataTable)ViewState["Datatable"]);
         
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally {
            ViewState["Datatable"] = null;
        }
    }
    public void ExportToExcel(DataTable dt)
    {
        if (dt.Rows.Count > 0)
        {
            string filename = "Test.xls";
            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            DataGrid dgGrid = new DataGrid();
            dgGrid.DataSource = dt;
            dgGrid.DataBind();

            //Get the HTML for the control.
            dgGrid.RenderControl(hw);
            //Write the HTML back to the browser.
            Response.ContentType = "application/vnd.ms-excel";
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
            this.EnableViewState = false;
            Response.Write(tw.ToString());
            Response.End();
        }
    }

    public void Bind()
    {
        using (MySqlConnection ObjCon = new MySqlConnection(new CommonClass().GetConnectionString()))
        {
            try
            {
                DataSet ds = new DataSet();
                MySqlDataAdapter ada = new MySqlDataAdapter("Select * from CustomerOrder", ObjCon);
                ada.Fill(ds);
                DataTable dt = new DataTable();
                dt.Columns.Add("Cus_orderId");
                dt.Columns.Add("Fund_TransId");
                dt.Columns.Add("BeneName");
                dt.Columns.Add("BeneAcc");
                dt.Columns.Add("Bank");
                dt.Columns.Add("Branch");
                dt.Columns.Add("Amount");
             
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DataRow dr = dt.NewRow();
                    dt.Rows.Add(dr);
                    DataTable dt1 = new DataTable();
                    MySqlDataAdapter ad1 = new MySqlDataAdapter("SELECT Name,AccountNo,BankName,BranchName FROM Beneficiary Where Id='"+ds.Tables[0].Rows[0][3].ToString()+"'", ObjCon);
                    ad1.Fill(dt1);
                    dr[0] = ds.Tables[0].Rows[i][0].ToString();
                    dr[1] = ds.Tables[0].Rows[i][1].ToString();
                    dr[2] = dt1.Rows[0][0].ToString();
                    dr[3] = dt1.Rows[0][1].ToString();
                    dr[4] = dt1.Rows[0][2].ToString();
                    dr[5] = dt1.Rows[0][3].ToString();
                    dr[6] = ds.Tables[0].Rows[i][6].ToString();
                }
                GridView1.DataSource = dt;
                ViewState["Datatable"] = dt;
                GridView1.DataBind();
            }

            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
        }

    }
}

Disable Browser BackButton with Javasccript Function

<script type="text/javascript">

    function BackButton() {

        window.history.forward();

    }

    setTimeout("BackButton()", 0);

</script>   

<body  bgcolor="#ffff99" onload="BackButton()">

Different Types Of Dropdown in windows Application

Go To property Or press F4 to Change Property of Drop Down into Simple ,Dropdownlist,Dropdown.

Collapse All Node Except Selected Node in Asp.net Tree View

     protected void TreeView1_SelectedNodeChanged(object sender, EventArgs e)

    {

        foreach (TreeNode tn in TreeView1.Nodes)

        {

            tn.Collapse();

        }

        TreeView1.SelectedNode.Expand();

    

    }

Number Into Text (Or) Rupees in Words in C#

 public string NumberToText(int number)
        {




            if (number == 0)
            {
                label1.Text = "Zero";
            }
            else if (number == -2147483648)
            {
                label1.Text = "Minus Two Hundred and Fourteen Crore Seventy Four Lakh Eighty Three                                                 Thousand Six Hundred and Forty Eight";
            }
            else
            {
                int[] num = new int[4];


                int first = 0;


                int u, h, t;
                    if (number < 0)
                {
                    sb.Append("Minus ");
                    number = -number;
                }
                string[] words0 = {"" ,"One ", "Two ", "Three ", "Four ",
      "Five " ,"Six ", "Seven ", "Eight ", "Nine "};
                string[] words1 = {"Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ",
      "Fifteen ","Sixteen ","Seventeen ","Eighteen ", "Nineteen "};
                string[] words2 = {"Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ",
      "Seventy ","Eighty ", "Ninety "};
                string[] words3 = { "Thousand ", "Lakh ", "Crore " };
                num[0] = number % 1000; // units
                num[1] = number / 1000;
                num[2] = number / 100000;
                num[1] = num[1] - 100 * num[2]; // thousands
                num[3] = number / 10000000; // crores
                num[2] = num[2] - 100 * num[3]; // lakhs
                for (int i = 3; i > 0; i--)
                {
                    if (num[i] != 0)
                    {
                        first = i;
                        break;
                    }
                }
                for (int i = first; i >= 0; i--)
                {
                    if (num[i] == 0) continue;
                    u = num[i] % 10; // ones
                    t = num[i] / 10;
                    h = num[i] / 100; // hundreds
                    t = t - 10 * h; // tens
                    if (h > 0) sb.Append(words0[h] + "Hundred ");
                    if (u > 0|| t > 0)
                    {
                        if (h > 0 || i == 0) sb.Append("and ");
                        if (t == 0)
                            sb.Append(words0[u]);
                        else if (t == 1)
                            sb.Append(words1[u]);
                        else
                            sb.Append(words2[t - 2] + words0[u]);
                    }
                    if (i != 0) sb.Append(words3[i - 1]);
               
                }
            }
            return sb.ToString().TrimEnd();
        }


Result Looks Like This:


TOTAL AMOUNT: 10.00
Rupees In Words: and Ten Only.
TOTAL AMOUNT: 145.00
Rupees In Words:One Hundred and Forty Five Only.
TOTAL AMOUNT: 2,400.00
Rupees In Words:Two Thousand and Four Hundred Only.
TOTAL AMOUNT: 19,476.00
Rupees In Words:Nineteen Thousand Four Hundred and Seventy Six Only.
TOTAL AMOUNT: 10,00,180.00
Rupees In Words:Ten Lakh One Hundred and Eighty Only.