事务问题

[liang80318]

写了一个事务,执行一级动态生成的SQL


CREATE PROCEDURE L_InsertNew
@strSql1 varchar(5000),
@strSql2 varchar(5000),
@strSql3 varchar(5000),
@intOut int output
AS
SET NOCOUNT ON
begin tran
exec(@strSql1)
exec(@strSql2)
exec(@strSql3)
if @@Error <> 0 or @@rowcount = 0 goto ErrMsg
set @intOut=1
commit
ErrMsg:
set nocount off
rollback tran
set @intOut=0 --添加失败并回滚


GO

在VB中执行,调用过程
With comCommand
.ActiveConnection = g_conConnection2
.CommandType = adCmdStoredProc
.CommandText = "L_InsertNew"
.Parameters("@strSql1") = strSql1
.Parameters("@strSql2") = strSql2
.Parameters("@strSql3") = strSql3
.Execute
但是执行到2,3时,出现错误,生成的语句如下:

insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',5563,14480,'1',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',5567,14480,'65.6',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',14027,14480,'212.8',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',14033,14480,'15.2',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',14050,14480,'14.8',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',14055,14480,'226.8',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',14067,14480,'30',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',14077,14480,'3.2',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',14078,14480,'5',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',14090,14480,'2.2',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',14100,14480,'1.4',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',14104,14480,'4.2',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',14106,14480,'8.2',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',14123,14480,'15.2',0)
insert into ICInventory(FBatchNo,fitemid,fstockid,fqty,fbrno) values ('',14125,14480,'4.8',0)

出现错误:应用程序在当前操作中使用了错误类型的值

[tushadongjing]

2005可以用try catch

[dawugui]

建议在VB中使用VB的代码来获取错误信息然后来判断是否进行下一步工作.

[pt1314917]

顶`

[-狙击手-]

if @@Error  <>  0 or @@rowcount = 0 
begin
   set nocount off 
   rollback tran  
   set @intOut=0   --添加失败并回滚 

end
else
begin
   set @intOut=1 
   commit 
end

[liang80318]

兄弟们，找到方法了，在ＶＢ里直接写了个事务提交，然后就可以了

[dawugui]

哦,恭喜.
那接20分.

[dawugui]

唉.

慢了.