Wednesday, May 16, 2012

Update a table on Updating data in other table in SqlServer(After Update trigger)

Update a table on Updating data in other table in SqlServer(After Update trigger)

Here if we update data in [TBL_PROSPECTDETAILS] table ..using this after update trigger we can update the data in TBL_ACORD_MISC.

After Update Trigger :

Create TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[TBL_PROSPECTDETAILS]
after update
AS

 Update TBL_ACORD_MISC set Company=i.NameofBusiness,ApplicantName=i.OwnerContact,CompOffPhone=i.Phone,
                              CompMobPhone=i.PayrollHRContact,CompMailingAddress=i.Address,CompCity=i.City,
                              CompState=i.State,CompZip=i.Zipcode,CompYRSinBus=i.YearofBusiness,CompSIC=i.SIC,
                              CompNAICS=i.PCompany,CompWebsite=i.URL,ComEmail=i.Email,CompCorporationType=i.CorpType,
                              CompFederalIDNum=i.FedTaxId from TBL_ACORD_MISC j join TBL_PROSPECTDETAILS i on j.PID=i.PId      
   
   
   
   
Go

Monday, May 14, 2012

3-tier Architecture Sample Code:

DataAccess  Layer :

DAL/Datalayer.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

///


/// Summary description for dataaccesslayer
///

namespace datalayer.DAL
{

    ///


    /// Summary description for parameter
    ///

    public class DBConnection
    {
        private IDbCommand cmd = new SqlCommand();
        private string strConnectionString = "";
        private bool handleErrors = false;
        private string strLastError = "";

        public DBConnection()
        {
            strConnectionString = System.Configuration.ConfigurationManager.AppSettings["dbstring"].ToString();
            SqlConnection cnn = new SqlConnection();
            cnn.ConnectionString = strConnectionString;
            cmd.Connection = cnn;
            cmd.CommandType = CommandType.StoredProcedure;
        }


        public int ExecuteNonQuery()
        {
            int i = -1;
            try
            {
                this.Open();
                i = cmd.ExecuteNonQuery();
                this.Close();
            }
            catch (Exception ex)
            {
                if (handleErrors)
                    strLastError = ex.Message;
                else
                    throw;
            }
            catch
            {
                throw;
            }

            return i;
        }


        public int ExecuteNonQuery(string commandtext)
        {
            int i = -1;
            try
            {
                cmd.CommandText = commandtext;
                i = this.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                if (handleErrors)
                    strLastError = ex.Message;
                else
                throw;
            }
            catch
            {
                throw;
            }

            return i;
        }


        public DataSet ExecuteDataSet()
        {
            SqlDataAdapter da = null;
            DataSet ds = null;
            try
            {
                da = new SqlDataAdapter();
                da.SelectCommand = (SqlCommand)cmd;
                ds = new DataSet();
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                if (handleErrors)
                    strLastError = ex.Message;
                else
                    throw;
            }
            catch
            {
                throw;
            }

            return ds;
        }


        public DataSet ExecuteDataSet(string commandtext)
        {
            DataSet ds = null;
            try
            {
                cmd.CommandText = commandtext;
                ds = this.ExecuteDataSet();
            }
            catch (Exception ex)
            {
                if (handleErrors)
                    strLastError = ex.Message;
                else
                    throw;
            }
            catch
            {
                throw;
            }

            return ds;
        }


        public string CommandText
        {
            get
            {
                return cmd.CommandText;
            }
            set
            {
                cmd.CommandText = value;
                cmd.Parameters.Clear();
            }
        }

        public IDataParameterCollection Parameters
        {
            get
            {
                return cmd.Parameters;
            }
        }

        public void AddParameter(string paramname, object paramvalue)
        {
            SqlParameter param = new SqlParameter(paramname, paramvalue);
            cmd.Parameters.Add(param);
        }

        public void AddParameter(IDataParameter param)
        {
            cmd.Parameters.Add(param);
        }


        public string ConnectionString
        {
            get
            {
                return strConnectionString;
            }
            set
            {
                strConnectionString = value;
            }
        }

        private void Open()
        {
            cmd.Connection.Open();
        }

        private void Close()
        {
            cmd.Connection.Close();
        }

        public bool HandleExceptions
        {
            get
            {
                return handleErrors;
            }
            set
            {
                handleErrors = value;
            }
        }

        public string LastError
        {
            get
            {
                return strLastError;
            }
        }

        public void Dispose()
        {
            cmd.Dispose();
        }
    }
}

Businees Layer :

BOL/WC_Agency.cs


using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using datalayer;
using datalayer.DAL;
///


/// Summary AFName for Company.cs
/// ************************************************************
/// *    Project Name       :       *  
/// *    Created on         :   Monday, Jan 30,2012         *      
/// *    Page Name          :   WC_Agency.cs                    *
/// *    Parent Page        :   Status Coverded pages          *      
/// *    Procedure name     :   sp_TBL_WC_Agency_MASTER               *  
/// *    Front end          :   Visual Studio 2010             *
/// *    Back end           :   Sql server 2008                *  
/// ************************************************************
///

