Pabitra@Microsoft.Research: sql Procedure with error handeling

Simply Copy and past it in ur Sql server and webform it will work
------------------------------------------------------------------

Write this code in Sql
--------------------------

CREATE TABLE tblEmp
(
em_Id int NOT NULL primary key,
em_Name varchar(200) NULL
)

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

CREATE PROCEDURE tblEmpInsert --1001,'pabitramicrosoftreasearch.blogspot.com'
    -- Add the parameters for the stored procedure here
@em_Id        int,
@em_Name      varchar(200)=NULL,
@msg      varchar(200)=NULL output
AS
BEGIN --Starts begin
Declare @errorDueto INT ---Declare For Error Checking

SET NOCOUNT ON;---Declare For not Count the rows affected
BEGIN TRAN Mytransatation --Mytransatation = Is likea Alias

    SET @errorDueto = 0
    Insert into tblEmp(em_Id,em_Name) values(@em_Id,@em_Name)


    IF (@@ERROR <> 0)
     SET @errorDueto = 100

     IF (@@ERROR = 0)
     SET @msg='Emplyee Save Successfully'
     ELSE
     SET @msg=' faild due to '+@errorDueto

    IF(@errorDueto <> 0)
    BEGIN
    ROLLBACK TRAN Mytransatation
    RETURN @errorDueto
    END
    ELSE
    COMMIT TRAN Mytransatation
end ------end of begin
-----------------------------------------------

MyEmployee Design Page
---------------------------------------

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="Scripts/jquery-1.7.1.min.js"></script>
     <script type="text/javascript">
         function Validate() {
             if (!CheckValidate("<%=txtEmpID.ClientID %>", "Employee ID")) {
                document.getElementById("<%=txtEmpID.ClientID %>").style.backgroundColor = "red";
                return false;
            }
             if (!CheckValidate("<%=txtEmpName.ClientID %>", "Employee Name")) {
                document.getElementById("<%=txtEmpName.ClientID %>").style.backgroundColor = "red";
                return false;
            }

        }
    </script>
   <script type="text/javascript">
    function CheckValidate(ControlID, msg)
           {
               var VarControlID = document.getElementById(ControlID);
               if (VarControlID.value == "")
                    {
                      alert(msg + ' cannot be Blank!');
                      VarControlID.focus();
                      return false;
                    }
          return true;
        }
   </script>

</head>
<body>
    <form id="form1" runat="server">
    <div>

        Employee ID        : 
        <asp:TextBox ID="txtEmpID" runat="server"></asp:TextBox>
        <br />
        Employee Name   : 
        <asp:TextBox ID="txtEmpName" runat="server"></asp:TextBox>
        <br />
   
        <br />
                 
        <asp:Button ID="btnSave" OnClientClick="return Validate();" runat="server" Text="Save" OnClick="btnSave_Click" />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />

    </div>
    </form>
</body>
</html>
------------------------------------------------------------------------------------------
My Class file
-------------------------------------------------------------------------------------------
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 System.Data;
public partial class EmployeeDtl : System.Web.UI.Page
{
    string SqlCon = "server=.;uid=sa;pwd=pabitramicrosoft;database=master";
     //or
   // string Sqlconn=System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString();
    SqlConnection con;
    SqlCommand cmd;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnSave_Click(object sender, EventArgs e)
    {

        try
        {
            string Message = "";
            con = new SqlConnection(SqlCon);
            cmd = new SqlCommand("tblEmpInsert", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@em_Id", Convert.ToInt32(txtEmpID.Text));
           // cmd.Parameters.AddWithValue("@em_Id", "qq");
            //cmd.Parameters.AddWithValue("@em_Id", "133.345");
            cmd.Parameters.AddWithValue("@em_Name", txtEmpName.Text);
            SqlParameter p = new SqlParameter("@msg", SqlDbType.VarChar, 8000);
            p.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(p);
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            cmd.ExecuteNonQuery();
            Message = (string)cmd.Parameters["@msg"].Value;
            con.Close();
            ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + Message + "');", true);
        }
        catch (SqlException ex)
        {
            ClientScript.RegisterStartupScript(GetType(), "alert", "alert('"+ex.Message+"');", true);
        }
        catch (Exception ex)
        {
            ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + ex.Message+ "');", true);
        }
    }
}
-------------------------------------------------------------------------------------------------------------------

Pabitra@Microsoft.Research

Translate

Saturday, 14 February 2015

sql Procedure with ROLLBACK TRAN

Blog Archive