In this post, I explained same stored procedure, that contains insert and update data. You can see the stored procedure and table script below.
private void btnSave_Click(objectsender, EventArgs e)
{
if(con.State == ConnectionState.Closed) con.Open();
{
cmd = newSqlCommand("Usp_Ins_BranchDetails ", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@p_branchcode", txtChitNo.Text);
cmd.Parameters.AddWithValue("@p_branchname", txtChitName.Text);
cmd.Parameters.AddWithValue("@p_abbr ", txtChitAmount.Text);
cmd.Parameters.AddWithValue("@p_address", txtChitInstAmount.Text);
cmd.Parameters.AddWithValue("@p_city ", txtNumOfInst.Text);
cmd.Parameters.AddWithValue("@p_state", txtBidDay.Text);
cmd.Parameters.AddWithValue("@p_pincode", mtxtStartDate.Text);
cmd.Parameters.AddWithValue("@p_phoneno", mtxtEndDate.Text);
cmd.Parameters.AddWithValue("@p_mobileno", txtDedAmount.Text);
cmd.Parameters.AddWithValue("@p_fax", txtNonBidInst.Text);
cmd.Parameters.AddWithValue("@p_branchtype", "A");
cmd.Parameters.AddWithValue("@p_openingdate", txtRemarks.Text);
cmd.Parameters.AddWithValue("@p_inchargename", Program.userID);
cmd.Parameters.AddWithValue("@p_inchargephonno", Program.userID);
cmd.Parameters.AddWithValue("@p_status", Program.userID);
cmd.Parameters.AddWithValue("@p_creator", Program.userID);
cmd.Parameters.AddWithValue("@p_mode", mode);
cmd.Parameters.Add("@p_result", SqlDbType.Int);
cmd.Parameters["@p_result"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
intresult = Convert.ToInt32(cmd.Parameters["@p_result"].Value);
if(result == -1)
{
MessageBox.Show("Please Check the Data.", "COMPANY");
}
else
{
if(mode == "I")
MessageBox.Show("Information Posted Successfully.", "COMPANY");
else
MessageBox.Show("Details Updated Successfully.", "COMPANY");
getChitDetails();
Clear();
}
}
============================================================================
This si my stored procedure
ALTER procedure [dbo].[Usp_Ins_BranchDetails]
(
@p_branchcode varchar(10),
@p_branchname varchar(30),
@p_abbr varchar(10),
@p_address varchar(100),
@p_city varchar(30),
@p_state varchar(20),
@p_pincode varchar(8),
@p_phoneno varchar(15),
@p_mobileno varchar(15),
@p_fax varchar(15),
@P_branchtype varchar(2),
@p_openingdate datetime,
@P_inchargename varchar(50),
@P_inchargephno varchar(20),
@p_status varchar(2),
@p_creater varchar(20),
@p_mode varchar(1),
@p_result int output
)
as
set nocount on
Begin
Begin Transaction
Begin Try
if (@p_mode='I')
Begin
insert into dbo.BRANCH_MASTER(BM_BRANCH_CODE,BM_BRANCH_NAME,
BM_ADDRESS,BM_CITY,BM_STATE,BM_PINCOE_CODE,BM_PHONE,BM_MOBILE,
BM_FAX,BM_BRANCH_TYPE,BM_OPENING_DATE,BM_INCHARGE_NAME,
BM_INCHARGE_PHNO,BM_STATUS,BM_CREATED_ON,BM_CREATED_BY,BM_ABBR)
values
(@p_branchcode,@p_branchname,@p_address,@p_city,@p_state,
@p_pincode,@p_phoneno,@p_mobileno,@p_fax,@P_branchtype,
@p_openingdate,@P_inchargename,@P_inchargephno,@p_status,
getdate(),@p_creater,@p_abbr)
End
Else
Begin
UpdateBRANCH_MASTER set BM_BRANCH_NAME=@p_branchname,BM_ADDRESS=@p_address,
BM_CITY=@p_city,BM_STATE=@p_state, BM_PINCOE_CODE=@p_pincode,
BM_PHONE=@p_phoneno,BM_MOBILE=@p_mobileno,BM_FAX=@p_fax,BM_ABBR=@p_abbr,
BM_BRANCH_TYPE=@P_branchtype,BM_OPENING_DATE=@p_openingdate,
BM_INCHARGE_NAME=@P_inchargename,BM_INCHARGE_PHNO=@P_inchargephno,
BM_STATUS=@p_status,BM_MODIFIED_ON=getdate(),BM_MODIFIED_BY=getdate()
whereBM_BRANCH_CODE=@p_branchcode
End
Commit Transaction
set @p_result=1
End Try
Begin Catch
Rollback Transaction
set @p_result=-1
End Catch
End
=============================================================================
This is my table script :
CREATE TABLE [dbo].[BRANCH_MASTER](
[BM_BRANCH_CODE] [varchar](10) NOT NULL,
[BM_ABBR] [nchar](10) NULL,
[BM_BRANCH_NAME] [varchar](30) NOT NULL,
[BM_ADDRESS] [varchar](100) NOT NULL,
[BM_CITY] [varchar](30) NULL,
[BM_STATE] [varchar](20) NOT NULL,
[BM_PINCOE_CODE] [varchar](8) NULL,
[BM_PHONE] [varchar](15) NOT NULL,
[BM_MOBILE] [varchar](15) NULL,
[BM_FAX] [varchar](15) NULL,
[BM_BRANCH_TYPE] [varchar](2) NOT NULL,
[BM_OPENING_DATE] [datetime] NOTNULL,
[BM_EXPIRY_DATE] [datetime] NULL,
[BM_INCHARGE_NAME] [varchar](50) NOT NULL,
[BM_INCHARGE_PHNO] [varchar](20) NULL,
[BM_STATUS] [varchar](2) NULL,
[BM_CREATED_ON] [datetime] NOT NULL,
[BM_CREATED_BY] [varchar](12) NOT NULL,
[BM_MODIFIED_ON] [datetime] NULL,
[BM_MODIFIED_BY] [varchar](12) NULL,
CONSTRAINT[PK__BRANCH_MASTER__1B0907CE] PRIMARY KEY CLUSTERED
(
[BM_BRANCH_CODE] 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
SET ANSI_PADDING OFF
GO
No comments:
Post a Comment