EXECUTE 后的事务计数指示缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句

Begin Tran

....

Commit Tran

 

中间不能出现 return,因为BEGIN TRANSACTION 语句将 @@TranCount加 1。ROLLBACK TRANSACTION 将 @@TranCount递减到 0,  return 使执行进程返回,但并没有使事务计数器减一,所以出现了语句执行后事务计数器出现不一致的情况....

execute后的事务计数指示少了commitrollback transaction

11-10

发些前台代码 SqlConnection con = baseoperate.getcon();rn con.Open();rn SqlTransaction tran = con.BeginTransaction();rn tryrn rn foreach (DataGridViewRow row in dataGridView1.Rows)rn rn SqlCommand sqlcom = new SqlCommand("GoodsOutput", con);rn sqlcom.Transaction = tran;rn sqlcom.CommandType = CommandType.StoredProcedure;rn sqlcom.Parameters.AddWithValue("@出仓单号", MOdanhao.Text);rn sqlcom.Parameters.AddWithValue("@出仓日期", MOdate.Value);rn sqlcom.Parameters.AddWithValue("@操作人员代码", MOperson.Text);rn sqlcom.Parameters.AddWithValue("@物料代码", Convert.ToString(row.Cells[0].Value));rn sqlcom.Parameters.AddWithValue("@数量", Convert.ToInt32(row.Cells[1].Value));rn sqlcom.Parameters.AddWithValue("@备注", MOremark.Text);rn sqlcom.Parameters.Add(new SqlParameter("@return",SqlDbType.Int));rn sqlcom.Parameters["@return"].Direction=ParameterDirection.ReturnValue;rn sqlcom.ExecuteNonQuery(); rn if (int.Parse(sqlcom.Parameters["@return"].Value.ToString())==0)rn rn tran.Rollback();rn MessageBox.Show("数量不足");rn return;rn rn sqlcom.Dispose();rnrnrnrnrnrnGoodsOutput存储过程rnrnset ANSI_NULLS ONrnset QUOTED_IDENTIFIER ONrnGOrnALTER procedure [dbo].[GoodsOutput] @出仓单号 nchar(20),@出仓日期 datetime,@操作人员代码 nchar(10),rn @物料代码 nchar(16),@数量 int,@备注 nchar(20)rnASrnbegin transactionrndeclare @序号 int,@number intrnif not exists(select * from Output)rn set @序号=1rnelsern set @序号=(select max(序号) from Output)+1rnset @number=(select 库存量 from material where 物料代码=@物料代码)-@数量rnif @number<0 rn beginrn rollbackrn return 0rn endrnelsern beginrn insert into Output values(@序号,@出仓单号,@出仓日期,@操作人员代码,@备注,@物料代码,@数量)rn update material set 库存量=库存量-@数量 where 物料代码=@物料代码rn commitrn return 1rn end rnrn rnrnrnrn把存储过程的事务去掉就不会出错,添加上去就出错。 求个解决方法 (不能取消事务) rn

存储过程问题:EXECUTE 后的事务计数指出缺少了 COMMITROLLBACK TRANSACTION 语句

10-18