namespace WC_Agency.BOL
{
    public class WC_Agency_frm
    {
        private int _Action;

        public int Action
        {
            get { return _Action; }
            set { _Action = value; }
        }

        private int _Active;

        public int Active
        {
            get { return _Active; }
            set { _Active = value; }
        }

        private int _PID;

        public int PID
        {
            get { return _PID; }
            set { _PID = value; }
        }

        private int _Wid;

        public int Wid
        {
            get { return _Wid; }
            set { _Wid = value; }
        }

        private string _AgencyName;

        public string AgencyName
        {
            get { return _AgencyName; }
            set { _AgencyName = value; }
        }

       
        private string _Address;

        public string Address
        {
            get { return _Address; }
            set { _Address = value; }
        }

        private string _City;

        public string City
        {
            get { return _City; }
            set { _City = value; }
        }

       

        private string _State;

        public string State
        {
            get { return _State; }
            set { _State = value; }
        }

        private string _Zip;

        public string Zip
        {
            get { return _Zip; }
            set { _Zip = value; }
        }

        private string _ProducerName;

        public string ProducerName
        {
            get { return _ProducerName; }
            set { _ProducerName = value; }
        }

        private string _CSPName;

        public string CSPName
        {
            get { return _CSPName; }
            set { _CSPName = value; }
        }

        private string _OfficePhone;

        public string OfficePhone
        {
            get { return _OfficePhone; }
            set { _OfficePhone = value; }
        }

        private string _MobilePhone;

        public string MobilePhone
        {
            get { return _MobilePhone; }
            set { _MobilePhone = value; }
        }

        private string _Fax;

        public string Fax
        {
            get { return _Fax; }
            set { _Fax = value; }
        }

        private string _Email;

        public string Email
        {
            get { return _Email; }
            set { _Email = value; }
        }

        private string _Code;

        public string Code
        {
            get { return _Code; }
            set { _Code = value; }
        }

        private string _SubCode;

        public string SubCode
        {
            get { return _SubCode; }
            set { _SubCode = value; }
        }

        private string _AgencyCustomerID;

        public string AgencyCustomerID
        {
            get { return _AgencyCustomerID; }
            set { _AgencyCustomerID = value; }
        }



        public static int Add_Update_delete_WC_Agency(WC_Agency_frm af)
        {
            DBConnection dc = new DBConnection();
            dc.Parameters.Add(new SqlParameter("@Action", af.Action));
            dc.Parameters.Add(new SqlParameter("@Wid", af.Wid));
            dc.Parameters.Add(new SqlParameter("@pid", af.PID));
            dc.Parameters.Add(new SqlParameter("@AgencyName", af.AgencyName));
            dc.Parameters.Add(new SqlParameter("@Address", af.Address));
            dc.Parameters.Add(new SqlParameter("@City", af.City));
            dc.Parameters.Add(new SqlParameter("@Zip", af.Zip));
            dc.Parameters.Add(new SqlParameter("@State", af.State));
            dc.Parameters.Add(new SqlParameter("@ProducerName", af.ProducerName));
            dc.Parameters.Add(new SqlParameter("@CSPName", af.CSPName));
            dc.Parameters.Add(new SqlParameter("@OfficePhone", af.OfficePhone));
            dc.Parameters.Add(new SqlParameter("@MobilePhone", af.MobilePhone));
            dc.Parameters.Add(new SqlParameter("@Fax", af.Fax));
            dc.Parameters.Add(new SqlParameter("@Email", af.Email));
            dc.Parameters.Add(new SqlParameter("@Code", af.Code));
            dc.Parameters.Add(new SqlParameter("@SubCode", af.SubCode));
            dc.Parameters.Add(new SqlParameter("@AgencyCustomerID", af.AgencyCustomerID));
            dc.Parameters.Add(new SqlParameter("@Active", af.Active));

            return dc.ExecuteNonQuery("SP_TBL_WC_AGENCY");
        }
        public DataSet Select_WC_Agency(WC_Agency_frm af)
        {
            DBConnection dc = new DBConnection();
            DataSet ds = new DataSet();
            dc.Parameters.Add(new SqlParameter("@Action", af.Action));
            dc.Parameters.Add(new SqlParameter("@Wid", af.Wid));
            dc.Parameters.Add(new SqlParameter("@pid", af.PID));
            dc.Parameters.Add(new SqlParameter("@AgencyName", af.AgencyName));
            dc.Parameters.Add(new SqlParameter("@Address", af.Address));
            dc.Parameters.Add(new SqlParameter("@City", af.City));
            dc.Parameters.Add(new SqlParameter("@Zip", af.Zip));
            dc.Parameters.Add(new SqlParameter("@State", af.State));
            dc.Parameters.Add(new SqlParameter("@ProducerName", af.ProducerName));
            dc.Parameters.Add(new SqlParameter("@CSPName", af.CSPName));
            dc.Parameters.Add(new SqlParameter("@OfficePhone", af.OfficePhone));
            dc.Parameters.Add(new SqlParameter("@MobilePhone", af.MobilePhone));
            dc.Parameters.Add(new SqlParameter("@Fax", af.Fax));
            dc.Parameters.Add(new SqlParameter("@Email", af.Email));
            dc.Parameters.Add(new SqlParameter("@Code", af.Code));
            dc.Parameters.Add(new SqlParameter("@SubCode", af.SubCode));
            dc.Parameters.Add(new SqlParameter("@AgencyCustomerID", af.AgencyCustomerID));
            dc.Parameters.Add(new SqlParameter("@Active", af.Active));

            return ds = dc.ExecuteDataSet("SP_TBL_WC_AGENCY");
        }

