Sunday, July 13, 2014

How to Insert data using after trigger... SubString


create database mydata121
 CREATE TABLE CONCATE
(
Empid varchar(50)
,Ename varchar(50)
,Salary money,
associatID numeric
)

insert into CONCATE(Empid,Ename,Salary)
values('101','Ashok',25000)
insert into CONCATE(Empid,Ename,Salary)
values('202','shaarth',60000)

select * from CONCATE

select SUBSTRING(empid,1,1)+'_'+SUBSTRING(ename,1,1)+'_'+
SUBSTRING(cast(Salary as varchar(50)),1,2) as Associate,
Empid,Ename,Salary from CONCATE



CREATE TRIGGER AFTERINSERT ON CONCATE AFTER INSERT
AS
BEGIN
DECLARE @EMPID VARCHAR(50)
DECLARE @ENAME VARCHAR(50)
DECLARE @SALARY VARCHAR(50)


SELECT @EMPID= i.empid FROM inserted i
select @ENAME=i.ename from inserted i
select @SALARY=i.Salary from inserted i


DECLARE @SUBSTRING VARCHAR(50)=SUBSTRING(@EMPID,1,1)+'_'+SUBSTRING(@ENAME,1,1)+'_'+
SUBSTRING(cast(@SALARY as varchar(50)),1,2)


update concate set associatID=@SUBSTRING where empid=@EMPID


END

GO

Sending Mail

.aspx

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

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    TO: <asp:TextBox ID="txtto" runat="server"></asp:TextBox>
        <br />
        Sub: <asp:TextBox ID="txtsub" runat="server"></asp:TextBox>
        <br />
        Body:<asp:TextBox ID="txtbody" runat="server" TextMode="MultiLine"></asp:TextBox>
        <br />
    <asp:Button ID="btnmail" runat="server" Text="Send" onclick="btnmail_Click" />
    </div>
    </form>
</body>
</html>

-------------------------------------------------
.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Mail;
using System.Net.Mail;

public partial class Default2 : System.Web.UI.Page
{

    string gmailusername = System.Configuration.ConfigurationManager.AppSettings["Gmailusername"].ToString();
    string pwd = System.Configuration.ConfigurationManager.AppSettings["Password"].ToString();
    string port = System.Configuration.ConfigurationManager.AppSettings["Port"].ToString();
    string protocval = System.Configuration.ConfigurationManager.AppSettings["protocal"].ToString();
    protected void Page_Load(object sender, EventArgs e)
    {

    }

   
    protected void btnmail_Click(object sender, EventArgs e)
    {
        System.Net.Mail.MailMessage newmsg = new System.Net.Mail.MailMessage();

        newmsg.From = new MailAddress(gmailusername);

            newmsg.Subject = txtsub.Text;
            newmsg.To.Add(txtto.Text);
            newmsg.Body = txtbody.Text;
            SmtpClient smtp = new SmtpClient();
            smtp.Host = protocval;
            smtp.Port = Convert.ToInt32(port);
            smtp.Credentials = new System.Net.NetworkCredential(gmailusername,pwd);

            smtp.EnableSsl = true;
            smtp.Send(newmsg);
            Response.Write("msg is send");

      
    }
}
===============================================================
Web.config

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <connectionStrings>

    <add name="myconnreader" connectionString="Data Source=KCLINK-PC\SQLEXPRESS;Initial Catalog=mydataworld;Integrated Security=True"/>
  </connectionStrings>

  <appSettings>
    <add key="Gmailusername" value="x@gmail.com"/>
    <add key="Password" value="xx"/>
    <add key="Port" value="587"/>
    <add key="protocal" value="smtp.gmail.com"/>
   
  </appSettings>
     <system.web>
           <compilation debug="true" targetFramework="4.0"/>
     </system.web>
</configuration>
================================================================
Note: Where X is Ur USERNAME
Where XX is Ur PASSWORD