Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count...

0

Although this type of questions are already present in this site, since i don't find them useful in solving my problem, i'm posting my question here, my stored procedure is:

ALTER Proc [OxFund].[Usp_IUD_BudgetRevisionEntry]

(

    @Event char(2) = 'I', 

    @BDGREM_Id bigint = 0, 

    @BDGREM_No varchar(50)=Null,

    @BDGREM_Date datetime=Null,

    @BDGREM_Miti varchar(10)=Null,

    @BDGEM_Id bigint = 0, 

    @BDGEM_No varchar(50)=Null,

    @BDGEM_Date datetime=Null,

    @BDGEM_Miti varchar(10)=Null,

    @Donor_Id bigint=Null,

    @PR_Id bigint=Null,

    @Project_Name varchar(80)=Null,

    @Project_No varchar(50)=Null,

    @PeriodFrom_Date datetime=Null,

    @PeriodTo_Date datetime=Null,   

    @Cur_Id bigint=Null,

    @Cur_Rate decimal(18,4)=Null,

    @Net_BudgetAmt decimal(18,6)=Null,

    @Net_LocalBudgetAmt  decimal(18,6)=Null,

    @Remarks varchar(1024)=Null,

    @Created_By bigint=Null,

    @Created_Date datetime=Null,

    @Station varchar(5)=Null,

    @Branch_Id bigint=Null,

    @FiscalYear_Id bigint=Null,

    @AutoDescNo Varchar(75)=Null,

    @XmlBudgetEntryDetl varchar(max)=Null,

    @XmlBudgetEntryPeriodDetl varchar(max)=Null,

    @IP varchar(256)=Null,

    @Result Varchar(max) output ,

    @Return_Id bigint output

)

As

IF @BDGEM_Id=0

SET @BDGEM_Id=NULL

IF @Pr_Id=0

SET @Pr_Id=NULL

IF @Cur_Id=0

SET @Cur_Id=NULL

IF @PeriodFrom_Date='1753/01/01'

set @PeriodFrom_Date=NULL

IF @PeriodTo_Date='1753/01/01'

set @PeriodTo_Date=NULL

IF @Created_Date='1753/01/01'

set @Created_Date=NULL



Declare @xmlBudgetEntry xml 

        set   @xmlBudgetEntry  =Convert(xml,@XmlBudgetEntryDetl)    

        Set XAct_Abort On



 Declare @xmlBudgetEntryPeriod xml 

        set   @xmlBudgetEntryPeriod  =Convert(xml,@XmlBudgetEntryPeriodDetl)    

        Set XAct_Abort On 





