sql Procedure with ROLLBACK TRAN

Simply Copy and past and run
——————————
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
—————————————

function Validate() {
if (!CheckValidate(“”, “Employee ID”)) {
document.getElementById(“”).style.backgroundColor = “red”;
return false;
}
if (!CheckValidate(“”, “Employee Name”)) {
document.getElementById(“”).style.backgroundColor = “red”;
return false;
}

}

function CheckValidate(ControlID, msg)
{
var VarControlID = document.getElementById(ControlID);
if (VarControlID.value == “”)
{
alert(msg + ‘ cannot be Blank!’);
VarControlID.focus();
return false;
}
return true;
}

Employee ID        : 

Employee Name   : 

   

                 

——————————————————————————————
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);
}
}
}
——————————————————————————————————————-

Advertisements

Reasearcher in microsoft product