asked on

How to capture "RAISERROR" from the stored procedure?

Hi,

I have tried raising error through my stored procedure, which looks like below:
CREATE PROCEDURE dbo.sp_insert_CustMaster
@CustCode varchar(10),@CustName varchar(100),
@EffFrom varchar(10),@EffTo varchar(10),
@BizType varchar(10),
@BillFreq int,@BillMth int,
@CreditLimit money,@UserID varchar(10)
AS

declare @accCode varchar(10)

select @accCode = sCustomerCodeCM
from tCustomerMaster
where sCustomerCodeCM = @CustCode

if @accCode = ''
begin
      insert into tCustomerMaster
      (sCustomerCodeCM, sCompanyNameCM, dEffFromCM, dEffToCM,
      iDefSchemeCM, sBusinessTypeCM,iBillingFrequencyCM,
      iBillingMthCM, sSetofAcctsCM, sTermOfPaymentCM,
      mCreditLimitCM, sCreateByCM, dCreateOnCM,
      sLastUpdatedByCM, dLastUpdatedOnCM, iRoutineVisitMthCM )
      values (@CustCode,@CustName,@EffFrom,@EffTo, 1,@BizType,
      @BillFreq,@BillMth, 'STD', '30PT', @CreditLimit, @UserID,
      getdate(), @UserID, getdate(), 0)

end
else
begin
      RAISERROR ('Account Code already existed',10,1)
end
GO

The VB code that I used to call the stored procedure is as below:

Dim Co_con As ADODB.Connection

'Then insert the customer address
Set Co_con = New ADODB.Connection
Co_con.Open "File Name=" & App.Path & "\cms_db.udl"

Co_con.sp_insert_CustMaster Me.txtCoCode.Text, Me.txtCoName.Text, Me.dtCoEffFrom.Value, _
Me.dtCoEffTo.Value, Me.cboBizType.Text, iBillFre, _
Me.cbo1stBillMth, CDbl(Me.txtCreditLimit.Text), "TT"

However, when I try to pick up the error by using some data which I'm sure will generate error, then Co_con.Errors.Count returns 0.

Can someone tell me how can I pick up the error from SQL successfully? Thanks!

bukko

IF @@ERROR
BEGIN
-- Do whatever
END

ASKER CERTIFIED SOLUTION

bukko

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sukumar_diya

Hi,

Try by catching the error object ..

On Error GoTo Err
'code to execute
Err:
If Err.Number = 50001 Then
' your statements go here
End If

Hope it helps you,
Sukumar

KarinLoos

or if you have no further processing to do within the sp if the account number already exists
CREATE PROCEDURE dbo.sp_insert_CustMaster
@CustCode varchar(10),@CustName varchar(100),
@EffFrom varchar(10),@EffTo varchar(10),
@BizType varchar(10),
@BillFreq int,@BillMth int,
@CreditLimit money,@UserID varchar(10)
AS
BEGIN
declare @accCode varchar(10)
select @accCode = sCustomerCodeCM
from tCustomerMaster
where sCustomerCodeCM = @CustCode
if @accCode = ''
begin
insert into tCustomerMaster
(sCustomerCodeCM, sCompanyNameCM, dEffFromCM, dEffToCM,
iDefSchemeCM, sBusinessTypeCM,iBillingFrequencyCM,
iBillingMthCM, sSetofAcctsCM, sTermOfPaymentCM,
mCreditLimitCM, sCreateByCM, dCreateOnCM,
sLastUpdatedByCM, dLastUpdatedOnCM, iRoutineVisitMthCM )
values (@CustCode,@CustName,@EffFrom,@EffTo, 1,@BizType,
@BillFreq,@BillMth, 'STD', '30PT', @CreditLimit, @UserID,
getdate(), @UserID, getdate(), 0)
end
else
begin
goto ERROR_HANDLER
end
return 0
ERROR_HANDLER:
return 1
end
GO

-- Now within your VB application check the value of the REturnCode parameter
for more help refer to the MSDN example (link provided)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_525v.asp

HTH

How to capture &quot;RAISERROR&quot; from the stored procedure?

How to capture "RAISERROR" from the stored procedure?