Transaction count exception in vb.net (SQL Exception)
I am getting below exception when i am going to enter record in a table through StoredProceduer of sqlserver i have a field which i need u update immediately after insert of new record in table. for that i created a sotredprocedure in which i wrote insert command first and after that i wrote a update command for same recored but it gave me below error SQlException: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2. Uncommittable transaction is detected at the end of the batch. The transaction is rolled back. Some Error in stored procedure execution
i tried in another way also like: i just wrote insert command in storedprocedure and i created a afterInsert trigger in which i am firing update command. but still i am facing above exception
Details: Table structure:
CREATE TABLE [dbo].[TabStoreGRNMaster](
[pk_grnm_id] [bigint] IDENTITY(1,1) NOT NULL,
[fk_col_id] [bigint] NULL,
[fk_sup_id] [bigint] NULL,
[grnm_grnno] [varchar](50) NULL,
[grnm_date] [nvarchar](10) NULL,
[grnm_partyinvno] [varchar](50) NULL,
[grnm_invdate] [nvarchar](10) NULL,
[grnm_freight] [numeric](7, 2) NULL,
[grnm_otherchrg] [numeric](7, 2) NULL,
[grnm_roundoff] [bit] NULL,
[Fk_User_Id] [bigint] NULL,
[grnm_EntryDate] [nvarchar](8) NULL,
CONSTRAINT [PK_TabStoreGRNMaster] PRIMARY KEY CLUSTERED
(
[pk_grnm_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
storedprocedure:
USE [Kollege]
GO
/****** Object: StoredProcedure [dbo].[StoreGRNMaster] Script Date: 11/27/2009 10:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[StoreGRNMaster]
(
@pk_grnm_id [bigint] output ,
@fk_col_id [bigint] = 0 ,
@fk_sup_id [bigint] = 0 ,
@grnm_grnno [varchar](50) = '',
@grnm_date [nvarchar](10) = null,
@grnm_partyinvno [varchar](50) = '',
@grnm_invdate [nvarchar](10) = null,
@grnm_freight [numeric] = 0 ,
@grnm_otherchrg [numeric] = 0 ,
@grnm_roundoff [bit] = 0 ,
@Fk_User_Id [bigint] = 0 ,
@grnm_EntryDate [nvarchar](8) = null,
@opt [BIGINT] =0,
@del [bit] =0
)
as
begin
SET NOCOUNT ON;
BEGIN TRY
begin transaction
if isnull(@opt,0) = 0 begin
INSERT INTO TabStoreGRNMaster
(
fk_col_id,
fk_sup_id,
grnm_grnno,
grnm_date,
grnm_partyinvno,
grnm_invdate,
grnm_freight,
grnm_otherchrg,
grnm_roundoff,
Fk_User_Id,
grnm_EntryDate
)
VALUES
(
@fk开发者_高级运维_col_id,
@fk_sup_id,
@grnm_grnno,
@grnm_date,
@grnm_partyinvno,
@grnm_invdate,
@grnm_freight,
@grnm_otherchrg,
@grnm_roundoff,
@Fk_User_Id,
@grnm_EntryDate
)
set @pk_grnm_id = @@identity
end
else
begin
if @del = 0
UPDATE TabStoreGRNMaster
SET
fk_col_id = @fk_col_id,
fk_sup_id = @fk_sup_id,
grnm_grnno = @grnm_grnno,
grnm_date = @grnm_date,
grnm_partyinvno = @grnm_partyinvno,
grnm_invdate = @grnm_invdate,
grnm_freight = @grnm_freight,
grnm_otherchrg = @grnm_otherchrg,
grnm_roundoff = @grnm_roundoff,
Fk_User_Id = @Fk_User_Id,
grnm_EntryDate = @grnm_EntryDate
WHERE
(
pk_grnm_id = @opt
)
if @del=1 and isnull(@opt,0) <> 0
DELETE from [TabStoreGRNMaster]
WHERE
( [pk_grnm_id] = @opt)
end
commit transaction
END TRY
BEGIN CATCH
raiserror ('Some Error in stored procedure execution',1,1)
END CATCH;
END
Trigger:
alter TRIGGER TGRStoreGRNMasterCode
ON tabStoreGRNMaster
AFTER INSERT
AS
begin
declare @pk varchar(10)
declare @colcode varchar(10)
declare @current_session varchar(20)
declare @colid bigint
set @pk = (select pk_grnm_id from Inserted)
--set @colid = (select fk_col_id from Inserted)
set @colcode= 'jiet'--(select col_code from tabcollegemaster where pk_col_id =5) (when i replace word 'Jiet' with commented qry i gets sql exception otherwise its working fine)
select @current_session = Ses_abbrev from TabAcaSessionMast where ses_current = 1
--update tabStoreGRNMaster set grnm_grnno= @colcode +'/' +@current_session+ '/' + @pk where pk_grnm_id=convert(bigint,@pk)
update tabStoreGRNMaster set grnm_grnno= (select col_code from tabcollegemaster where pk_col_id=5) +'/' +@current_session+ '/' + @pk where pk_grnm_id=convert(bigint,@pk)
end
Your comment to another answer mentioned "I am maintain Transactions from Vb.net also while calling this stored procedure". This is the cause
You have to balance your BEGIN TRAN and COMMITs/ROLLBACKs so @@TRANCOUNT starts and finishes with the same value for the same scope.
Either do it all in the stored proc or do it all in the client
It looks to me like you're not doing anything with the open transaction if an error is found in your TRY block - the COMMIT would not run, but your CATCH block has no ROLLBACK.
精彩评论