如题:
vb里用On Error GoTo Errhandle捕获错误存储过程如下:
CREATE PROCEDURE dbo.P_Import
@ID nvarchar(50) output,@ImportCode nvarchar(20) output,@Client nvarchar(20),@ClassName nvarchar(20),@Standard nvarchar(20),@Num decimal(18,3),@Unit nvarchar(10),@Memo nvarchar(50),
@MainID nvarchar(50) output,@ImportDate datetime,@SendCode nvarchar(20),@Provider nvarchar(20),@Storage nvarchar(10),@Operator nvarchar(20),@MainMemo nvarchar(50)
AS
DECLARE @MaxNum as int
begin tran SetPropertiesif Exists(SELECT * FROM T_ImportDetails WHERE ImportCode=@ImportCode AND Client=@Client AND ClassName=@ClassName AND Standard=@Standard AND ID<>@ID) RAISERROR ('该入库单中已有相同的入库资料,不允许重复!',16,1)
if @@error <> 0 GOTO Err_Handler--主表的处理
IF Len(@ImportCode)=0
BEGIN
SET @ImportCode='P'+convert(varchar(6),getdate(),12)
SELECT @MaxNum=CAST(Right(IsNull(Max(ImportCode),@ImportCode+'000'),3) AS INT)+1 FROM T_ImportInfo WHERE ImportCode LIKE @ImportCode+'%'
if @MaxNum<10
SET @ImportCode=@ImportCode+'00'+CAST(@MaxNum as varchar(3))
else if @MaxNum<100
SET @ImportCode=@ImportCode+'0'+CAST(@MaxNum as varchar(3))
else
SET @ImportCode=@ImportCode+CAST(@MaxNum as varchar(3))
if @@error <> 0 GOTO Err_Handler
SET @MainID=NEWID()
INSERT INTO T_ImportInfo Values(@MainID,@ImportDate,@ImportCode,@SendCode,@Provider,@Storage,0,null,'',@Operator,@MainMemo)
if @@error <> 0 GOTO Err_Handler
END
ELSE
UPDATE T_ImportInfo SET ImportDate=@ImportDate,SendCode=@SendCode,Provider=@Provider,Storage=@Storage,Operator=@Operator,Memo=@MainMemo WHERE ImportCode=@ImportCode
if @@error <> 0 GOTO Err_Handler--从表的处理
IF len(@ID)=0
BEGIN
SET @ID=NEWID()
INSERT INTO T_ImportDetails Values(@ID,@ImportCode,@Client,@ClassName,@Standard,@Num,@Unit,@Memo)
End
Else
BEGIN
Update T_ImportDetails SET ID=@ID,ImportCode=@ImportCode,Client=@Client,ClassName=@ClassName,Standard=@Standard,Num=@Num,Unit=@Unit,Memo=@Memo
WHERE ID=@ID
End--数据字典的处理
--供应商表
if not exists(SELECT * FROM T_Provider WHERE ShortName=@Provider)
INSERT INTO T_Provider Values(NEWID(),'',@Provider,@Provider,'','','','','')
if @@error <> 0 GOTO Err_Handler
--客户表
if not exists(SELECT * FROM T_Client WHERE ShortName=@Client)
INSERT INTO T_Client Values(NEWID(),'',@Client,@Client,'','','','','','','')
if @@error <> 0 GOTO Err_Handler
--品名表
if not exists(SELECT * FROM T_ClassName WHERE ClassName=@ClassName)
INSERT INTO T_ClassName Values(NEWID(),@ClassName)
if @@error <> 0 GOTO Err_Handler
--规格型号表
if not exists(SELECT * FROM T_Standard WHERE Standard=@Standard)
INSERT INTO T_Standard Values(NEWID(),@Standard)
if @@error <> 0 GOTO Err_Handler
--单位表
if not exists(SELECT * FROM T_Unit WHERE Unit=@Unit)
INSERT INTO T_Unit Values(NEWID(),@Unit)
if @@error <> 0 GOTO Err_Handler
--仓库表
if not exists(SELECT * FROM T_Storage WHERE Storage=@Storage)
INSERT INTO T_Storage Values(NEWID(),@Storage,'')if @@error <> 0 GOTO Err_Handler commit tran SetProperties Return Err_Handler: rollback tran SetProperties Return
GO当运行到INSERT INTO T_Provider Values(NEWID(),'',@Provider,@Provider,'','','','','') 时会因为该表的主键约束导致插入失败,2627错误,但在VB里调用以上存储过程时无法捕获到这个错误(请不要怀疑是否存在这个错误,因为在查询分析里用同样的参数来执行该存储过程时,会提示这个错误)另外,如果我把存储过程主体部分只保留该insert语句,把其它语句注释掉以后再运行,在VB里能捕获到这个错误:“主键约束导致插入失败!”请问这是什么原因造成的?这样的错误处理应该没有问题啊!100分奉上,请高手解答,不胜感激!
vb里用On Error GoTo Errhandle捕获错误存储过程如下:
CREATE PROCEDURE dbo.P_Import
@ID nvarchar(50) output,@ImportCode nvarchar(20) output,@Client nvarchar(20),@ClassName nvarchar(20),@Standard nvarchar(20),@Num decimal(18,3),@Unit nvarchar(10),@Memo nvarchar(50),
@MainID nvarchar(50) output,@ImportDate datetime,@SendCode nvarchar(20),@Provider nvarchar(20),@Storage nvarchar(10),@Operator nvarchar(20),@MainMemo nvarchar(50)
AS
DECLARE @MaxNum as int
begin tran SetPropertiesif Exists(SELECT * FROM T_ImportDetails WHERE ImportCode=@ImportCode AND Client=@Client AND ClassName=@ClassName AND Standard=@Standard AND ID<>@ID) RAISERROR ('该入库单中已有相同的入库资料,不允许重复!',16,1)
if @@error <> 0 GOTO Err_Handler--主表的处理
IF Len(@ImportCode)=0
BEGIN
SET @ImportCode='P'+convert(varchar(6),getdate(),12)
SELECT @MaxNum=CAST(Right(IsNull(Max(ImportCode),@ImportCode+'000'),3) AS INT)+1 FROM T_ImportInfo WHERE ImportCode LIKE @ImportCode+'%'
if @MaxNum<10
SET @ImportCode=@ImportCode+'00'+CAST(@MaxNum as varchar(3))
else if @MaxNum<100
SET @ImportCode=@ImportCode+'0'+CAST(@MaxNum as varchar(3))
else
SET @ImportCode=@ImportCode+CAST(@MaxNum as varchar(3))
if @@error <> 0 GOTO Err_Handler
SET @MainID=NEWID()
INSERT INTO T_ImportInfo Values(@MainID,@ImportDate,@ImportCode,@SendCode,@Provider,@Storage,0,null,'',@Operator,@MainMemo)
if @@error <> 0 GOTO Err_Handler
END
ELSE
UPDATE T_ImportInfo SET ImportDate=@ImportDate,SendCode=@SendCode,Provider=@Provider,Storage=@Storage,Operator=@Operator,Memo=@MainMemo WHERE ImportCode=@ImportCode
if @@error <> 0 GOTO Err_Handler--从表的处理
IF len(@ID)=0
BEGIN
SET @ID=NEWID()
INSERT INTO T_ImportDetails Values(@ID,@ImportCode,@Client,@ClassName,@Standard,@Num,@Unit,@Memo)
End
Else
BEGIN
Update T_ImportDetails SET ID=@ID,ImportCode=@ImportCode,Client=@Client,ClassName=@ClassName,Standard=@Standard,Num=@Num,Unit=@Unit,Memo=@Memo
WHERE ID=@ID
End--数据字典的处理
--供应商表
if not exists(SELECT * FROM T_Provider WHERE ShortName=@Provider)
INSERT INTO T_Provider Values(NEWID(),'',@Provider,@Provider,'','','','','')
if @@error <> 0 GOTO Err_Handler
--客户表
if not exists(SELECT * FROM T_Client WHERE ShortName=@Client)
INSERT INTO T_Client Values(NEWID(),'',@Client,@Client,'','','','','','','')
if @@error <> 0 GOTO Err_Handler
--品名表
if not exists(SELECT * FROM T_ClassName WHERE ClassName=@ClassName)
INSERT INTO T_ClassName Values(NEWID(),@ClassName)
if @@error <> 0 GOTO Err_Handler
--规格型号表
if not exists(SELECT * FROM T_Standard WHERE Standard=@Standard)
INSERT INTO T_Standard Values(NEWID(),@Standard)
if @@error <> 0 GOTO Err_Handler
--单位表
if not exists(SELECT * FROM T_Unit WHERE Unit=@Unit)
INSERT INTO T_Unit Values(NEWID(),@Unit)
if @@error <> 0 GOTO Err_Handler
--仓库表
if not exists(SELECT * FROM T_Storage WHERE Storage=@Storage)
INSERT INTO T_Storage Values(NEWID(),@Storage,'')if @@error <> 0 GOTO Err_Handler commit tran SetProperties Return Err_Handler: rollback tran SetProperties Return
GO当运行到INSERT INTO T_Provider Values(NEWID(),'',@Provider,@Provider,'','','','','') 时会因为该表的主键约束导致插入失败,2627错误,但在VB里调用以上存储过程时无法捕获到这个错误(请不要怀疑是否存在这个错误,因为在查询分析里用同样的参数来执行该存储过程时,会提示这个错误)另外,如果我把存储过程主体部分只保留该insert语句,把其它语句注释掉以后再运行,在VB里能捕获到这个错误:“主键约束导致插入失败!”请问这是什么原因造成的?这样的错误处理应该没有问题啊!100分奉上,请高手解答,不胜感激!
set xact_abort on
@ID nvarchar(50) output,@ImportCode nvarchar(20) output,@Client nvarchar(20),@ClassName nvarchar(20),@Standard nvarchar(20),@Num decimal(18,3),@Unit nvarchar(10),@Memo nvarchar(50),
@MainID nvarchar(50) output,@ImportDate datetime,@SendCode nvarchar(20),@Provider nvarchar(20),@Storage nvarchar(10),@Operator nvarchar(20),@MainMemo nvarchar(50)
AS
DECLARE @MaxNum as int
begin tran SetProperties---就该了这段
if Exists(SELECT * FROM T_ImportDetails WHERE ImportCode=@ImportCode AND Client=@Client AND ClassName=@ClassName AND Standard=@Standard AND ID<>@ID)
begin
RAISERROR ('该入库单中已有相同的入库资料,不允许重复!',16,1)
GOTO Err_Handler
end
--主表的处理
IF Len(@ImportCode)=0
BEGIN
SET @ImportCode='P'+convert(varchar(6),getdate(),12)
SELECT @MaxNum=CAST(Right(IsNull(Max(ImportCode),@ImportCode+'000'),3) AS INT)+1 FROM T_ImportInfo WHERE ImportCode LIKE @ImportCode+'%'
if @MaxNum<10
SET @ImportCode=@ImportCode+'00'+CAST(@MaxNum as varchar(3))
else if @MaxNum<100
SET @ImportCode=@ImportCode+'0'+CAST(@MaxNum as varchar(3))
else
SET @ImportCode=@ImportCode+CAST(@MaxNum as varchar(3))
if @@error <> 0 GOTO Err_Handler
SET @MainID=NEWID()
INSERT INTO T_ImportInfo Values(@MainID,@ImportDate,@ImportCode,@SendCode,@Provider,@Storage,0,null,'',@Operator,@MainMemo)
if @@error <> 0 GOTO Err_Handler
END
ELSE
UPDATE T_ImportInfo SET ImportDate=@ImportDate,SendCode=@SendCode,Provider=@Provider,Storage=@Storage,Operator=@Operator,Memo=@MainMemo WHERE ImportCode=@ImportCode
if @@error <> 0 GOTO Err_Handler--从表的处理
IF len(@ID)=0
BEGIN
SET @ID=NEWID()
INSERT INTO T_ImportDetails Values(@ID,@ImportCode,@Client,@ClassName,@Standard,@Num,@Unit,@Memo)
End
Else
BEGIN
Update T_ImportDetails SET ID=@ID,ImportCode=@ImportCode,Client=@Client,ClassName=@ClassName,Standard=@Standard,Num=@Num,Unit=@Unit,Memo=@Memo
WHERE ID=@ID
End--数据字典的处理
--供应商表
if not exists(SELECT * FROM T_Provider WHERE ShortName=@Provider)
INSERT INTO T_Provider Values(NEWID(),'',@Provider,@Provider,'','','','','')
if @@error <> 0 GOTO Err_Handler
--客户表
if not exists(SELECT * FROM T_Client WHERE ShortName=@Client)
INSERT INTO T_Client Values(NEWID(),'',@Client,@Client,'','','','','','','')
if @@error <> 0 GOTO Err_Handler
--品名表
if not exists(SELECT * FROM T_ClassName WHERE ClassName=@ClassName)
INSERT INTO T_ClassName Values(NEWID(),@ClassName)
if @@error <> 0 GOTO Err_Handler
--规格型号表
if not exists(SELECT * FROM T_Standard WHERE Standard=@Standard)
INSERT INTO T_Standard Values(NEWID(),@Standard)
if @@error <> 0 GOTO Err_Handler
--单位表
if not exists(SELECT * FROM T_Unit WHERE Unit=@Unit)
INSERT INTO T_Unit Values(NEWID(),@Unit)
if @@error <> 0 GOTO Err_Handler
--仓库表
if not exists(SELECT * FROM T_Storage WHERE Storage=@Storage)
INSERT INTO T_Storage Values(NEWID(),@Storage,'')if @@error <> 0 GOTO Err_Handler commit tran SetProperties Return
Err_Handler: rollback tran SetProperties Return
GO
上面的sql语句太多了 不知道大家有没有耐心看
其实就是运行到这里的时候:
--供应商表
if not exists(SELECT * FROM T_Provider WHERE ShortName=@Provider)
INSERT INTO T_Provider Values(NEWID(),'',@Provider,@Provider,'','','','','')
if @@error <> 0 GOTO Err_Handler
我测试的数据 是会产生一个主键约束冲突的 然后我在vb里有捕获错误的语句。
但捕获不了这个错误,并且存储过程运行到这里的时候 的确没有往下运行了 而是goto err-handler了
如果我把该存储过程的参数定义部分和错误处理部分保留 而把其他的语句注释掉 就是存储过程主体仅仅为 if not exists(SELECT * FROM T_Provider WHERE ShortName=@Provider)
INSERT INTO T_Provider Values(NEWID(),'',@Provider,@Provider,'','','','','')
if @@error <> 0 GOTO Err_Handler这时候能捕获到错误 一点问题都没有
放发生错误,回滚时候,在vb中并不能通过Error来获取错误信息。可以自己根据实际定义错误信息。
如:
Err_Handler:
rollback tran SetProperties
SELECT '错误信息内容!'
Return在vb中根据返回的记录集来判断是否有错误。