        public static int Add_Update_delete_WC_Agency_MASTER(WC_Agency_frm af)
        {
            DBConnection dc = new DBConnection();
            dc.Parameters.Add(new SqlParameter("@Action", af.Action));
            dc.Parameters.Add(new SqlParameter("@Wid", af.Wid));
            dc.Parameters.Add(new SqlParameter("@AgencyName", af.AgencyName));
            dc.Parameters.Add(new SqlParameter("@Address", af.Address));
            dc.Parameters.Add(new SqlParameter("@City", af.City));
            dc.Parameters.Add(new SqlParameter("@Zip", af.Zip));
            dc.Parameters.Add(new SqlParameter("@State", af.State));
            dc.Parameters.Add(new SqlParameter("@ProducerName", af.ProducerName));
            dc.Parameters.Add(new SqlParameter("@CSPName", af.CSPName));
            dc.Parameters.Add(new SqlParameter("@OfficePhone", af.OfficePhone));
            dc.Parameters.Add(new SqlParameter("@MobilePhone", af.MobilePhone));
            dc.Parameters.Add(new SqlParameter("@Fax", af.Fax));
            dc.Parameters.Add(new SqlParameter("@Email", af.Email));
            dc.Parameters.Add(new SqlParameter("@Code", af.Code));
            dc.Parameters.Add(new SqlParameter("@SubCode", af.SubCode));
            dc.Parameters.Add(new SqlParameter("@AgencyCustomerID", af.AgencyCustomerID));
            dc.Parameters.Add(new SqlParameter("@Active", af.Active));

            return dc.ExecuteNonQuery("SP_TBL_WC_AGENCY_MASTER");
        }
        public DataSet Select_WC_Agency_MASTER(WC_Agency_frm af)
        {
            DBConnection dc = new DBConnection();
            DataSet ds = new DataSet();
            dc.Parameters.Add(new SqlParameter("@Action", af.Action));
            dc.Parameters.Add(new SqlParameter("@Wid", af.Wid));
            dc.Parameters.Add(new SqlParameter("@AgencyName", af.AgencyName));
            dc.Parameters.Add(new SqlParameter("@Address", af.Address));
            dc.Parameters.Add(new SqlParameter("@City", af.City));
            dc.Parameters.Add(new SqlParameter("@Zip", af.Zip));
            dc.Parameters.Add(new SqlParameter("@State", af.State));
            dc.Parameters.Add(new SqlParameter("@ProducerName", af.ProducerName));
            dc.Parameters.Add(new SqlParameter("@CSPName", af.CSPName));
            dc.Parameters.Add(new SqlParameter("@OfficePhone", af.OfficePhone));
            dc.Parameters.Add(new SqlParameter("@MobilePhone", af.MobilePhone));
            dc.Parameters.Add(new SqlParameter("@Fax", af.Fax));
            dc.Parameters.Add(new SqlParameter("@Email", af.Email));
            dc.Parameters.Add(new SqlParameter("@Code", af.Code));
            dc.Parameters.Add(new SqlParameter("@SubCode", af.SubCode));
            dc.Parameters.Add(new SqlParameter("@AgencyCustomerID", af.AgencyCustomerID));
            dc.Parameters.Add(new SqlParameter("@Active", af.Active));

            return ds = dc.ExecuteDataSet("SP_TBL_WC_AGENCY_MASTER");
        }


    }

}

Presentation Layer :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using Common.BOL;
using System.Data;
using User_Details.BOL;
using datalayer.DAL;
using User_Roles.BOL;
using Authentication.BOL;
using System.Configuration;
using WCRates.BOL;
using States.BOL;
using Carrier.BOL;
using WC_Agency.BOL;

