Transaction count exception in vb.net (SQL Exception)

Question

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

Answer 1

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.

Answer 2

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