更新问题财政年度明智 [英] update problem financial year wise

问题描述

大家好，



我在finyearhosteldetails表中有finyearwise数据。但我无法更新它们每当我点击按钮更新它会给我以下错误。



执行后的事务计数表示开始和不匹配的数量提交声明。先前的计数= 1，当前计数= 0



如何解决这个问题？请优先考虑解决方案



我的sp是：

Hi everyone,

I have finyearwise data in finyearhosteldetails table. But i not able to update them Whenever i click button update it is giving me the following error.

transaction count after execute indicates a mismatching number of begin and commit statements. previous count = 1, current count = 0

How to solve this?? Please give the solution as it under top priority

My sp is:

ALTER PROCEDURE [dbo].[UpdateBasicHostelDetails] 
  (     
  @DistrictCd int,   
  @TalukCd int,  
  @VillageCd int,   
   @HostName varchar(150),  
    @PreOrPost int,   
    @ScOrSt varchar(10),   
    @BoysOrGirlsOrCoEd int,  
     @Sant int,   
     @Admt int,   
     @WardenName varchar(100),   
     @WardenMobNo float ,   
     @WardenMobNo2 float,   
     @NeighborName1 varchar(100),  
     @Neigh1MobNo float,   
     @NeighborName2 varchar(100),  
      @Neigh2MobNo float,   
      @Location varchar(150),   
      @HostelAdd varchar(500),   
      @User varchar(25),   
      @FinYear varchar(25),   
      @Ip varchar(150),   
      @HostelCd  int,
      @seccode nvarchar(max),
      @plan nvarchar(max)
)
as
begin 

	begin try 
	begin transaction   
	DECLARE @Count int
	
	update HostelDetails 
	set districtcd=@DistrictCd,talukcd=@TalukCd, HostelName=@HostName ,Location=@Location ,Hostel_Address=@HostelAdd , 
	Sc_St_Hostel=@ScOrSt,B_G_Coed=@BoysOrGirlsOrCoEd,Pre_Post=@PreOrPost ,
	Sanctioned=@Sant ,Admitted=@Admt ,Warden_Name=@WardenName ,Warden_Mobile_No1=@WardenMobNo ,
	Warden_Mobile_No2=@WardenMobNo2 ,Neighbor1_Name=@NeighborName1 ,Neighbor1_MobNo=@Neigh1MobNo ,
	Neighbor2_Name=@NeighborName2 ,Neighbor2_MobNo=@Neigh2MobNo ,status=1,Sector=@seccode,HostelPlan=@plan   
	where Hostelcd=@HostelCd


	--update  FinYearHostelDetails 
	--set HostelName=@HostName ,Location=@Location ,Hostel_Address=@HostelAdd , 
	--Sc_St_Hostel=@ScOrSt,B_G_Coed=@BoysOrGirlsOrCoEd,Pre_Post=@PreOrPost ,
	--Sanctioned=@Sant ,Admitted=@Admt ,Warden_Name=@WardenName ,Warden_Mobile_No1=@WardenMobNo ,
	--Warden_Mobile_No2=@WardenMobNo2 ,Neighbor1_Name=@NeighborName1 ,Neighbor1_MobNo=@Neigh1MobNo ,
	--Neighbor2_Name=@NeighborName2 ,Neighbor2_MobNo=@Neigh2MobNo ,status=1, Fin_year=@FinYear,Sector=@seccode,HostelPlan=@plan   
	--where Hostelcd=@HostelCd
		
	set @Count =(select count(*) from  FinYearHostelDetails where Hostelcd =@HostelCd and (Fin_year=@FinYear))     
	if (@Count = 0)          
	Begin            
	insert into FinYearHostelDetails(districtcd,talukcd,Hostelcd,HostelName,Sc_St_Hostel,B_G_Coed,Pre_Post, 
	Sanctioned,Admitted,Warden_Name,Warden_Mobile_No1,Warden_Mobile_No2,Neighbor1_Name,
	Neighbor1_MobNo,Neighbor2_Name,Neighbor2_MobNo,Location,Hostel_Address,Status,
	Fin_year,Sector,HostelPlan)            
	values (@DistrictCd,@TalukCd,@HostelCd,@HostName ,@ScOrSt ,@BoysOrGirlsOrCoEd ,@PreOrPost ,@Sant ,@Admt ,@WardenName ,
	@WardenMobNo ,@WardenMobNo2 ,@NeighborName1 ,@Neigh1MobNo ,@NeighborName2 ,@Neigh2MobNo ,@Location ,
	@HostelAdd ,0,@FinYear,@seccode,@plan)                                        
	End      
	Else        
	Begin           
	update  FinYearHostelDetails 
	set districtcd=@DistrictCd,talukcd=@TalukCd, HostelName=@HostName ,Location=@Location ,Hostel_Address=@HostelAdd ,
	Sc_St_Hostel=@ScOrSt ,B_G_Coed=@BoysOrGirlsOrCoEd ,Pre_Post=@PreOrPost , 
	Sanctioned=@Sant ,Admitted=@Admt ,Warden_Name=@WardenName ,Warden_Mobile_No1=@WardenMobNo ,
	Warden_Mobile_No2=@WardenMobNo2 ,Neighbor1_Name=@NeighborName1 ,Neighbor1_MobNo=@Neigh1MobNo ,
	Neighbor2_Name=@NeighborName2 ,Neighbor2_MobNo=@Neigh2MobNo ,status = 0,Fin_year=@FinYear,Sector=@seccode,HostelPlan=@plan           
	where  Hostelcd =@HostelCd                               
	 End   
	 commit transaction    
	 End Try
Begin Catch    
BEGIN        
ROLLBACK TRANSACTION    
END	
End catch
end

推荐答案

请阅读我对该问题的评论。



针对此类情况的一般解决方案是添加错误处理 [ ^ ]通过添加 TRY ... CATCH [ ^ ]块（指令）。这可能有助于您确定错误发生的位置。



如需了解更多信息，请参阅：

在Transact-SQL中使用TRY ... CATCH [ ^ ]

< a href =https://msdn.microsoft.com/en-us/library/ms141679%28v=sql.110%29.aspx>数据中的错误处理 [ ^ ]

Please, read my comment to the question.

A general solution for such as situations is to add error handling[^] via adding TRY... CATCH[^] block (instruction). This might help you identify where the error occurs.

For further information, please see:
Using TRY...CATCH in Transact-SQL[^]
Error Handling in Data[^]

SET XACT_ABORT ON



当SET XACT_ABORT为ON时，如果Transact-SQL语句引发运行时错误，则整个事务终止并回滚。



https://msdn.microsoft.com/en-us/library/ms188792.aspx for ref

SET XACT_ABORT ON

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

https://msdn.microsoft.com/en-us/library/ms188792.aspx for ref

这篇关于更新问题财政年度明智的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！