public partial class Controls_AddWCAgency : System.Web.UI.UserControl
{
    WC_Agency_frm wcf = new WC_Agency_frm();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.QueryString["Rateid"] != null)
        {
            int Rateid = int.Parse(Request.QueryString["Rateid"].ToString());
            if (!IsPostBack)
            {
                Get_WCAgencyDetails(Rateid);
            }
        }
    }
    protected void btnWCRatesAdd_Click(object sender, EventArgs e)
    {
        AddWCAgency();
    }
    protected void btnWCRatesUpdate_Click(object sender, EventArgs e)
    {
        UpdateWCAgency();
    }

    public void Get_WCAgencyDetails(int Rateid)
    {
        btnWCRatesAdd.Visible = false;
        btnWCRatesUpdate.Visible = true;
        int Active = 1;

        DataSet ds_typrCorp = new DataSet();

        wcf.Wid = Rateid;
        wcf.Action = 5;
        wcf.Active = Convert.ToInt32(Active);
        ds_typrCorp = wcf.Select_WC_Agency_MASTER(wcf);

        if (ds_typrCorp.Tables[0].Rows.Count > 0)
        {
            txtAgencyName.Text = ds_typrCorp.Tables[0].Rows[0]["AgencyName"].ToString();
            txtAddress.Text = ds_typrCorp.Tables[0].Rows[0]["Address"].ToString();
            txtCity.Text = ds_typrCorp.Tables[0].Rows[0]["City"].ToString();
            txtState.Text = ds_typrCorp.Tables[0].Rows[0]["State"].ToString();
            txtZip.Text = ds_typrCorp.Tables[0].Rows[0]["Zip"].ToString();
            txtProducerName.Text = ds_typrCorp.Tables[0].Rows[0]["ProducerName"].ToString();
            txtCSPName.Text = ds_typrCorp.Tables[0].Rows[0]["CSPName"].ToString();
            txtOffPhone.Text = ds_typrCorp.Tables[0].Rows[0]["OfficePhone"].ToString();
            txtMobPhone.Text = ds_typrCorp.Tables[0].Rows[0]["MobilePhone"].ToString();
            txtFax.Text = ds_typrCorp.Tables[0].Rows[0]["Fax"].ToString();
            txtEmail.Text = ds_typrCorp.Tables[0].Rows[0]["Email"].ToString();
            txtCode.Text = ds_typrCorp.Tables[0].Rows[0]["Code"].ToString();
            txtSubCode.Text = ds_typrCorp.Tables[0].Rows[0]["SubCode"].ToString();
            txtAgeCustID.Text = ds_typrCorp.Tables[0].Rows[0]["AgencyCustomerID"].ToString();

            txtAgencyName.Focus();
        }

    }

    public void AddWCAgency()
    {
        string AgencyName = txtAgencyName.Text.Trim().ToString();
        string Address = txtAddress.Text.Trim().ToString();
        string City = txtCity.Text.Trim().ToString();
        string State = txtState.Text.Trim().ToString();
        string Zip = txtZip.Text.Trim().ToString();
        string ProducerName = txtProducerName.Text.Trim().ToString();
        string CSPName = txtCSPName.Text.Trim().ToString();
        string OffPhone = txtOffPhone.Text.Trim().ToString();
        string MobPhone = txtMobPhone.Text.Trim().ToString();
        string Fax = txtFax.Text.Trim().ToString();
        string Email = txtEmail.Text.Trim().ToString();
        string Code = txtCode.Text.Trim().ToString();
        string SubCode = txtSubCode.Text.Trim().ToString();
        string AgeCustID = txtAgeCustID.Text.Trim().ToString();

        wcf.Action = 1;
        wcf.Active = 1;
        wcf.AgencyName = AgencyName;
        wcf.Address = Address;
        wcf.City = City;
        wcf.State = State;
        wcf.Zip = Zip;
        wcf.ProducerName = ProducerName;
        wcf.CSPName = CSPName;
        wcf.OfficePhone = OffPhone;
        wcf.MobilePhone = MobPhone;
        wcf.Fax = Fax;
        wcf.Email = Email;
        wcf.Code = Code;
        wcf.SubCode = SubCode;
        wcf.AgencyCustomerID = AgeCustID;

        WC_Agency_frm.Add_Update_delete_WC_Agency_MASTER(wcf);

        lblStatus.Visible = true;
        lblStatus.Text = "Added Successfully";

        ClearControls();



    }

    public void UpdateWCAgency()
    {

        string AgencyName = txtAgencyName.Text.Trim().ToString();
        string Address = txtAddress.Text.Trim().ToString();
        string City = txtCity.Text.Trim().ToString();
        string State = txtState.Text.Trim().ToString();
        string Zip = txtZip.Text.Trim().ToString();
        string ProducerName = txtProducerName.Text.Trim().ToString();
        string CSPName = txtCSPName.Text.Trim().ToString();
        string OffPhone = txtOffPhone.Text.Trim().ToString();
        string MobPhone = txtMobPhone.Text.Trim().ToString();
        string Fax = txtFax.Text.Trim().ToString();
        string Email = txtEmail.Text.Trim().ToString();
        string Code = txtCode.Text.Trim().ToString();
        string SubCode = txtSubCode.Text.Trim().ToString();
        string AgeCustID = txtAgeCustID.Text.Trim().ToString();

        wcf.Action = 2;
        wcf.Active = 1;
        wcf.Wid = int.Parse(Request.QueryString["Rateid"].ToString());
        wcf.AgencyName = AgencyName;
        wcf.Address = Address;       
        wcf.City = City;
        wcf.State = State;
        wcf.Zip = Zip;
        wcf.ProducerName = ProducerName;
        wcf.CSPName = CSPName;
        wcf.OfficePhone = OffPhone;
        wcf.MobilePhone = MobPhone;
        wcf.Fax = Fax;
        wcf.Email = Email;
        wcf.Code = Code;
        wcf.SubCode = SubCode;
        wcf.AgencyCustomerID = AgeCustID;

        WC_Agency_frm.Add_Update_delete_WC_Agency_MASTER(wcf);

        lblStatus.Visible = true;
        lblStatus.Text = "Updated Successfully";

        ClearControls();


    }

    public void ClearControls()
    {
        txtAgencyName.Text = "";
        txtAddress.Text = "";
        txtCity.Text = "";
        txtState.Text = "";
        txtZip.Text = "";
        txtProducerName.Text = "";
        txtCSPName.Text = "";
        txtOffPhone.Text = "";
        txtMobPhone.Text = "";
        txtFax.Text = "";
        txtEmail.Text = "";
        txtCode.Text = "";
        txtSubCode.Text = "";
        txtAgeCustID.Text = "";

    }
                           

      
}