各位大虾,我用SQL做了一个存储过程,调试的时候老是提示出错,详细错误如下:rn[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE 后的事务计数指出缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。原计数 = 0,当前计数 = 1。rnrn我的存储过程如下:rnrnCREATE proc dbo.Fee_Exrnrn@ReturnMsg int output --返回值 大于0表示成功 0表示不处理 负数表示失败rnrnASrnrn--Set @ReturnMsg=0rn--set @Remark='OK成功'rndeclare @lordname varchar(50) --用户名rndeclare @lordAccount varchar(10) --用户帐号rndeclare @Functype varchar(4) --业务类型rndeclare @lordnumb varchar(12) --主叫号rndeclare @benumb varchar(18) --被叫号rndeclare @starttime datetime --接通时间rndeclare @endtime datetime --结束时间rndeclare @Usetime datetime --使用时间rndeclare @timelong int --时长(秒)rndeclare @expenses int --费用rndeclare @Fee int --计费费率rndeclare @FeeTmp int --计费费率rndeclare @endtimeTmp datetime --结束时间rndeclare @pay int --金额rndeclare @tolltype int --付费类型rndeclare @id int --临时表话单IDrndeclare @ErrorCNT int --错误数rnrnSET @ErrorCNT=0rnWHILE 1=1 rnBEGINrnbegin TRANSACTION---***添加事务rnSet @id=-1rnselect top 1 @lordname=lordname,@lordAccount=lordAccount,@Functype=Functype,@id=[id],@benumb=benumb,@lordnumb=lordnumb,@endtime=endtime,@starttime=starttime,@expenses=Fee,@timelong=long FROM corp_excess WHERE isOK=0rnif @id=-1rnbreakrnSet @FeeTmp=-99999rnselect @FeeTmp=expenses,@endtimeTmp=endtime from corp_qd where benumb=@benumb and Functype=@Functype and starttime=@starttime and endtime>=@endtimernif @FeeTmp<>-99999 --找到已经存储的话单清单,且结束时间比上报的错误话单还晚则直接返回rn-- DELETE FROM corp_excess WHERE id=@id --删除该临时话单rnUPDATE corp_excess SET isOK=2 WHERE [id]=@idrnelsernbeginrnSet @FeeTmp=-99999rnselect @FeeTmp=expenses,@endtimeTmp=endtime from corp_qd where benumb=@benumb and Functype=@Functype and starttime=@starttime and endtime<=@endtimernif @FeeTmp<>-99999 --找到已经存储的话单清单但是结束时间小于上报的错误话单结束时间则以上报的为准,进行更新rn beginrn update corp_qd set expenses=@expenses,endtime=@endtime,timelong=@timelong,Usetime=@starttime where benumb=@benumb and Functype=@Functype and starttime=@starttimern select @pay=pay from corp_zh where lordname=@lordname --查询余额rn update corp_zh set pay=(@pay-(@expenses-@FeeTmp)) where lordname=@lordname --更新余额rn-- DELETE FROM corp_excess WHERE id=@id --删除该临时话单rn endrnelse --未找到,则新建rn beginrn insert into corp_qd(lordname,lordAccount,Functype,lordnumb,benumb,starttime,endtime,Usetime,timelong,expenses,Remark) values (@lordname,@lordAccount,@Functype,@lordnumb,@benumb,@starttime,@endtime,@starttime,@timelong,@expenses,'OK-处理临时表')rn select @pay=pay from corp_zh where lordname=@lordname --查询余额rn update corp_zh set pay=(@pay-@expenses) where lordname=@lordname --更新余额rn-- DELETE FROM corp_excess WHERE id=@id --删除该临时话单rn endrnendrnIf @@error <> 0rn BEGINrn SET @ErrorCNT=@ErrorCNT+1rn UPDATE corp_excess SET isOK=2 WHERE [id]=@idrn Rollback TRANSACTIONrnrn-- set @ReturnMsg=-1rn-- return @ReturnMsgrn ENDrnelsernbeginrn UPDATE corp_excess SET isOK=1 WHERE [id]=@idrn commit TRANSACTIONrn-- set @ReturnMsg=8rn-- return @ReturnMsgrnendrnend --WHILE 1=1rnrnif @ErrorCNT>0rnbeginrn set @ReturnMsg=-1rn return @ReturnMsgrnendrnelsernbeginrn set @ReturnMsg=1rn return @ReturnMsgrnendrnGO

EXECUTE 后的事务计数指出缺少了 COMMITROLLBACK TRANSACTION 语句

05-05

[code=SQL]rn/*-----------------------------------------------------------------------------------------------------------rn功能:提交表单并自动转交到下一步(适用于固定流程)rn时间:2008.10.11rn参数:rn说明:rn节点模式(0-任意授权者完成,1-所有授权者处理,2-自由节点)rn步骤人员办理模式(0-会签,1-非会签)rnrn--------------------------------------------------------------------------------------------------------------*/rnCREATE proc Arch_GotoNextSteprn@ArchObjectID int, rn@StepOrder int,rn@GroupIndex int,rn@UserID nvarchar(50),rn@ConsignedBy nvarchar(50),rn@SignText nvarchar(200),rnrn@sqlUpdateFile nvarchar(4000),rn@sqlInsertFile nvarchar(4000),rn@sqlInsertStep nvarchar(4000),rn@Sys int,rn@Mobile intrnrnAS rnrn SET XACT_ABORT ON rn begin tran t1rnrn-------------------------当前流程属性-----------------------------rn declare @ArchClassID int --流程定义编号rn declare @ArchFounder nvarchar(50) --流程创建人编号rn-------------------------当前步骤属性-----------------------------rn declare @StepIndex int --流程步骤定义序号rn declare @StepModelID int --节点模式rn declare @CurrMode int --步骤人员办理模式rnrn-------------------------下一步骤属性-----------------------------rn declare @NextStepModelID int --步节模式rn declare @NextStepType int --节点定义类型rn declare @MobileNumber varchar(30)--发送人手机号码rnrn --判断是否为本步骤处理人rn if NOT exists(select * from [ArchStepObjPersion] rn WHERE ArchObjectID=@ArchObjectID and StepOrder=@StepOrder rn and Transctor=@UserID and Transctor = @UserID and Consign = @ConsignedBy and GroupIndex = @GroupIndex and State<1)rn RETURNrnrn---------------------------------------初始化起始值-----------------------------rn--取出当前流程定义编号rn SELECT @ArchClassID= ArchClassID FROM [ArchObject] WHERE ArchObjectID= @ArchObjectID rnrn--取出当前步骤定义的索引和人员办理模式rn SELECT @StepIndex=StepIndex,@CurrMode = State From [ArchStepObject] where ArchObjectID= @ArchObjectID and StepOrder=@StepOrderrn SELECT @StepModelID=StepModelID FROM [ArchStepClass] where ArchClassID=@ArchClassID AND StepIndex=@StepIndexrnrn ----------------------------修改当前步骤信息及状态---------------------------------------------------------rnrn --删除处理人的委托或者被委托人员rnrn IF @ConsignedBy <> '' rn beginrn Delete From ArchStepObjPersion Where ArchObjectID =@ArchObjectID and State <= 0 and StepOrder =@StepOrder rn and Transctor =@ConsignedBy and GroupIndex = @GroupIndexrn endrn ELSErn beginrn Delete From ArchStepObjPersion Where ArchObjectID =@ArchObjectID and State <= 0 and StepOrder =@StepOrder rn and Consign =@UserID and GroupIndex = @GroupIndexrn endrn -- 修改当前步骤信息(办理意见、意见内容)rn Update ArchStepObjPersion Set TransResult = 1 ,TransSign = @SignText,State = 1,endtime = getdate()rn WHERE ArchObjectID =@ArchObjectID and StepOrder = @StepOrder and Transctor = @UserID AND Consign = @ConsignedBy and GroupIndex = @GroupIndexrnrn ----------------------------处理公共附件及当前办理人附件-----------------------------rn if @sqlUpdateFile<>'' exec(@sqlUpdateFile)--更新公共附件信息rn if @sqlInsertFile<>'' exec(@sqlInsertFile)--添加公共附件信息rn if @sqlInsertStep<>'' exec(@sqlInsertStep)--添加当前办理人附件信息rnrn ----------------------------当前步骤处理--------------------------------------------------rn IF ((@StepModelID =1) or (@StepModelID = 2 and @CurrMode = 1))--//当前步骤是会签rn BEGINrn IF EXISTS(Select * From ArchStepObjPersion Where ArchObjectID =@ArchObjectID and State <= 0 and StepOrder =@StepOrder)rn BEGINrn returnrn ENDrn ENDrn Elsern beginrn Delete From ArchStepObjPersion Where ArchObjectID =@ArchObjectID and StepOrder =@StepOrder And (Transctor <> @UserID or Consign <> @ConsignedBy) and GroupIndex = @GroupIndexrn endrnrn ----------------------------判断当前步骤是否为最后一步-----------------------------rn IF NOT EXISTS(Select * From ArchStepClass Where ArchClassID =@ArchClassID and StepIndex > @StepIndex)rn BEGIN rn -- 结束公文流 rn --Update ArchStepObject Set State = 1 Where ArchObjectID =@ArchObjectID and StepOrder=@StepOrderrn if not Exists(Select * From ArchStepObjPersion Where ArchObjectID =@ArchObjectID and State < 1)rn Update ArchObject Set State = 1 Where ArchObjectID =@ArchObjectIDrn returnrn ENDrnrn ----------------------------下一步处理----------------------------- rn --下一步定义类型 rn SELECT @NextStepType = StepTypeID FROM [ArchStepClass] where ArchClassID=@ArchClassID AND StepIndex=@StepIndex + 1rnrn Insert into ArchStepObject(ArchObjectID,StepIndex,StepOrder,BeginTime,State,Remark) rn values (@ArchObjectID,@StepIndex + 1,@StepOrder+1,getdate(),0,'')rnrn --下一步骤为返回发起人节点rn if (@NextStepType = 6)rn beginrn --查询出流程创建人编号rn select @ArchFounder = Founder From ArchObject Where ArchObjectID = @ArchObjectIDrn rn insert into ArchStepObjPersion(ArchObjectID,StepOrder,Transctor,GroupIndex,BeginTime,EndTime,TransResult,TransSign,State)rn values(@ArchObjectID,@StepOrder + 1,@ArchFounder,1,getdate(),'',0,'',-1)rn endrn elsern beginrn --创建下一步人员rn declare @NextIndex int rn set @NextIndex = @StepIndex+1rn if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)rn DROP table temprn create table temp(UID NVARCHAR(50))rn INSERT INTO TEMP EXEC FLOW_GetStepUser @ArchClassID,@NextIndexrn insert into ArchStepObjPersion(ArchObjectID,StepOrder,GroupIndex,BeginTime,EndTime,TransResult,TransSign,State,Transctor) rn SELECT @ArchObjectID,@StepOrder+1,1,getdate(),'',0,'',-1,Transctor rn FROM(SELECT [Uid] as Transctor FROM [temp]) as b rn if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)rn drop table temp rn end rncommit tran t1rnSET XACT_ABORT OFFrn[/code]

EXECUTE 后的事务计数指出缺少了 COMMITROLLBACK TRANSACTION 语句

02-24

ALTER PROCEDURE [dbo].[Proc_DomainSales_Pay]rn @id int,--域名拍卖表IDrn @uid int,--用户IDrn @money numeric(18, 2),--冻结金额rn @ret int output --成功返回1,失败返回零rnASrnBEGINrnbegin transactionrnif(@money>0)rnbeginrn --解冻拍卖领先金额-Startrn declare @congeal_moneysum numeric(9,2)rn set @congeal_moneysum = 0rn select @congeal_moneysum = moneysum FROM eb_congeal where userid=@uid and linkid=@id and types = 2 and state = 1rn if not @congeal_moneysum is null and @congeal_moneysum <> 0rn beginrn -- 更新用户冻结金额rn update eb_user set usemoney=usemoney + @congeal_moneysum, Congealsum = Congealsum - @congeal_moneysum where id = @uidrn if(@@error<>0)rn beginrn set @ret=0;rn rollback transaction;rn return 1;rn endrn update eb_congeal set state= 0 where userid=@uid and linkid=@id and types = 1 and state = 1rn if(@@error<>0)rn beginrn set @ret=0;rn rollback transaction;rn return 1;rn endrn endrnrn --解冻拍卖领先金额--Endrnrn declare @strdomain varchar(256);rn--修改用户可用金额rn update eb_user set useMoney=useMoney-@money, Congealsum=Congealsum+@money where id=@uidrn if(@@error<>0)rn beginrn set @ret=0;rn rollback transaction;rn return 1;rn endrn select @strdomain=strdomain from EB_domain_sals where userid=@uidrn --增加冻结信息rn INSERT INTO EB_Congeal( [linkid], [Moneysum], [userid], [types], [remark], [addtime], [state])rn VALUES(@id,@money,@uid,102,'支付拍卖域名'+@strdomain+'冻结',getdate(),1);rn if(@@error<>0)rn beginrn set @ret=0;rn rollback transaction;rn return 1;rn endrn --增加财务记录rn --改变域名操作状态rn update Eb_domain_sals set mkstate=1 where[id]=@idrnendrnrn if(@@error =0)rn beginrn set @ret=1rn commit transaction;rn return 1;rn endrn rn set @ret=0rn rollback transactionrn rnENDrnrn程序在执行时,提示rnEXECUTE 后的事务计数指出缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。原计数 = 0,当前计数 = 1rn我实在看不出来哪儿的问题

没有更多推荐了,返回首页