BEGIN TRY

    BEGIN TRANSACTION     

    begin



        If @Event = 'I'  --for Insert

        Begin 

            --------------------------------------------- Validation Begin ------------------------------------------------------



            declare @Starting_No varchar(50)

            if exists ( select TOP 1 Starting_No from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            begin               

                set @Starting_No=(select TOP 1 Starting_No from OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id) 

                set @Starting_No=( select TOP 1 stuff(@Starting_No,1,0,REPLICATE(0,Padding_Len -len(@Starting_No) )) as StartingNo from OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id) 

                set @BDGREM_No= (select TOP 1 (Starting_Word+ convert(varchar(50),(@Starting_No))+convert(varchar(50),IsNull(Ending_Word,''))) as BDGEM_No from  OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            end



           --------------------------------------------- Validation End  -------------------------------------------------------

          If exists (select BDGREM_No from OxFund.BudgetRevisionEntry_Master where BDGREM_No=@BDGREM_No and Branch_Id=@Branch_Id and FiscalYear_Id=@FiscalYear_Id and AutoDesc=@AutoDescNo)

           Begin

            Set @Return_Id = 0

            Set @Result = 'Already Exists !'    

            COMMIT TRANSACTION

            return

          End



            set @BDGREM_Id=(Select CONVERT(BIGINT, CONVERT(VARCHAR(50),@Branch_Id) + CONVERT(VARCHAR(50),REPLICATE('0',3 -LEN(IsNull(MAX(@Branch_Id),@Branch_Id)))) + STUFF(IsNull(Right(MAX(BDGREM_Id),10),0)+1,1,0,REPLICATE('0',10 -LEN(IsNull(Right(MAX(BDGREM_Id),10),0)+1)))) from OxFund.BudgetRevisionEntry_Master Where Branch_Id=@Branch_Id)  

            If exists (Select BDGREM_No from  [OxFund].BudgetRevisionEntry_Master where BDGREM_Id=@BDGREM_Id )

            begin      

                set @BDGREM_Id=@BDGREM_Id+1     

            end 



            Insert Into OxFund.BudgetRevisionEntry_Master Values 

            (   

                @BDGREM_Id,

                @BDGREM_No,

                @BDGREM_Date,

                @BDGREM_Miti,

                @BDGEM_Id,

                @BDGEM_No,

                @BDGEM_Date,

                @BDGEM_Miti,        

                @Donor_Id,

                @Pr_Id,

                @Project_Name,

                @Project_No,

                @PeriodFrom_Date,

                @PeriodTo_Date,         

                @Cur_Id,

                @Cur_Rate,

                @Net_BudgetAmt,

                @Net_LocalBudgetAmt,

                @Created_By,

                @Created_Date,

                @Remarks,

                @Branch_Id,

                @FiscalYear_Id,

                @IP,

                @AutoDescNo

            )  

            Set @Return_Id = @BDGREM_Id --@@IDENTITY

            Set @Result = 'Record Inserted Successfully !'  



           --------------------------------------------- AC TRANSACTION END   -------------------------------------------------------



            If exists ( select TOP 1 Starting_No from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            begin   

                Set @Starting_No=(select TOP 1 Starting_No+1 from OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)                      

                Update OxFund.AutoGenerate set Starting_No=@Starting_No where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id

            END     



        End 



        If @Event = 'U'  or @Event ='I'   --- Update

        Begin

            --------------------------------------------- Validation Begin ------------------------------------------------------



           --------------------------------------------- Validation End  -------------------------------------------------------

            If @Event = 'U' 

            BEGIN

                Update OxFund.BudgetRevisionEntry_Master Set        

                [BDGREM_Date] = @BDGREM_Date,

                [BDGREM_Miti] = @BDGREM_Miti,

                [BDGEM_Id] = @BDGEM_Id,

                [BDGEM_No] = @BDGEM_No,

                [BDGEM_Date] = @BDGEM_Date,

                [BDGEM_Miti] = @BDGEM_Miti,

                [Donor_Id]=@Donor_Id,

                [PR_Id]=@Pr_Id,

                [Project_Name] = @Project_Name,     

                [Project_No]=@Project_No,       

                [PeriodFrom_Date] = @PeriodFrom_Date,

                [PeriodTo_Date] = @PeriodTo_Date,

                [Cur_Id] = @Cur_Id,

                [Cur_Rate] = @Cur_Rate, 

                [Net_BudgetAmt]=@Net_BudgetAmt,

                [Net_LocalBudgetAmt]=@Net_LocalBudgetAmt,

                [Created_By] = @Created_By,

                [Created_Date] = @Created_Date,

                [Remarks] = @Remarks,

                [Branch_Id] = @Branch_Id,

                [FiscalYear_Id] = @FiscalYear_Id,

                [IP]=@IP

                Where BDGREM_Id=@BDGREM_Id and BDGREM_No = @BDGREM_No

                Set @Result = 'Record Updated Successfully !'

                set @Return_Id=@BDGREM_Id

             END

             --------------------------------------------- BDGEM DETAILS BEGIN ------------------------------------------------------

             Declare @BDGRED_Id bigint

             DELETE FROM OxFund.BudgetRevisionEntry_Details WHERE BDGREM_Id=@BDGREM_Id and BDGREM_No = @BDGREM_No and Branch_Id=@Branch_Id

             set @BDGRED_Id=(Select CONVERT(BIGINT, CONVERT(VARCHAR(50),@Branch_Id) + CONVERT(VARCHAR(50),REPLICATE('0',3 -LEN(IsNull(MAX(@Branch_Id),@Branch_Id)))) + STUFF(IsNull(Right(MAX(BDGRED_Id),10),0)+1,1,0,REPLICATE('0',10 -LEN(IsNull(Right(MAX(BDGRED_Id),10),0)+1)))) from OxFund.BudgetRevisionEntry_Details  Where Branch_Id=@Branch_Id)   

             If exists (Select BDGRED_Id from  [OxFund].BudgetRevisionEntry_Details where BDGRED_Id=@BDGRED_Id )

             begin     

                set @BDGRED_Id=@BDGRED_Id+1     

             end

             INSERT INTO OxFund.BudgetRevisionEntry_Details

                    SELECT 

                    --ParamValues.ID.query('BDGRED_Id').value('.','bigint') As BDGRED_Id ,

                    BDGRED_Id=@BDGRED_Id + (ParamValues.ID.query('SNo').value('.','bigint')-1),   

                    BDGREM_Id=@Return_Id,

                    BDGREM_No=@BDGREM_No,

                    ParamValues.ID.query('SNo').value('.','bigint') As SNo ,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('PRA_Id').value('.', 'varchar(50)'),'')) AS PRA_Id,

                    ParamValues.ID.query('BH').value('.','VARCHAR(50)') As BH ,    

                    ParamValues.ID.query('Activity').value('.','VARCHAR(80)') As Activity ,   

                    ParamValues.ID.query('Gl_Code').value('.','VARCHAR(50)') As Gl_Code ,   

                    ParamValues.ID.query('C_Code').value('.','VARCHAR(50)') As C_Code , 

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('PRC_Id').value('.', 'varchar(50)'),'')) AS PRC_Id,  

                    ParamValues.ID.query('Contract').value('.','VARCHAR(50)') As Contract ,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('PRT_Id').value('.', 'varchar(50)'),'')) AS PRT_Id,   

                    ParamValues.ID.query('Theme').value('.','VARCHAR(50)') As Theme ,           

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id').value('.', 'varchar(50)'),'')) AS Place_Id,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id1').value('.', 'varchar(50)'),'')) AS Place_Id1,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id2').value('.', 'varchar(50)'),'')) AS Place_Id2,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id3').value('.', 'varchar(50)'),'')) AS Place_Id3,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id4').value('.', 'varchar(50)'),'')) AS Place_Id4,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id5').value('.', 'varchar(50)'),'')) AS Place_Id5,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id6').value('.', 'varchar(50)'),'')) AS Place_Id6,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id7').value('.', 'varchar(50)'),'')) AS Place_Id7,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id8').value('.', 'varchar(50)'),'')) AS Place_Id8,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id9').value('.', 'varchar(50)'),'')) AS Place_Id9,           

                    [Cur_Id] = @Cur_Id,

                    [Cur_Rate] = @Cur_Rate,     

                    ParamValues.ID.query('Budget_Amt').value('.','DECIMAL(18,4)') As Budget_Amt,          

                    ParamValues.ID.query('Local_BudgetAmt').value('.','DECIMAL(18,4)') As Local_BudgetAmt,

                    ParamValues.ID.query('Month1').value('.','DECIMAL(18,4)') As Month1, 

                    ParamValues.ID.query('Month2').value('.','DECIMAL(18,4)') As Month2, 

                    ParamValues.ID.query('Month3').value('.','DECIMAL(18,4)') As Month3, 

                    ParamValues.ID.query('Month4').value('.','DECIMAL(18,4)') As Month4, 

                    ParamValues.ID.query('Month5').value('.','DECIMAL(18,4)') As Month5, 

                    ParamValues.ID.query('Month6').value('.','DECIMAL(18,4)') As Month6, 

                    ParamValues.ID.query('Month7').value('.','DECIMAL(18,4)') As Month7, 

                    ParamValues.ID.query('Month8').value('.','DECIMAL(18,4)') As Month8, 

                    ParamValues.ID.query('Month9').value('.','DECIMAL(18,4)') As Month9, 

                    ParamValues.ID.query('Month10').value('.','DECIMAL(18,4)') As Month10, 

                    ParamValues.ID.query('Month11').value('.','DECIMAL(18,4)') As Month11, 

                    ParamValues.ID.query('Month12').value('.','DECIMAL(18,4)') As Month12, 

                    NULLIF(ParamValues.ID.query('Narration').value('.','VARCHAR(1024)'),'') As Narration,

                    @Branch_Id

                    FROM @xmlBudgetEntry.nodes('/DocumentElement/Temp') as ParamValues(ID)



           ---------------------------------------------  BDGEM DETAILS END   -------------------------------------------------------

           --------------------------------------------- Period BEGIN ------------------------------------------------------

             Delete from OxFund.BudgetRevisionEntry_PeriodDetails where BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No and Branch_Id=@Branch_Id



             Declare @BDGREPD_Id bigint  

             set @BDGREPD_Id=(Select CONVERT(BIGINT, CONVERT(VARCHAR(50),@Branch_Id) + CONVERT(VARCHAR(50),REPLICATE('0',3 -LEN(IsNull(MAX(@Branch_Id),@Branch_Id)))) + STUFF(IsNull(Right(MAX(BDGREPD_Id),10),0)+1,1,0,REPLICATE('0',10 -LEN(IsNull(Right(MAX(BDGREPD_Id),10),0)+1)))) from OxFund.BudgetRevisionEntry_PeriodDetails Where Branch_Id=@Branch_Id)   

             If exists (Select BDGREPD_Id from  [OxFund].BudgetRevisionEntry_PeriodDetails where BDGREPD_Id=@BDGREPD_Id )

             begin     

                set @BDGREPD_Id=@BDGREPD_Id+1       

             end

             INSERT INTO OxFund.BudgetRevisionEntry_PeriodDetails

                    SELECT 

                    ParamValues.ID.query('BDGREPD_Id').value('.','bigint') As BDGREPD_Id ,

                    --BDGREPD_Id=@BDGREPD_Id + (ParamValues.ID.query('SNo').value('.','bigint')-1), 

                    BDGREM_Id=@Return_Id,

                    BDGREM_No=@BDGREM_No,

                    BDGRED_Id=NULL,--@Return_Id,----Detail Id

                    ParamValues.ID.query('DSNo').value('.','bigint') As DSNo ,

                    ParamValues.ID.query('SNo').value('.','bigint') As SNo ,            

                    ParamValues.ID.query('Month_AName').value('.','VARCHAR(50)') As Month_AName ,

                    ParamValues.ID.query('Month_MName').value('.','VARCHAR(50)') As Month_MName ,

                    Cur_Id=@Cur_Id,

                    Cur_Rate=@Cur_Rate,             

                    ParamValues.ID.query('Month_Amt').value('.','DECIMAL(18,4)') As Month_Amt,           

                    ParamValues.ID.query('Local_MonthAmt').value('.','DECIMAL(18,4)') As Local_MonthAmt,

                    NULLIF(ParamValues.ID.query('Narration').value('.','VARCHAR(1024)'),'') As Narration,   

                    NULLIF(ParamValues.ID.query('Month_Date').value('.','DateTime'),'') As Month_Date,

                    @Branch_Id  

                    FROM @xmlBudgetEntryPeriod.nodes('/DocumentElement/Temp') as ParamValues(ID)



           --------------------------------------------- AC TRAN END   -------------------------------------------------------

        End 



        If @Event = 'D'   -- For Delete 

        Begin 



        --------------------------------------------- Validation Begin ------------------------------------------------------

            if exists ( select TOP 1 Starting_No from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            begin   

                IF( CONVERT(bigint,substring(@BDGREM_No,(select TOP 1 len(Starting_Word)+1 from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id),(select TOP 1 Padding_Len from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)))=(select TOP 1 Starting_No-1 from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id))                        

                update OxFund.AutoGenerate set Starting_No=Starting_No-1 where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id

            END 

        --------------------------------------------- Validation End  -------------------------------------------------------

            DELETE FROM OxFund.BudgetRevisionEntry_PeriodDetails WHERE BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No

            DELETE FROM OxFund.BudgetRevisionEntry_Details WHERE BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No 

            Delete from OxFund.BudgetRevisionEntry_Master Where BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No 

            Set @Result = 'Record Deleted Successfully !'   

            Set @Return_Id = @BDGREM_Id

        End 



    END

    COMMIT TRANSACTION

END TRY

    BEGIN CATCH

    set @Result=(select ERROR_MESSAGE() AS ErrorMessage)

    ROLLBACK

    END CATCH

Here i'm trying to do insert operation for budget revision task. Here for first time everything works properly, but next time when i again try to insert the budget revision voucher this kind of error is coming.

sql asp.net stored-procedures transactions

share|improve this question

asked Nov 21 '18 at 6:07

Dinesh LamaDinesh Lama

975

add a comment | 

0

Although this type of questions are already present in this site, since i don't find them useful in solving my problem, i'm posting my question here, my stored procedure is:

ALTER Proc [OxFund].[Usp_IUD_BudgetRevisionEntry]

(

    @Event char(2) = 'I', 

    @BDGREM_Id bigint = 0, 

    @BDGREM_No varchar(50)=Null,

    @BDGREM_Date datetime=Null,

    @BDGREM_Miti varchar(10)=Null,

    @BDGEM_Id bigint = 0, 

    @BDGEM_No varchar(50)=Null,

    @BDGEM_Date datetime=Null,

    @BDGEM_Miti varchar(10)=Null,

    @Donor_Id bigint=Null,

    @PR_Id bigint=Null,

    @Project_Name varchar(80)=Null,

    @Project_No varchar(50)=Null,

    @PeriodFrom_Date datetime=Null,

    @PeriodTo_Date datetime=Null,   

    @Cur_Id bigint=Null,

    @Cur_Rate decimal(18,4)=Null,

    @Net_BudgetAmt decimal(18,6)=Null,

    @Net_LocalBudgetAmt  decimal(18,6)=Null,

    @Remarks varchar(1024)=Null,

    @Created_By bigint=Null,

    @Created_Date datetime=Null,

    @Station varchar(5)=Null,

    @Branch_Id bigint=Null,

    @FiscalYear_Id bigint=Null,

    @AutoDescNo Varchar(75)=Null,

    @XmlBudgetEntryDetl varchar(max)=Null,

    @XmlBudgetEntryPeriodDetl varchar(max)=Null,

    @IP varchar(256)=Null,

    @Result Varchar(max) output ,

    @Return_Id bigint output

)

As

IF @BDGEM_Id=0

SET @BDGEM_Id=NULL

IF @Pr_Id=0

SET @Pr_Id=NULL

IF @Cur_Id=0

SET @Cur_Id=NULL

IF @PeriodFrom_Date='1753/01/01'

set @PeriodFrom_Date=NULL

IF @PeriodTo_Date='1753/01/01'

set @PeriodTo_Date=NULL

IF @Created_Date='1753/01/01'

set @Created_Date=NULL



Declare @xmlBudgetEntry xml 

        set   @xmlBudgetEntry  =Convert(xml,@XmlBudgetEntryDetl)    

        Set XAct_Abort On



 Declare @xmlBudgetEntryPeriod xml 

        set   @xmlBudgetEntryPeriod  =Convert(xml,@XmlBudgetEntryPeriodDetl)    

        Set XAct_Abort On 





BEGIN TRY

    BEGIN TRANSACTION     

    begin



        If @Event = 'I'  --for Insert

        Begin 

            --------------------------------------------- Validation Begin ------------------------------------------------------



            declare @Starting_No varchar(50)

            if exists ( select TOP 1 Starting_No from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            begin               

                set @Starting_No=(select TOP 1 Starting_No from OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id) 

                set @Starting_No=( select TOP 1 stuff(@Starting_No,1,0,REPLICATE(0,Padding_Len -len(@Starting_No) )) as StartingNo from OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id) 

                set @BDGREM_No= (select TOP 1 (Starting_Word+ convert(varchar(50),(@Starting_No))+convert(varchar(50),IsNull(Ending_Word,''))) as BDGEM_No from  OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            end



           --------------------------------------------- Validation End  -------------------------------------------------------

          If exists (select BDGREM_No from OxFund.BudgetRevisionEntry_Master where BDGREM_No=@BDGREM_No and Branch_Id=@Branch_Id and FiscalYear_Id=@FiscalYear_Id and AutoDesc=@AutoDescNo)

           Begin

            Set @Return_Id = 0

            Set @Result = 'Already Exists !'    

            COMMIT TRANSACTION

            return

          End



            set @BDGREM_Id=(Select CONVERT(BIGINT, CONVERT(VARCHAR(50),@Branch_Id) + CONVERT(VARCHAR(50),REPLICATE('0',3 -LEN(IsNull(MAX(@Branch_Id),@Branch_Id)))) + STUFF(IsNull(Right(MAX(BDGREM_Id),10),0)+1,1,0,REPLICATE('0',10 -LEN(IsNull(Right(MAX(BDGREM_Id),10),0)+1)))) from OxFund.BudgetRevisionEntry_Master Where Branch_Id=@Branch_Id)  

            If exists (Select BDGREM_No from  [OxFund].BudgetRevisionEntry_Master where BDGREM_Id=@BDGREM_Id )

            begin      

                set @BDGREM_Id=@BDGREM_Id+1     

            end 



            Insert Into OxFund.BudgetRevisionEntry_Master Values 

            (   

                @BDGREM_Id,

                @BDGREM_No,

                @BDGREM_Date,

                @BDGREM_Miti,

                @BDGEM_Id,

                @BDGEM_No,

                @BDGEM_Date,

                @BDGEM_Miti,        

                @Donor_Id,

                @Pr_Id,

                @Project_Name,

                @Project_No,

                @PeriodFrom_Date,

                @PeriodTo_Date,         

                @Cur_Id,

                @Cur_Rate,

                @Net_BudgetAmt,

                @Net_LocalBudgetAmt,

                @Created_By,

                @Created_Date,

                @Remarks,

                @Branch_Id,

                @FiscalYear_Id,

                @IP,

                @AutoDescNo

            )  

            Set @Return_Id = @BDGREM_Id --@@IDENTITY

            Set @Result = 'Record Inserted Successfully !'  



           --------------------------------------------- AC TRANSACTION END   -------------------------------------------------------



            If exists ( select TOP 1 Starting_No from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            begin   

                Set @Starting_No=(select TOP 1 Starting_No+1 from OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)                      

                Update OxFund.AutoGenerate set Starting_No=@Starting_No where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id

            END     



        End 



        If @Event = 'U'  or @Event ='I'   --- Update

        Begin

            --------------------------------------------- Validation Begin ------------------------------------------------------



           --------------------------------------------- Validation End  -------------------------------------------------------

            If @Event = 'U' 

            BEGIN

                Update OxFund.BudgetRevisionEntry_Master Set        

                [BDGREM_Date] = @BDGREM_Date,

                [BDGREM_Miti] = @BDGREM_Miti,

                [BDGEM_Id] = @BDGEM_Id,

                [BDGEM_No] = @BDGEM_No,

                [BDGEM_Date] = @BDGEM_Date,

                [BDGEM_Miti] = @BDGEM_Miti,

                [Donor_Id]=@Donor_Id,

                [PR_Id]=@Pr_Id,

                [Project_Name] = @Project_Name,     

                [Project_No]=@Project_No,       

                [PeriodFrom_Date] = @PeriodFrom_Date,

                [PeriodTo_Date] = @PeriodTo_Date,

                [Cur_Id] = @Cur_Id,

                [Cur_Rate] = @Cur_Rate, 

                [Net_BudgetAmt]=@Net_BudgetAmt,

                [Net_LocalBudgetAmt]=@Net_LocalBudgetAmt,

                [Created_By] = @Created_By,

                [Created_Date] = @Created_Date,

                [Remarks] = @Remarks,

                [Branch_Id] = @Branch_Id,

                [FiscalYear_Id] = @FiscalYear_Id,

                [IP]=@IP

                Where BDGREM_Id=@BDGREM_Id and BDGREM_No = @BDGREM_No

                Set @Result = 'Record Updated Successfully !'

                set @Return_Id=@BDGREM_Id

             END

             --------------------------------------------- BDGEM DETAILS BEGIN ------------------------------------------------------

             Declare @BDGRED_Id bigint

             DELETE FROM OxFund.BudgetRevisionEntry_Details WHERE BDGREM_Id=@BDGREM_Id and BDGREM_No = @BDGREM_No and Branch_Id=@Branch_Id

             set @BDGRED_Id=(Select CONVERT(BIGINT, CONVERT(VARCHAR(50),@Branch_Id) + CONVERT(VARCHAR(50),REPLICATE('0',3 -LEN(IsNull(MAX(@Branch_Id),@Branch_Id)))) + STUFF(IsNull(Right(MAX(BDGRED_Id),10),0)+1,1,0,REPLICATE('0',10 -LEN(IsNull(Right(MAX(BDGRED_Id),10),0)+1)))) from OxFund.BudgetRevisionEntry_Details  Where Branch_Id=@Branch_Id)   

             If exists (Select BDGRED_Id from  [OxFund].BudgetRevisionEntry_Details where BDGRED_Id=@BDGRED_Id )

             begin     

                set @BDGRED_Id=@BDGRED_Id+1     

             end

             INSERT INTO OxFund.BudgetRevisionEntry_Details

                    SELECT 

                    --ParamValues.ID.query('BDGRED_Id').value('.','bigint') As BDGRED_Id ,

                    BDGRED_Id=@BDGRED_Id + (ParamValues.ID.query('SNo').value('.','bigint')-1),   

                    BDGREM_Id=@Return_Id,

                    BDGREM_No=@BDGREM_No,

                    ParamValues.ID.query('SNo').value('.','bigint') As SNo ,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('PRA_Id').value('.', 'varchar(50)'),'')) AS PRA_Id,

                    ParamValues.ID.query('BH').value('.','VARCHAR(50)') As BH ,    

                    ParamValues.ID.query('Activity').value('.','VARCHAR(80)') As Activity ,   

                    ParamValues.ID.query('Gl_Code').value('.','VARCHAR(50)') As Gl_Code ,   

                    ParamValues.ID.query('C_Code').value('.','VARCHAR(50)') As C_Code , 

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('PRC_Id').value('.', 'varchar(50)'),'')) AS PRC_Id,  

                    ParamValues.ID.query('Contract').value('.','VARCHAR(50)') As Contract ,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('PRT_Id').value('.', 'varchar(50)'),'')) AS PRT_Id,   

                    ParamValues.ID.query('Theme').value('.','VARCHAR(50)') As Theme ,           

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id').value('.', 'varchar(50)'),'')) AS Place_Id,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id1').value('.', 'varchar(50)'),'')) AS Place_Id1,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id2').value('.', 'varchar(50)'),'')) AS Place_Id2,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id3').value('.', 'varchar(50)'),'')) AS Place_Id3,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id4').value('.', 'varchar(50)'),'')) AS Place_Id4,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id5').value('.', 'varchar(50)'),'')) AS Place_Id5,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id6').value('.', 'varchar(50)'),'')) AS Place_Id6,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id7').value('.', 'varchar(50)'),'')) AS Place_Id7,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id8').value('.', 'varchar(50)'),'')) AS Place_Id8,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id9').value('.', 'varchar(50)'),'')) AS Place_Id9,           

                    [Cur_Id] = @Cur_Id,

                    [Cur_Rate] = @Cur_Rate,     

                    ParamValues.ID.query('Budget_Amt').value('.','DECIMAL(18,4)') As Budget_Amt,          

                    ParamValues.ID.query('Local_BudgetAmt').value('.','DECIMAL(18,4)') As Local_BudgetAmt,

                    ParamValues.ID.query('Month1').value('.','DECIMAL(18,4)') As Month1, 

                    ParamValues.ID.query('Month2').value('.','DECIMAL(18,4)') As Month2, 

                    ParamValues.ID.query('Month3').value('.','DECIMAL(18,4)') As Month3, 

                    ParamValues.ID.query('Month4').value('.','DECIMAL(18,4)') As Month4, 

                    ParamValues.ID.query('Month5').value('.','DECIMAL(18,4)') As Month5, 

                    ParamValues.ID.query('Month6').value('.','DECIMAL(18,4)') As Month6, 

                    ParamValues.ID.query('Month7').value('.','DECIMAL(18,4)') As Month7, 

                    ParamValues.ID.query('Month8').value('.','DECIMAL(18,4)') As Month8, 

                    ParamValues.ID.query('Month9').value('.','DECIMAL(18,4)') As Month9, 

                    ParamValues.ID.query('Month10').value('.','DECIMAL(18,4)') As Month10, 

                    ParamValues.ID.query('Month11').value('.','DECIMAL(18,4)') As Month11, 

                    ParamValues.ID.query('Month12').value('.','DECIMAL(18,4)') As Month12, 

                    NULLIF(ParamValues.ID.query('Narration').value('.','VARCHAR(1024)'),'') As Narration,

                    @Branch_Id

                    FROM @xmlBudgetEntry.nodes('/DocumentElement/Temp') as ParamValues(ID)



           ---------------------------------------------  BDGEM DETAILS END   -------------------------------------------------------

           --------------------------------------------- Period BEGIN ------------------------------------------------------

             Delete from OxFund.BudgetRevisionEntry_PeriodDetails where BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No and Branch_Id=@Branch_Id



             Declare @BDGREPD_Id bigint  

             set @BDGREPD_Id=(Select CONVERT(BIGINT, CONVERT(VARCHAR(50),@Branch_Id) + CONVERT(VARCHAR(50),REPLICATE('0',3 -LEN(IsNull(MAX(@Branch_Id),@Branch_Id)))) + STUFF(IsNull(Right(MAX(BDGREPD_Id),10),0)+1,1,0,REPLICATE('0',10 -LEN(IsNull(Right(MAX(BDGREPD_Id),10),0)+1)))) from OxFund.BudgetRevisionEntry_PeriodDetails Where Branch_Id=@Branch_Id)   

             If exists (Select BDGREPD_Id from  [OxFund].BudgetRevisionEntry_PeriodDetails where BDGREPD_Id=@BDGREPD_Id )

             begin     

                set @BDGREPD_Id=@BDGREPD_Id+1       

             end

             INSERT INTO OxFund.BudgetRevisionEntry_PeriodDetails

                    SELECT 

                    ParamValues.ID.query('BDGREPD_Id').value('.','bigint') As BDGREPD_Id ,

                    --BDGREPD_Id=@BDGREPD_Id + (ParamValues.ID.query('SNo').value('.','bigint')-1), 

                    BDGREM_Id=@Return_Id,

                    BDGREM_No=@BDGREM_No,

                    BDGRED_Id=NULL,--@Return_Id,----Detail Id

                    ParamValues.ID.query('DSNo').value('.','bigint') As DSNo ,

                    ParamValues.ID.query('SNo').value('.','bigint') As SNo ,            

                    ParamValues.ID.query('Month_AName').value('.','VARCHAR(50)') As Month_AName ,

                    ParamValues.ID.query('Month_MName').value('.','VARCHAR(50)') As Month_MName ,

                    Cur_Id=@Cur_Id,

                    Cur_Rate=@Cur_Rate,             

                    ParamValues.ID.query('Month_Amt').value('.','DECIMAL(18,4)') As Month_Amt,           

                    ParamValues.ID.query('Local_MonthAmt').value('.','DECIMAL(18,4)') As Local_MonthAmt,

                    NULLIF(ParamValues.ID.query('Narration').value('.','VARCHAR(1024)'),'') As Narration,   

                    NULLIF(ParamValues.ID.query('Month_Date').value('.','DateTime'),'') As Month_Date,

                    @Branch_Id  

                    FROM @xmlBudgetEntryPeriod.nodes('/DocumentElement/Temp') as ParamValues(ID)



           --------------------------------------------- AC TRAN END   -------------------------------------------------------

        End 



        If @Event = 'D'   -- For Delete 

        Begin 



        --------------------------------------------- Validation Begin ------------------------------------------------------

            if exists ( select TOP 1 Starting_No from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            begin   

                IF( CONVERT(bigint,substring(@BDGREM_No,(select TOP 1 len(Starting_Word)+1 from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id),(select TOP 1 Padding_Len from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)))=(select TOP 1 Starting_No-1 from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id))                        

                update OxFund.AutoGenerate set Starting_No=Starting_No-1 where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id

            END 

        --------------------------------------------- Validation End  -------------------------------------------------------

            DELETE FROM OxFund.BudgetRevisionEntry_PeriodDetails WHERE BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No

            DELETE FROM OxFund.BudgetRevisionEntry_Details WHERE BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No 

            Delete from OxFund.BudgetRevisionEntry_Master Where BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No 

            Set @Result = 'Record Deleted Successfully !'   

            Set @Return_Id = @BDGREM_Id

        End 



    END

    COMMIT TRANSACTION

END TRY

    BEGIN CATCH

    set @Result=(select ERROR_MESSAGE() AS ErrorMessage)

    ROLLBACK

    END CATCH

Here i'm trying to do insert operation for budget revision task. Here for first time everything works properly, but next time when i again try to insert the budget revision voucher this kind of error is coming.

sql asp.net stored-procedures transactions

share|improve this question

asked Nov 21 '18 at 6:07

Dinesh LamaDinesh Lama

975

add a comment | 

0

0

0

Although this type of questions are already present in this site, since i don't find them useful in solving my problem, i'm posting my question here, my stored procedure is:

ALTER Proc [OxFund].[Usp_IUD_BudgetRevisionEntry]

(

    @Event char(2) = 'I', 

    @BDGREM_Id bigint = 0, 

    @BDGREM_No varchar(50)=Null,

    @BDGREM_Date datetime=Null,

    @BDGREM_Miti varchar(10)=Null,

    @BDGEM_Id bigint = 0, 

    @BDGEM_No varchar(50)=Null,

    @BDGEM_Date datetime=Null,

    @BDGEM_Miti varchar(10)=Null,

    @Donor_Id bigint=Null,

    @PR_Id bigint=Null,

    @Project_Name varchar(80)=Null,

    @Project_No varchar(50)=Null,

    @PeriodFrom_Date datetime=Null,

    @PeriodTo_Date datetime=Null,   

    @Cur_Id bigint=Null,

    @Cur_Rate decimal(18,4)=Null,

    @Net_BudgetAmt decimal(18,6)=Null,

    @Net_LocalBudgetAmt  decimal(18,6)=Null,

    @Remarks varchar(1024)=Null,

    @Created_By bigint=Null,

    @Created_Date datetime=Null,

    @Station varchar(5)=Null,

    @Branch_Id bigint=Null,

    @FiscalYear_Id bigint=Null,

    @AutoDescNo Varchar(75)=Null,

    @XmlBudgetEntryDetl varchar(max)=Null,

    @XmlBudgetEntryPeriodDetl varchar(max)=Null,

    @IP varchar(256)=Null,

    @Result Varchar(max) output ,

    @Return_Id bigint output

)

As

IF @BDGEM_Id=0

SET @BDGEM_Id=NULL

IF @Pr_Id=0

SET @Pr_Id=NULL

IF @Cur_Id=0

SET @Cur_Id=NULL

IF @PeriodFrom_Date='1753/01/01'

set @PeriodFrom_Date=NULL

IF @PeriodTo_Date='1753/01/01'

set @PeriodTo_Date=NULL

IF @Created_Date='1753/01/01'

set @Created_Date=NULL



Declare @xmlBudgetEntry xml 

        set   @xmlBudgetEntry  =Convert(xml,@XmlBudgetEntryDetl)    

        Set XAct_Abort On



 Declare @xmlBudgetEntryPeriod xml 

        set   @xmlBudgetEntryPeriod  =Convert(xml,@XmlBudgetEntryPeriodDetl)    

        Set XAct_Abort On 





BEGIN TRY

    BEGIN TRANSACTION     

    begin



        If @Event = 'I'  --for Insert

        Begin 

            --------------------------------------------- Validation Begin ------------------------------------------------------



            declare @Starting_No varchar(50)

            if exists ( select TOP 1 Starting_No from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            begin               

                set @Starting_No=(select TOP 1 Starting_No from OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id) 

                set @Starting_No=( select TOP 1 stuff(@Starting_No,1,0,REPLICATE(0,Padding_Len -len(@Starting_No) )) as StartingNo from OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id) 

                set @BDGREM_No= (select TOP 1 (Starting_Word+ convert(varchar(50),(@Starting_No))+convert(varchar(50),IsNull(Ending_Word,''))) as BDGEM_No from  OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            end



           --------------------------------------------- Validation End  -------------------------------------------------------

          If exists (select BDGREM_No from OxFund.BudgetRevisionEntry_Master where BDGREM_No=@BDGREM_No and Branch_Id=@Branch_Id and FiscalYear_Id=@FiscalYear_Id and AutoDesc=@AutoDescNo)

           Begin

            Set @Return_Id = 0

            Set @Result = 'Already Exists !'    

            COMMIT TRANSACTION

            return

          End



            set @BDGREM_Id=(Select CONVERT(BIGINT, CONVERT(VARCHAR(50),@Branch_Id) + CONVERT(VARCHAR(50),REPLICATE('0',3 -LEN(IsNull(MAX(@Branch_Id),@Branch_Id)))) + STUFF(IsNull(Right(MAX(BDGREM_Id),10),0)+1,1,0,REPLICATE('0',10 -LEN(IsNull(Right(MAX(BDGREM_Id),10),0)+1)))) from OxFund.BudgetRevisionEntry_Master Where Branch_Id=@Branch_Id)  

            If exists (Select BDGREM_No from  [OxFund].BudgetRevisionEntry_Master where BDGREM_Id=@BDGREM_Id )

            begin      

                set @BDGREM_Id=@BDGREM_Id+1     

            end 



            Insert Into OxFund.BudgetRevisionEntry_Master Values 

            (   

                @BDGREM_Id,

                @BDGREM_No,

                @BDGREM_Date,

                @BDGREM_Miti,

                @BDGEM_Id,

                @BDGEM_No,

                @BDGEM_Date,

                @BDGEM_Miti,        

                @Donor_Id,

                @Pr_Id,

                @Project_Name,

                @Project_No,

                @PeriodFrom_Date,

                @PeriodTo_Date,         

                @Cur_Id,

                @Cur_Rate,

                @Net_BudgetAmt,

                @Net_LocalBudgetAmt,

                @Created_By,

                @Created_Date,

                @Remarks,

                @Branch_Id,

                @FiscalYear_Id,

                @IP,

                @AutoDescNo

            )  

            Set @Return_Id = @BDGREM_Id --@@IDENTITY

            Set @Result = 'Record Inserted Successfully !'  



           --------------------------------------------- AC TRANSACTION END   -------------------------------------------------------



            If exists ( select TOP 1 Starting_No from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            begin   

                Set @Starting_No=(select TOP 1 Starting_No+1 from OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)                      

                Update OxFund.AutoGenerate set Starting_No=@Starting_No where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id

            END     



        End 



        If @Event = 'U'  or @Event ='I'   --- Update

        Begin

            --------------------------------------------- Validation Begin ------------------------------------------------------



           --------------------------------------------- Validation End  -------------------------------------------------------

            If @Event = 'U' 

            BEGIN

                Update OxFund.BudgetRevisionEntry_Master Set        

                [BDGREM_Date] = @BDGREM_Date,

                [BDGREM_Miti] = @BDGREM_Miti,

                [BDGEM_Id] = @BDGEM_Id,

                [BDGEM_No] = @BDGEM_No,

                [BDGEM_Date] = @BDGEM_Date,

                [BDGEM_Miti] = @BDGEM_Miti,

                [Donor_Id]=@Donor_Id,

                [PR_Id]=@Pr_Id,

                [Project_Name] = @Project_Name,     

                [Project_No]=@Project_No,       

                [PeriodFrom_Date] = @PeriodFrom_Date,

                [PeriodTo_Date] = @PeriodTo_Date,

                [Cur_Id] = @Cur_Id,

                [Cur_Rate] = @Cur_Rate, 

                [Net_BudgetAmt]=@Net_BudgetAmt,

                [Net_LocalBudgetAmt]=@Net_LocalBudgetAmt,

                [Created_By] = @Created_By,

                [Created_Date] = @Created_Date,

                [Remarks] = @Remarks,

                [Branch_Id] = @Branch_Id,

                [FiscalYear_Id] = @FiscalYear_Id,

                [IP]=@IP

                Where BDGREM_Id=@BDGREM_Id and BDGREM_No = @BDGREM_No

                Set @Result = 'Record Updated Successfully !'

                set @Return_Id=@BDGREM_Id

             END

             --------------------------------------------- BDGEM DETAILS BEGIN ------------------------------------------------------

             Declare @BDGRED_Id bigint

             DELETE FROM OxFund.BudgetRevisionEntry_Details WHERE BDGREM_Id=@BDGREM_Id and BDGREM_No = @BDGREM_No and Branch_Id=@Branch_Id

             set @BDGRED_Id=(Select CONVERT(BIGINT, CONVERT(VARCHAR(50),@Branch_Id) + CONVERT(VARCHAR(50),REPLICATE('0',3 -LEN(IsNull(MAX(@Branch_Id),@Branch_Id)))) + STUFF(IsNull(Right(MAX(BDGRED_Id),10),0)+1,1,0,REPLICATE('0',10 -LEN(IsNull(Right(MAX(BDGRED_Id),10),0)+1)))) from OxFund.BudgetRevisionEntry_Details  Where Branch_Id=@Branch_Id)   

             If exists (Select BDGRED_Id from  [OxFund].BudgetRevisionEntry_Details where BDGRED_Id=@BDGRED_Id )

             begin     

                set @BDGRED_Id=@BDGRED_Id+1     

             end

             INSERT INTO OxFund.BudgetRevisionEntry_Details

                    SELECT 

                    --ParamValues.ID.query('BDGRED_Id').value('.','bigint') As BDGRED_Id ,

                    BDGRED_Id=@BDGRED_Id + (ParamValues.ID.query('SNo').value('.','bigint')-1),   

                    BDGREM_Id=@Return_Id,

                    BDGREM_No=@BDGREM_No,

                    ParamValues.ID.query('SNo').value('.','bigint') As SNo ,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('PRA_Id').value('.', 'varchar(50)'),'')) AS PRA_Id,

                    ParamValues.ID.query('BH').value('.','VARCHAR(50)') As BH ,    

                    ParamValues.ID.query('Activity').value('.','VARCHAR(80)') As Activity ,   

                    ParamValues.ID.query('Gl_Code').value('.','VARCHAR(50)') As Gl_Code ,   

                    ParamValues.ID.query('C_Code').value('.','VARCHAR(50)') As C_Code , 

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('PRC_Id').value('.', 'varchar(50)'),'')) AS PRC_Id,  

                    ParamValues.ID.query('Contract').value('.','VARCHAR(50)') As Contract ,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('PRT_Id').value('.', 'varchar(50)'),'')) AS PRT_Id,   

                    ParamValues.ID.query('Theme').value('.','VARCHAR(50)') As Theme ,           

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id').value('.', 'varchar(50)'),'')) AS Place_Id,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id1').value('.', 'varchar(50)'),'')) AS Place_Id1,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id2').value('.', 'varchar(50)'),'')) AS Place_Id2,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id3').value('.', 'varchar(50)'),'')) AS Place_Id3,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id4').value('.', 'varchar(50)'),'')) AS Place_Id4,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id5').value('.', 'varchar(50)'),'')) AS Place_Id5,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id6').value('.', 'varchar(50)'),'')) AS Place_Id6,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id7').value('.', 'varchar(50)'),'')) AS Place_Id7,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id8').value('.', 'varchar(50)'),'')) AS Place_Id8,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id9').value('.', 'varchar(50)'),'')) AS Place_Id9,           

                    [Cur_Id] = @Cur_Id,

                    [Cur_Rate] = @Cur_Rate,     

                    ParamValues.ID.query('Budget_Amt').value('.','DECIMAL(18,4)') As Budget_Amt,          

                    ParamValues.ID.query('Local_BudgetAmt').value('.','DECIMAL(18,4)') As Local_BudgetAmt,

                    ParamValues.ID.query('Month1').value('.','DECIMAL(18,4)') As Month1, 

                    ParamValues.ID.query('Month2').value('.','DECIMAL(18,4)') As Month2, 

                    ParamValues.ID.query('Month3').value('.','DECIMAL(18,4)') As Month3, 

                    ParamValues.ID.query('Month4').value('.','DECIMAL(18,4)') As Month4, 

                    ParamValues.ID.query('Month5').value('.','DECIMAL(18,4)') As Month5, 

                    ParamValues.ID.query('Month6').value('.','DECIMAL(18,4)') As Month6, 

                    ParamValues.ID.query('Month7').value('.','DECIMAL(18,4)') As Month7, 

                    ParamValues.ID.query('Month8').value('.','DECIMAL(18,4)') As Month8, 

                    ParamValues.ID.query('Month9').value('.','DECIMAL(18,4)') As Month9, 

                    ParamValues.ID.query('Month10').value('.','DECIMAL(18,4)') As Month10, 

                    ParamValues.ID.query('Month11').value('.','DECIMAL(18,4)') As Month11, 

                    ParamValues.ID.query('Month12').value('.','DECIMAL(18,4)') As Month12, 

                    NULLIF(ParamValues.ID.query('Narration').value('.','VARCHAR(1024)'),'') As Narration,

                    @Branch_Id

                    FROM @xmlBudgetEntry.nodes('/DocumentElement/Temp') as ParamValues(ID)



           ---------------------------------------------  BDGEM DETAILS END   -------------------------------------------------------

           --------------------------------------------- Period BEGIN ------------------------------------------------------

             Delete from OxFund.BudgetRevisionEntry_PeriodDetails where BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No and Branch_Id=@Branch_Id



             Declare @BDGREPD_Id bigint  

             set @BDGREPD_Id=(Select CONVERT(BIGINT, CONVERT(VARCHAR(50),@Branch_Id) + CONVERT(VARCHAR(50),REPLICATE('0',3 -LEN(IsNull(MAX(@Branch_Id),@Branch_Id)))) + STUFF(IsNull(Right(MAX(BDGREPD_Id),10),0)+1,1,0,REPLICATE('0',10 -LEN(IsNull(Right(MAX(BDGREPD_Id),10),0)+1)))) from OxFund.BudgetRevisionEntry_PeriodDetails Where Branch_Id=@Branch_Id)   

             If exists (Select BDGREPD_Id from  [OxFund].BudgetRevisionEntry_PeriodDetails where BDGREPD_Id=@BDGREPD_Id )

             begin     

                set @BDGREPD_Id=@BDGREPD_Id+1       

             end

             INSERT INTO OxFund.BudgetRevisionEntry_PeriodDetails

                    SELECT 

                    ParamValues.ID.query('BDGREPD_Id').value('.','bigint') As BDGREPD_Id ,

                    --BDGREPD_Id=@BDGREPD_Id + (ParamValues.ID.query('SNo').value('.','bigint')-1), 

                    BDGREM_Id=@Return_Id,

                    BDGREM_No=@BDGREM_No,

                    BDGRED_Id=NULL,--@Return_Id,----Detail Id

                    ParamValues.ID.query('DSNo').value('.','bigint') As DSNo ,

                    ParamValues.ID.query('SNo').value('.','bigint') As SNo ,            

                    ParamValues.ID.query('Month_AName').value('.','VARCHAR(50)') As Month_AName ,

                    ParamValues.ID.query('Month_MName').value('.','VARCHAR(50)') As Month_MName ,

                    Cur_Id=@Cur_Id,

                    Cur_Rate=@Cur_Rate,             

                    ParamValues.ID.query('Month_Amt').value('.','DECIMAL(18,4)') As Month_Amt,           

                    ParamValues.ID.query('Local_MonthAmt').value('.','DECIMAL(18,4)') As Local_MonthAmt,

                    NULLIF(ParamValues.ID.query('Narration').value('.','VARCHAR(1024)'),'') As Narration,   

                    NULLIF(ParamValues.ID.query('Month_Date').value('.','DateTime'),'') As Month_Date,

                    @Branch_Id  

                    FROM @xmlBudgetEntryPeriod.nodes('/DocumentElement/Temp') as ParamValues(ID)



           --------------------------------------------- AC TRAN END   -------------------------------------------------------

        End 



        If @Event = 'D'   -- For Delete 

        Begin 



        --------------------------------------------- Validation Begin ------------------------------------------------------

            if exists ( select TOP 1 Starting_No from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            begin   

                IF( CONVERT(bigint,substring(@BDGREM_No,(select TOP 1 len(Starting_Word)+1 from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id),(select TOP 1 Padding_Len from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)))=(select TOP 1 Starting_No-1 from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id))                        

                update OxFund.AutoGenerate set Starting_No=Starting_No-1 where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id

            END 

        --------------------------------------------- Validation End  -------------------------------------------------------

            DELETE FROM OxFund.BudgetRevisionEntry_PeriodDetails WHERE BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No

            DELETE FROM OxFund.BudgetRevisionEntry_Details WHERE BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No 

            Delete from OxFund.BudgetRevisionEntry_Master Where BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No 

            Set @Result = 'Record Deleted Successfully !'   

            Set @Return_Id = @BDGREM_Id

        End 



    END

    COMMIT TRANSACTION

END TRY

    BEGIN CATCH

    set @Result=(select ERROR_MESSAGE() AS ErrorMessage)

    ROLLBACK

    END CATCH

Here i'm trying to do insert operation for budget revision task. Here for first time everything works properly, but next time when i again try to insert the budget revision voucher this kind of error is coming.

sql asp.net stored-procedures transactions

share|improve this question

asked Nov 21 '18 at 6:07

Dinesh LamaDinesh Lama

975

Although this type of questions are already present in this site, since i don't find them useful in solving my problem, i'm posting my question here, my stored procedure is:

ALTER Proc [OxFund].[Usp_IUD_BudgetRevisionEntry]

(

    @Event char(2) = 'I', 

    @BDGREM_Id bigint = 0, 

    @BDGREM_No varchar(50)=Null,

    @BDGREM_Date datetime=Null,

    @BDGREM_Miti varchar(10)=Null,

    @BDGEM_Id bigint = 0, 

    @BDGEM_No varchar(50)=Null,

    @BDGEM_Date datetime=Null,

    @BDGEM_Miti varchar(10)=Null,

    @Donor_Id bigint=Null,

    @PR_Id bigint=Null,

    @Project_Name varchar(80)=Null,

    @Project_No varchar(50)=Null,

    @PeriodFrom_Date datetime=Null,

    @PeriodTo_Date datetime=Null,   

    @Cur_Id bigint=Null,

    @Cur_Rate decimal(18,4)=Null,

    @Net_BudgetAmt decimal(18,6)=Null,

    @Net_LocalBudgetAmt  decimal(18,6)=Null,

    @Remarks varchar(1024)=Null,

    @Created_By bigint=Null,

    @Created_Date datetime=Null,

    @Station varchar(5)=Null,

    @Branch_Id bigint=Null,

    @FiscalYear_Id bigint=Null,

    @AutoDescNo Varchar(75)=Null,

    @XmlBudgetEntryDetl varchar(max)=Null,

    @XmlBudgetEntryPeriodDetl varchar(max)=Null,

    @IP varchar(256)=Null,

    @Result Varchar(max) output ,

    @Return_Id bigint output

)

As

IF @BDGEM_Id=0

SET @BDGEM_Id=NULL

IF @Pr_Id=0

SET @Pr_Id=NULL

IF @Cur_Id=0

SET @Cur_Id=NULL

IF @PeriodFrom_Date='1753/01/01'

set @PeriodFrom_Date=NULL

IF @PeriodTo_Date='1753/01/01'

set @PeriodTo_Date=NULL

IF @Created_Date='1753/01/01'

set @Created_Date=NULL



Declare @xmlBudgetEntry xml 

        set   @xmlBudgetEntry  =Convert(xml,@XmlBudgetEntryDetl)    

        Set XAct_Abort On



 Declare @xmlBudgetEntryPeriod xml 

        set   @xmlBudgetEntryPeriod  =Convert(xml,@XmlBudgetEntryPeriodDetl)    

        Set XAct_Abort On 





BEGIN TRY

    BEGIN TRANSACTION     

    begin



        If @Event = 'I'  --for Insert

        Begin 

            --------------------------------------------- Validation Begin ------------------------------------------------------



            declare @Starting_No varchar(50)

            if exists ( select TOP 1 Starting_No from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            begin               

                set @Starting_No=(select TOP 1 Starting_No from OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id) 

                set @Starting_No=( select TOP 1 stuff(@Starting_No,1,0,REPLICATE(0,Padding_Len -len(@Starting_No) )) as StartingNo from OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id) 

                set @BDGREM_No= (select TOP 1 (Starting_Word+ convert(varchar(50),(@Starting_No))+convert(varchar(50),IsNull(Ending_Word,''))) as BDGEM_No from  OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            end



           --------------------------------------------- Validation End  -------------------------------------------------------

          If exists (select BDGREM_No from OxFund.BudgetRevisionEntry_Master where BDGREM_No=@BDGREM_No and Branch_Id=@Branch_Id and FiscalYear_Id=@FiscalYear_Id and AutoDesc=@AutoDescNo)

           Begin

            Set @Return_Id = 0

            Set @Result = 'Already Exists !'    

            COMMIT TRANSACTION

            return

          End



            set @BDGREM_Id=(Select CONVERT(BIGINT, CONVERT(VARCHAR(50),@Branch_Id) + CONVERT(VARCHAR(50),REPLICATE('0',3 -LEN(IsNull(MAX(@Branch_Id),@Branch_Id)))) + STUFF(IsNull(Right(MAX(BDGREM_Id),10),0)+1,1,0,REPLICATE('0',10 -LEN(IsNull(Right(MAX(BDGREM_Id),10),0)+1)))) from OxFund.BudgetRevisionEntry_Master Where Branch_Id=@Branch_Id)  

            If exists (Select BDGREM_No from  [OxFund].BudgetRevisionEntry_Master where BDGREM_Id=@BDGREM_Id )

            begin      

                set @BDGREM_Id=@BDGREM_Id+1     

            end 



            Insert Into OxFund.BudgetRevisionEntry_Master Values 

            (   

                @BDGREM_Id,

                @BDGREM_No,

                @BDGREM_Date,

                @BDGREM_Miti,

                @BDGEM_Id,

                @BDGEM_No,

                @BDGEM_Date,

                @BDGEM_Miti,        

                @Donor_Id,

                @Pr_Id,

                @Project_Name,

                @Project_No,

                @PeriodFrom_Date,

                @PeriodTo_Date,         

                @Cur_Id,

                @Cur_Rate,

                @Net_BudgetAmt,

                @Net_LocalBudgetAmt,

                @Created_By,

                @Created_Date,

                @Remarks,

                @Branch_Id,

                @FiscalYear_Id,

                @IP,

                @AutoDescNo

            )  

            Set @Return_Id = @BDGREM_Id --@@IDENTITY

            Set @Result = 'Record Inserted Successfully !'  



           --------------------------------------------- AC TRANSACTION END   -------------------------------------------------------



            If exists ( select TOP 1 Starting_No from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            begin   

                Set @Starting_No=(select TOP 1 Starting_No+1 from OxFund.AutoGenerate where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)                      

                Update OxFund.AutoGenerate set Starting_No=@Starting_No where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id

            END     



        End 



        If @Event = 'U'  or @Event ='I'   --- Update

        Begin

            --------------------------------------------- Validation Begin ------------------------------------------------------



           --------------------------------------------- Validation End  -------------------------------------------------------

            If @Event = 'U' 

            BEGIN

                Update OxFund.BudgetRevisionEntry_Master Set        

                [BDGREM_Date] = @BDGREM_Date,

                [BDGREM_Miti] = @BDGREM_Miti,

                [BDGEM_Id] = @BDGEM_Id,

                [BDGEM_No] = @BDGEM_No,

                [BDGEM_Date] = @BDGEM_Date,

                [BDGEM_Miti] = @BDGEM_Miti,

                [Donor_Id]=@Donor_Id,

                [PR_Id]=@Pr_Id,

                [Project_Name] = @Project_Name,     

                [Project_No]=@Project_No,       

                [PeriodFrom_Date] = @PeriodFrom_Date,

                [PeriodTo_Date] = @PeriodTo_Date,

                [Cur_Id] = @Cur_Id,

                [Cur_Rate] = @Cur_Rate, 

                [Net_BudgetAmt]=@Net_BudgetAmt,

                [Net_LocalBudgetAmt]=@Net_LocalBudgetAmt,

                [Created_By] = @Created_By,

                [Created_Date] = @Created_Date,

                [Remarks] = @Remarks,

                [Branch_Id] = @Branch_Id,

                [FiscalYear_Id] = @FiscalYear_Id,

                [IP]=@IP

                Where BDGREM_Id=@BDGREM_Id and BDGREM_No = @BDGREM_No

                Set @Result = 'Record Updated Successfully !'

                set @Return_Id=@BDGREM_Id

             END

             --------------------------------------------- BDGEM DETAILS BEGIN ------------------------------------------------------

             Declare @BDGRED_Id bigint

             DELETE FROM OxFund.BudgetRevisionEntry_Details WHERE BDGREM_Id=@BDGREM_Id and BDGREM_No = @BDGREM_No and Branch_Id=@Branch_Id

             set @BDGRED_Id=(Select CONVERT(BIGINT, CONVERT(VARCHAR(50),@Branch_Id) + CONVERT(VARCHAR(50),REPLICATE('0',3 -LEN(IsNull(MAX(@Branch_Id),@Branch_Id)))) + STUFF(IsNull(Right(MAX(BDGRED_Id),10),0)+1,1,0,REPLICATE('0',10 -LEN(IsNull(Right(MAX(BDGRED_Id),10),0)+1)))) from OxFund.BudgetRevisionEntry_Details  Where Branch_Id=@Branch_Id)   

             If exists (Select BDGRED_Id from  [OxFund].BudgetRevisionEntry_Details where BDGRED_Id=@BDGRED_Id )

             begin     

                set @BDGRED_Id=@BDGRED_Id+1     

             end

             INSERT INTO OxFund.BudgetRevisionEntry_Details

                    SELECT 

                    --ParamValues.ID.query('BDGRED_Id').value('.','bigint') As BDGRED_Id ,

                    BDGRED_Id=@BDGRED_Id + (ParamValues.ID.query('SNo').value('.','bigint')-1),   

                    BDGREM_Id=@Return_Id,

                    BDGREM_No=@BDGREM_No,

                    ParamValues.ID.query('SNo').value('.','bigint') As SNo ,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('PRA_Id').value('.', 'varchar(50)'),'')) AS PRA_Id,

                    ParamValues.ID.query('BH').value('.','VARCHAR(50)') As BH ,    

                    ParamValues.ID.query('Activity').value('.','VARCHAR(80)') As Activity ,   

                    ParamValues.ID.query('Gl_Code').value('.','VARCHAR(50)') As Gl_Code ,   

                    ParamValues.ID.query('C_Code').value('.','VARCHAR(50)') As C_Code , 

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('PRC_Id').value('.', 'varchar(50)'),'')) AS PRC_Id,  

                    ParamValues.ID.query('Contract').value('.','VARCHAR(50)') As Contract ,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('PRT_Id').value('.', 'varchar(50)'),'')) AS PRT_Id,   

                    ParamValues.ID.query('Theme').value('.','VARCHAR(50)') As Theme ,           

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id').value('.', 'varchar(50)'),'')) AS Place_Id,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id1').value('.', 'varchar(50)'),'')) AS Place_Id1,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id2').value('.', 'varchar(50)'),'')) AS Place_Id2,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id3').value('.', 'varchar(50)'),'')) AS Place_Id3,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id4').value('.', 'varchar(50)'),'')) AS Place_Id4,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id5').value('.', 'varchar(50)'),'')) AS Place_Id5,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id6').value('.', 'varchar(50)'),'')) AS Place_Id6,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id7').value('.', 'varchar(50)'),'')) AS Place_Id7,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id8').value('.', 'varchar(50)'),'')) AS Place_Id8,

                    CONVERT(BIGINT,NULLIF(ParamValues.ID.query('Place_Id9').value('.', 'varchar(50)'),'')) AS Place_Id9,           

                    [Cur_Id] = @Cur_Id,

                    [Cur_Rate] = @Cur_Rate,     

                    ParamValues.ID.query('Budget_Amt').value('.','DECIMAL(18,4)') As Budget_Amt,          

                    ParamValues.ID.query('Local_BudgetAmt').value('.','DECIMAL(18,4)') As Local_BudgetAmt,

                    ParamValues.ID.query('Month1').value('.','DECIMAL(18,4)') As Month1, 

                    ParamValues.ID.query('Month2').value('.','DECIMAL(18,4)') As Month2, 

                    ParamValues.ID.query('Month3').value('.','DECIMAL(18,4)') As Month3, 

                    ParamValues.ID.query('Month4').value('.','DECIMAL(18,4)') As Month4, 

                    ParamValues.ID.query('Month5').value('.','DECIMAL(18,4)') As Month5, 

                    ParamValues.ID.query('Month6').value('.','DECIMAL(18,4)') As Month6, 

                    ParamValues.ID.query('Month7').value('.','DECIMAL(18,4)') As Month7, 

                    ParamValues.ID.query('Month8').value('.','DECIMAL(18,4)') As Month8, 

                    ParamValues.ID.query('Month9').value('.','DECIMAL(18,4)') As Month9, 

                    ParamValues.ID.query('Month10').value('.','DECIMAL(18,4)') As Month10, 

                    ParamValues.ID.query('Month11').value('.','DECIMAL(18,4)') As Month11, 

                    ParamValues.ID.query('Month12').value('.','DECIMAL(18,4)') As Month12, 

                    NULLIF(ParamValues.ID.query('Narration').value('.','VARCHAR(1024)'),'') As Narration,

                    @Branch_Id

                    FROM @xmlBudgetEntry.nodes('/DocumentElement/Temp') as ParamValues(ID)



           ---------------------------------------------  BDGEM DETAILS END   -------------------------------------------------------

           --------------------------------------------- Period BEGIN ------------------------------------------------------

             Delete from OxFund.BudgetRevisionEntry_PeriodDetails where BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No and Branch_Id=@Branch_Id



             Declare @BDGREPD_Id bigint  

             set @BDGREPD_Id=(Select CONVERT(BIGINT, CONVERT(VARCHAR(50),@Branch_Id) + CONVERT(VARCHAR(50),REPLICATE('0',3 -LEN(IsNull(MAX(@Branch_Id),@Branch_Id)))) + STUFF(IsNull(Right(MAX(BDGREPD_Id),10),0)+1,1,0,REPLICATE('0',10 -LEN(IsNull(Right(MAX(BDGREPD_Id),10),0)+1)))) from OxFund.BudgetRevisionEntry_PeriodDetails Where Branch_Id=@Branch_Id)   

             If exists (Select BDGREPD_Id from  [OxFund].BudgetRevisionEntry_PeriodDetails where BDGREPD_Id=@BDGREPD_Id )

             begin     

                set @BDGREPD_Id=@BDGREPD_Id+1       

             end

             INSERT INTO OxFund.BudgetRevisionEntry_PeriodDetails

                    SELECT 

                    ParamValues.ID.query('BDGREPD_Id').value('.','bigint') As BDGREPD_Id ,

                    --BDGREPD_Id=@BDGREPD_Id + (ParamValues.ID.query('SNo').value('.','bigint')-1), 

                    BDGREM_Id=@Return_Id,

                    BDGREM_No=@BDGREM_No,

                    BDGRED_Id=NULL,--@Return_Id,----Detail Id

                    ParamValues.ID.query('DSNo').value('.','bigint') As DSNo ,

                    ParamValues.ID.query('SNo').value('.','bigint') As SNo ,            

                    ParamValues.ID.query('Month_AName').value('.','VARCHAR(50)') As Month_AName ,

                    ParamValues.ID.query('Month_MName').value('.','VARCHAR(50)') As Month_MName ,

                    Cur_Id=@Cur_Id,

                    Cur_Rate=@Cur_Rate,             

                    ParamValues.ID.query('Month_Amt').value('.','DECIMAL(18,4)') As Month_Amt,           

                    ParamValues.ID.query('Local_MonthAmt').value('.','DECIMAL(18,4)') As Local_MonthAmt,

                    NULLIF(ParamValues.ID.query('Narration').value('.','VARCHAR(1024)'),'') As Narration,   

                    NULLIF(ParamValues.ID.query('Month_Date').value('.','DateTime'),'') As Month_Date,

                    @Branch_Id  

                    FROM @xmlBudgetEntryPeriod.nodes('/DocumentElement/Temp') as ParamValues(ID)



           --------------------------------------------- AC TRAN END   -------------------------------------------------------

        End 



        If @Event = 'D'   -- For Delete 

        Begin 



        --------------------------------------------- Validation Begin ------------------------------------------------------

            if exists ( select TOP 1 Starting_No from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)

            begin   

                IF( CONVERT(bigint,substring(@BDGREM_No,(select TOP 1 len(Starting_Word)+1 from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id),(select TOP 1 Padding_Len from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id)))=(select TOP 1 Starting_No-1 from  OxFund.AutoGenerate WHERE Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id))                        

                update OxFund.AutoGenerate set Starting_No=Starting_No-1 where Status=1 AND Terminal_Type=@Station and descriptions=@AutoDescNo and Branch_Id=@Branch_Id

            END 

        --------------------------------------------- Validation End  -------------------------------------------------------

            DELETE FROM OxFund.BudgetRevisionEntry_PeriodDetails WHERE BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No

            DELETE FROM OxFund.BudgetRevisionEntry_Details WHERE BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No 

            Delete from OxFund.BudgetRevisionEntry_Master Where BDGREM_Id=@BDGREM_Id and BDGREM_No=@BDGREM_No 

            Set @Result = 'Record Deleted Successfully !'   

            Set @Return_Id = @BDGREM_Id

        End 



    END

    COMMIT TRANSACTION

END TRY

    BEGIN CATCH

    set @Result=(select ERROR_MESSAGE() AS ErrorMessage)

    ROLLBACK

    END CATCH

Here i'm trying to do insert operation for budget revision task. Here for first time everything works properly, but next time when i again try to insert the budget revision voucher this kind of error is coming.

sql asp.net stored-procedures transactions

sql asp.net stored-procedures transactions

share|improve this question

asked Nov 21 '18 at 6:07

Dinesh LamaDinesh Lama

975

share|improve this question

asked Nov 21 '18 at 6:07

Dinesh LamaDinesh Lama

975

share|improve this question

share|improve this question

asked Nov 21 '18 at 6:07

Dinesh LamaDinesh Lama

975

asked Nov 21 '18 at 6:07

Dinesh LamaDinesh Lama

975

asked Nov 21 '18 at 6:07

Dinesh LamaDinesh Lama

975

975

add a comment | 

add a comment | 

0

active

oldest

votes

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53406150%2ftransaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-comm%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

active

oldest

votes

0

active

oldest

votes

active

oldest

votes

active

oldest

votes

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53406150%2ftransaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-comm%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here