Database Table and Stored Procedure


CREATE TABLE [dbo].[TBL_WC_AGENCY_MASTER](
    [Wid] [int] IDENTITY(1,1) NOT NULL,
    [AgencyName] [nvarchar](200) NULL,
    [Address] [nvarchar](200) NULL,
    [City] [nvarchar](200) NULL,
    [State] [nvarchar](200) NULL,
    [Zip] [nvarchar](200) NULL,
    [ProducerName] [nvarchar](200) NULL,
    [CSPName] [nvarchar](200) NULL,
    [OfficePhone] [nvarchar](200) NULL,
    [MobilePhone] [nvarchar](200) NULL,
    [Fax] [nvarchar](200) NULL,
    [Email] [nvarchar](200) NULL,
    [Code] [nvarchar](200) NULL,
    [SubCode] [nvarchar](200) NULL,
    [AgencyCustomerID] [nvarchar](200) NULL,
    [Active] [int] NULL,
 CONSTRAINT [PK_TBL_WC_AGENCY_MASTER] PRIMARY KEY CLUSTERED
(
    [Wid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


-----------------------------------------------------------------------------




Create procedure [dbo].[SP_TBL_WC_AGENCY_MASTER](
@Action int,
@Wid int,
@AgencyName nvarchar(200)=NULL,
@Address nvarchar(200)=NULL,
@City nvarchar(200)=NULL,
@State nvarchar(200)= NULL,
@Zip nvarchar(200)= NULL,
@ProducerName nvarchar(200)=null,
@CSPName nvarchar(200)=null,
@OfficePhone nvarchar(200)=null,
@MobilePhone nvarchar(200)=null,
@Fax nvarchar(200)=null,
@Email nvarchar(200)=null,
@Code nvarchar(200)=null,
@SubCode nvarchar(200)=null,
@AgencyCustomerID nvarchar(200)=null,
@Active int
)
as
begin

IF (@ACTION = 1)   
   BEGIN                             
   INSERT INTO TBL_WC_AGENCY_MASTER VALUES(@AgencyName,@Address,@City,@State,@Zip,@ProducerName,@CSPName,@OfficePhone,@MobilePhone,@Fax,@Email,@Code,@SubCode,@AgencyCustomerID,@Active)                      
   END                                 
  IF(@ACTION = 2)                                 
   BEGIN                                 
   UPDATE TBL_WC_AGENCY_MASTER set AgencyName=@AgencyName,Address=@Address,City=@City,State=@State,Zip=@Zip,ProducerName=@ProducerName,CSPName=@CSPName,OfficePhone=@OfficePhone,MobilePhone=@MobilePhone,Fax=@Fax,Email=@Email,Code=@Code,SubCode=@SubCode,AgencyCustomerID=@AgencyCustomerID where Wid=@Wid                    
   END                         
  IF(@ACTION = 3)                                 
   BEGIN                                 
   UPDATE TBL_WC_AGENCY_MASTER  set Active=0 where Wid=@Wid                            
   END        
  if(@ACTION = 4)                                 
   BEGIN                                 
   SELECT * FROM TBL_WC_AGENCY_MASTER  WHERE ACTIVE=1                    
   END  
  
   if(@ACTION = 5)                                 
   BEGIN                                 
   SELECT * FROM TBL_WC_AGENCY_MASTER  WHERE ACTIVE=1 and Wid=@Wid                             
   END  
  
    if(@ACTION = 6)                                 
   BEGIN                                 
   SELECT * FROM TBL_WC_AGENCY_MASTER  WHERE ACTIVE=1 and AgencyName=@AgencyName                           
   END    
                                 
    
                              
END

JavaScript Client side Validations

JavaScript Client side Validations

function Propsectvalidate() {

// For Textbox

 if (document.getElementById("<%=txtNameofBusiness.ClientID%>").value == "") {
                alert("Prospect Name can not be blank");
                document.getElementById("<%=txtNameofBusiness.ClientID%>").focus();
                return false;
            }

// For dropdownlist

if (document.getElementById("<%=ddlSalesRep.ClientID %>").value == 0) {
                alert("Please select the SalesRep Name");
                document.getElementById("<%=ddlSalesRep.ClientID%>").focus();
                return false;
            }

// For Checkboxlist and Radiobuttonlist

            //Start---Validation for checkbox list

            if (document.getElementById("<%=chklProductType.ClientID%>") != null) {

                var isChecked = 0;
                var list = document.getElementById('<%= chklProductType.ClientID %>');
                var chkList = list.getElementsByTagName("input");

                for (var i = 0; i < chkList.length; i++) {
                    if (chkList[i].checked) {
                        isChecked = 1;

                    }
                }
                if (isChecked == 0) {
                    alert("Please Check Product Type");
                    document.getElementById("<%=chklProductType.ClientID%>").focus();
                    return false;
                }
            }

            //End--Validation for checkbox list---

}



JavaScript functions for validating Numbers and Date and alphanumeric

function valAlpha() {
            if (event.keyCode >= 97 && event.keyCode <= 122 || event.keyCode >= 65 && event.keyCode <= 90 || event.keyCode == 32) event.returnValue = true;
            else
                event.returnValue = false;
        }
        function valNum() {
            if ((event.keyCode == 48 || event.keyCode == 49 || event.keyCode == 50 || event.keyCode == 51 || event.keyCode == 52 || event.keyCode == 53 || event.keyCode == 54 || event.keyCode == 55 || event.keyCode == 56 || event.keyCode == 57)) event.returnValue = true;
            else
                event.returnValue = false;
        }
        function valDate() {
            if ((event.keyCode == 48 || event.keyCode == 49 || event.keyCode == 50 || event.keyCode == 51 || event.keyCode == 52 || event.keyCode == 53 || event.keyCode == 54 || event.keyCode == 55 || event.keyCode == 56 || event.keyCode == 57 || event.keyCode == 47)) event.returnValue = true;
            else
                event.returnValue = false;
        }
        function valDecimal() {
            if ((event.keyCode == 48 || event.keyCode == 49 || event.keyCode == 50 || event.keyCode == 51 || event.keyCode == 52 || event.keyCode == 53 || event.keyCode == 54 || event.keyCode == 55 || event.keyCode == 56 || event.keyCode == 57 || event.keyCode == 46)) event.returnValue = true;
            else
                event.returnValue = false;
        }

javascript Mask Functions for Phonenumbers and Zipcode and etc....

 script src="../js/Mask.js" type="text/javascript" 



Mask.js
// Mask for Phone numbers and Zipcode


var zChar = new Array(' ', '(', ')', '-', '.');
var maxphonelength = 14;
var maxFEINlength = 10;
var maxZipcodelength = 10;
var maxSSNlength = 11;
var phonevalue1;
var phonevalue2;
var cursorposition;



function ParseForNumber1(object) {
    phonevalue1 = ParseChar(object.value, zChar);
}

function ParseForNumber2(object) {
    phonevalue2 = ParseChar(object.value, zChar);
}

function backspacerUPSSN(object, e) {
    if (e) {
        e = e
    } else {
        e = window.event
    }
    if (e.which) {
        var keycode = e.which
    } else {
        var keycode = e.keyCode
    }

    ParseForNumber1(object)

    if (keycode >= 48) {
        ValidateSSN(object)
    }
}

function backspacerUPPhone(object, e) {
    if (e) {
        e = e
    } else {
        e = window.event
    }
    if (e.which) {
        var keycode = e.which
    } else {
        var keycode = e.keyCode
    }

    ParseForNumber1(object)

    if (keycode >= 48) {
        ValidatePhone(object)
    }
}

function backspacerUPFEIN(object, e) {
    if (e) {
        e = e
    } else {
        e = window.event
    }
    if (e.which) {
        var keycode = e.which
    } else {
        var keycode = e.keyCode
    }

    ParseForNumber1(object)

    if (keycode >= 48) {
        ValidateFEIN(object)
    }
}

function backspacerUPZipcode(object, e) {
    if (e) {
        e = e
    } else {
        e = window.event
    }
    if (e.which) {
        var keycode = e.which
    } else {
        var keycode = e.keyCode
    }

    ParseForNumber1(object)

    if (keycode >= 48) {
        ValidateZipcode(object)
    }
}
function backspacerDOWNSSN(object, e) {
    if (e) {
        e = e
    } else {
        e = window.event
    }
    if (e.which) {
        var keycode = e.which
    } else {
        var keycode = e.keyCode
    }
    ParseForNumber2(object)
}

function backspacerDOWNPhone(object, e) {
    if (e) {
        e = e
    } else {
        e = window.event
    }
    if (e.which) {
        var keycode = e.which
    } else {
        var keycode = e.keyCode
    }
    ParseForNumber2(object)
}

function backspacerDOWNFEIN(object, e) {
    if (e) {
        e = e
    } else {
        e = window.event
    }
    if (e.which) {
        var keycode = e.which
    } else {
        var keycode = e.keyCode
    }
    ParseForNumber2(object)
}

function backspacerDOWNZipcode(object, e) {
    if (e) {
        e = e
    } else {
        e = window.event
    }
    if (e.which) {
        var keycode = e.which
    } else {
        var keycode = e.keyCode
    }
    ParseForNumber2(object)
}

function GetCursorPosition() {

    var t1 = phonevalue1;
    var t2 = phonevalue2;
    var bool = false
    for (i = 0; i < t1.length; i++) {
        if (t1.substring(i, 1) != t2.substring(i, 1)) {
            if (!bool) {
                cursorposition = i
                window.status = cursorposition
                bool = true
            }
        }
    }
}

function ValidateFEIN(object) {

    var p = phonevalue1
    if (p.length < 2) {
        object.value = p
    } else if (p.length >= 9) {
        p = p;
        l30 = p.length;
        p30 = p.substring(0, 2);
        p30 = p30 + "-"
        p31 = p.substring(2, p.length);
        pp = p30 + p31;
        object.value = pp.substring(0, maxFEINlength);
    }
}
function ValidateZipcode(object) {

    var p = phonevalue1

    if (p.length < 5) {
        object.value = p
    } else if (p.length >= 10) {
        p = p;
        l30 = p.length;
        p30 = p.substring(0, 5);
        p30 = p30 + "-"
        p31 = p.substring(5, p.length);
        pp = p30 + p31;
        object.value = pp.substring(0, maxZipcodelength);
    }
}
function ValidateSSN(object) {

    var p = phonevalue1

    if (p.length < 5) {
        object.value = p
    } else if (p.length >= 10) {
        p = p;
        l30 = p.length;
        p30 = p.substring(0, 3);
        p30 = p30 + "-"
        p31 = p.substring(0, 2);
        p31 = p31 + "-"
        p32 = p.substring(0, 6);
        pp = p30 + p31 + p32;
        object.value = pp.substring(0, maxSSNlength);
    }
}
function ValidatePhone(object) {

    var p = phonevalue1

    p = p.replace(/[^\d]*/gi, "")

    if (p.length < 3) {
        object.value = p
    } else if (p.length == 3) {
        pp = p;
        d4 = p.indexOf('(')
        d5 = p.indexOf(')')
        if (d4 == -1) {
            pp = "(" + pp;
        }
        if (d5 == -1) {
            pp = pp + ")";
        }
        object.value = pp;
    } else if (p.length > 3 && p.length < 7) {
        p = "(" + p;
        l30 = p.length;
        p30 = p.substring(0, 4);
        p30 = p30 + ") "

        p31 = p.substring(4, l30);
        pp = p30 + p31;

        object.value = pp;

    } else if (p.length >= 7) {
        p = "(" + p;
        l30 = p.length;
        p30 = p.substring(0, 4);
        p30 = p30 + ") "

        p31 = p.substring(4, l30);
        pp = p30 + p31;

        l40 = pp.length;
        p40 = pp.substring(0, 9);
        p40 = p40 + "-"

        p41 = pp.substring(9, l40);
        ppp = p40 + p41;

        object.value = ppp.substring(0, maxphonelength);
    }

    GetCursorPosition()

    if (cursorposition >= 0) {
        if (cursorposition == 0) {
            cursorposition = 2
        } else if (cursorposition <= 2) {
            cursorposition = cursorposition + 1
        } else if (cursorposition <= 4) {
            cursorposition = cursorposition + 3
        } else if (cursorposition == 5) {
            cursorposition = cursorposition + 3
        } else if (cursorposition == 6) {
            cursorposition = cursorposition + 3
        } else if (cursorposition == 7) {
            cursorposition = cursorposition + 4
        } else if (cursorposition == 8) {
            cursorposition = cursorposition + 4
            e1 = object.value.indexOf(')')
            e2 = object.value.indexOf('-')
            if (e1 > -1 && e2 > -1) {
                if (e2 - e1 == 4) {
                    cursorposition = cursorposition - 1
                }
            }
        } else if (cursorposition == 9) {
            cursorposition = cursorposition + 4
        } else if (cursorposition < 11) {
            cursorposition = cursorposition + 3
        } else if (cursorposition == 11) {
            cursorposition = cursorposition + 1
        } else if (cursorposition == 12) {
            cursorposition = cursorposition + 1
        } else if (cursorposition >= 13) {
            cursorposition = cursorposition
        }
        var txtRange = object.createTextRange();
        txtRange.moveStart("character", cursorposition);
        txtRange.moveEnd("character", cursorposition - object.value.length);
        txtRange.select();
    }
}

function ParseChar(sStr, sChar) {

    if (sChar.length == null) {
        zChar = new Array(sChar);
    }
    else zChar = sChar;

    for (i = 0; i < zChar.length; i++) {
        sNewStr = "";

        var iStart = 0;
        var iEnd = sStr.indexOf(sChar[i]);

        while (iEnd != -1) {
            sNewStr += sStr.substring(iStart, iEnd);
            iStart = iEnd + 1;
            iEnd = sStr.indexOf(sChar[i], iStart);
        }
        sNewStr += sStr.substring(sStr.lastIndexOf(sChar[i]) + 1, sStr.length);

        sStr = sNewStr;
    }

    return sNewStr;
}

// Remove Specail Charactes like $,',.,etc

function CheckRatesandSPCharacters(object)
 {
    var temp = object.value;
    temp = temp.replace(/[^a-zA-Z 0-9.]+/g, '');
    var result = Math.round(temp);  
    object.value = result;
}

function CheckRatesandSPCharacters1(object) {
    var temp = object.value;
    temp = temp.replace(/[^a-zA-Z 0-9.]+/g, '');
    var temp2 = Math.round(temp);
    var result = '$' + temp2;
    object.value = result;
}



// Checks for the following valid date formats:
// MM/DD/YY   MM/DD/YYYY   MM-DD-YY   MM-DD-YYYY
// Also separates date into month, day, and year variables

function isValidDate(object) {

    var dateStr = object.value;
  
    var datePat = /^(\d{1,2})(\/|-)(\d{1,2})\2(\d{2}|\d{4})$/;

    // To require a 4 digit year entry, use this line instead:
    // var datePat = /^(\d{1,2})(\/|-)(\d{1,2})\2(\d{4})$/;

    var matchArray = dateStr.match(datePat); // is the format ok?

    if (matchArray == null) {
        alert("Date is not in a valid format.")
        object.value = "";
        object.focus();
        return false;
    }
    month = matchArray[1]; // parse date into variables
    day = matchArray[3];
    year = matchArray[4];
    if (month < 1 || month > 12) { // check month range
        alert("Month must be between 1 and 12.");
        object.value = "";
        object.focus();
        return false;
    }
    if (day < 1 || day > 31) {
        alert("Day must be between 1 and 31.");
        object.value = "";
        object.focus();
        return false;
    }
    if ((month == 4 || month == 6 || month == 9 || month == 11) && day == 31) {
        alert("Month " + month + " doesn't have 31 days!")
        object.value = "";
        object.focus();
        return false
    }
    if (month == 2) { // check for february 29th
        var isleap = (year % 4 == 0 && (year % 100 != 0 || year % 400 == 0));
        if (day > 29 || (day == 29 && !isleap)) {
            alert("February " + year + " doesn't have " + day + " days!");
            object.value = "";
            object.focus();
            return false;
        }
    }
    return true;  // date is valid
}

function PhoneNumberFormat(formfield) {
    var currentValue = new String(formfield.value);
    var id = formfield.id;
    var currentStrippedValue = ReplaceAll(ReplaceAll(ReplaceAll(ReplaceAll(currentValue, "-", ""), " ", ""), "(", ""), ")", "");  //strip the dashes or parentheses from phone number
    lastField = id;

    if (currentStrippedValue.length > 0) {
        if (isNaN(currentStrippedValue)) {
            alert("Please Enter Numeric Values Only (###)###-####");  //Make sure only numbers are entered
            formfield.value = "";
            formfield.select();
            lastFieldIsValid = false;
            return;
        } else {
            if (currentStrippedValue.length != 10)  //Verify that the area code and telephone number are entered
            {
                alert("Phone # Must Be 10 Numbers Long (###)###-####");
                formfield.select();
                lastFieldIsValid = false;
                return;
            }
            else {
                lastFieldIsValid = true;
                formfield.value = "(" + currentStrippedValue.substring(0, 3) + ") " + currentStrippedValue.substring(3, 6) + "-" + currentStrippedValue.substring(6, 10); //Format the string for correct display
            }
        }
    }
}

function ReplaceAll(checkMe, toberep, repwith) {
    var temp = checkMe;
    var i = temp.indexOf(toberep);
    while (i > -1) { //Loop through and replace all instances
        temp = temp.replace(toberep, repwith);
        i = temp.indexOf(toberep);
    }
    return temp;
}

/* This script and many more are available free online at
The JavaScript Source!! http://javascript.internet.com
Created by: Pavel Donchev | http://chameleonbulgaria.com/ */

function currency(which) {
    currencyValue = which.value;
    currencyValue = currencyValue.replace(",", "");
    decimalPos = currencyValue.lastIndexOf(".");
    if (decimalPos != -1) {
        decimalPos = decimalPos + 1;
    }
    if (decimalPos != -1) {
        decimal = currencyValue.substring(decimalPos, currencyValue.length);
        if (decimal.length > 2) {
            decimal = decimal.substring(0, 2);
        }
        if (decimal.length < 2) {
            while (decimal.length < 2) {
                decimal += "0";
            }
        }
    }
    if (decimalPos != -1) {
        fullPart = currencyValue.substring(0, decimalPos - 1);
    } else {
        fullPart = currencyValue;
        decimal = "00";
    }
    newStr = "";
    for (i = 0; i < fullPart.length; i++) {
        newStr = fullPart.substring(fullPart.length - i - 1, fullPart.length - i) + newStr;
        if (((i + 1) % 3 == 0) & ((i + 1) > 0)) {
            if ((i + 1) < fullPart.length) {
                newStr = "," + newStr;
            }
        }
    }
    which.value = newStr + "." + decimal;
}

function normalize(which) {
    alert("Normal");
    val = which.value;
    val = val.replace(",", "");
    which.value = val;
}