Code Stack: 2015

Tuesday, September 1, 2015

Query to get data from table 2 if table 1 doesn't have data for some of the column's

SELECT CDAN8 BillTo,CDWMNC CreditCardName, CDWSV CreditCardNumber,CDMNTH CreditCardMonth, CDBLYR CreditCardYear,
(Case WHEN CDADD1 IS NULL OR CDADD1 = '' THEN ALADD1 ELSE CDADD1 END) CreditCardAddressLine1,(Case WHEN CDMSCY IS NULL OR CDMSCY =

'' THEN ALCTY1 ELSE CDMSCY END) CreditCardCity, (Case WHEN CDUSEX IS NULL OR CDUSEX = '' THEN ALADDS ELSE CDUSEX END)

CreditCardState,
(Case WHEN CDZIP IS NULL OR CDZIP = '' THEN ALADDZ ELSE CDZIP END) CreditCardZip,(Case WHEN CDDC IS NULL OR CDDC = '' THEN ALCTR

ELSE CDDC END) CreditCardCountry,
CDYN03 IsActive from table1 A LEFT JOIN table2 B ON A.CDAN8 = B.ALAN8
where cdan8='107189' and ((CDBLYR = 2015 and CDMNTH > 8) or CDBLYR >= 2016)
order by cdan8 desc

Tuesday, July 28, 2015

javascript to give text color code if the required value is less than the actual value

$(document).ready(function () {
        try {
            $("input[id^=txtNumAmountToPay]").each(function () {
                if ($(this).val() != "") {
                    var arrayRemarks = $(this).attr('id').split("txtNumAmountToPay");
                    var txtAmountToPayID = 'input[id="txtNumAmountToPay' + arrayRemarks[1] + '"]';
                    var txtOpenAmountID = 'input[id="hidNumAmountToPay' + arrayRemarks[1] + '"]';
                    var txtAmountToPay = parseFloat($(txtAmountToPayID).val().replace(",", ""));
                    var txtOpenAmount = parseFloat($(txtOpenAmountID).val().replace(",", ""));

                    if (txtAmountToPay < txtOpenAmount) {
                        $(this)[0].style.backgroundColor = "yellow";
                    }
                    else {
                        $(this)[0].style.backgroundColor = "white";
                    }
                }
            });

            $("input[id^=txtNumAmountToPay]").change(function () {
                if ($(this).val() != "") {
                    var arrayRemarks = $(this).attr('id').split("txtNumAmountToPay");
                    var txtAmountToPayID = 'input[id="txtNumAmountToPay' + arrayRemarks[1] + '"]';
                    var txtOpenAmountID = 'input[id="hidNumAmountToPay' + arrayRemarks[1] + '"]';
                    var txtAmountToPay = parseFloat($(txtAmountToPayID).val().replace(",", ""));
                    var txtOpenAmount = parseFloat($(txtOpenAmountID).val().replace(",", ""));

                    if (txtAmountToPay < txtOpenAmount) {
                        $(this)[0].style.backgroundColor = "yellow";
                    }
                    else {
                        $(this)[0].style.backgroundColor = "white";
                    }
                }
            });

"Like" on float datatype needs to have casting in sql server query

Below is the query :-

(Cast(Cast({0} as decimal) as NVARCHAR(250)) LIKE '%{1}%'),val1,val2)

here val 1 is the name of the column ,val 2 is the value of the column on which we need to perform like operation.

Send Email from gmail account code in asp.net c#

Asp.net aspx page:-

<%@ 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 id="Head1" runat="server">
    <title></title>
    <style type="text/css">
        body
        {
            font-family: Arial;
            font-size: 10pt;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
<table border="0" cellpadding="0" cellspacing="0">
    <tr>
        <td style="width: 80px">
            To:
        </td>
        <td>
            <asp:TextBox ID="txtTo" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
             
        </td>
    </tr>
    <tr>
        <td>
            Subject:
        </td>
        <td>
            <asp:TextBox ID="txtSubject" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
             
        </td>
    </tr>
    <tr>
        <td valign = "top">
            Body:
        </td>
        <td>
            <asp:TextBox ID="txtBody" runat="server" TextMode = "MultiLine" Height = "150" Width = "200"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
             
        </td>
    </tr>
    <tr>
        <td>
            File Attachment:
        </td>
        <td>
            <asp:FileUpload ID="fuAttachment" runat="server" />
        </td>
    </tr>
    <tr>
        <td>
             
        </td>
    </tr>
    <tr>
        <td>
            Gmail Email:
        </td>
        <td>
            <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
             
        </td>
    </tr>
    <tr>
        <td>
            Gmail Password:
        </td>
        <td>
            <asp:TextBox ID="txtPassword" runat="server" TextMode = "Password"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
             
        </td>
    </tr>
    <tr>
        <td>
        </td>
        <td>
            <asp:Button ID="Button1" Text="Send" OnClick="SendEmail" runat="server" />
        </td>
    </tr>
</table>
    </form>
</body>
</html>
Asp.net aspx.cs page:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Net;
using System.Net.Mail;
public partial class _Default : System.Web.UI.Page
{
    protected void SendEmail(object sender, EventArgs e)
    {
        string to = txtTo.Text;
        string from = txtEmail.Text;
        string subject = txtSubject.Text;
        string body = txtBody.Text;
        using (MailMessage mm = new MailMessage(txtEmail.Text, txtTo.Text))
        {
            mm.Subject = txtSubject.Text;
            mm.Body = txtBody.Text;
            if (fuAttachment.HasFile)
            {
                string FileName = Path.GetFileName(fuAttachment.PostedFile.FileName);
                mm.Attachments.Add(new Attachment(fuAttachment.PostedFile.InputStream, FileName));
            }
            mm.IsBodyHtml = false;
            SmtpClient smtp = new SmtpClient();
            smtp.Host = "smtp.gmail.com";
            smtp.EnableSsl = true;
            NetworkCredential NetworkCred = new NetworkCredential(txtEmail.Text, txtPassword.Text);
            smtp.UseDefaultCredentials = true;
            smtp.Credentials = NetworkCred;
            smtp.Port = 587;
            smtp.Send(mm);
            ClientScript.RegisterStartupScript(GetType(), "alert", "alert('Email sent.');", true);
        }
    }
}

Note :-Sometimes google setting require us to configure

use below link to configure: -

https://www.google.com/settings/security/lesssecureapps