[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to capture "RAISERROR" from the stored procedure?

Posted on 2004-11-16
4
Medium Priority
?
819 Views
Last Modified: 2010-08-05
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!
0
Comment
Question by:sata
  • 2
4 Comments
 
LVL 8

Expert Comment

by:bukko
ID: 12592043
IF @@ERROR
   BEGIN
   -- Do whatever
   END
0
 
LVL 8

Accepted Solution

by:
bukko earned 240 total points
ID: 12592131
Instead of
   RAISERROR ('Account Code already existed',10,1)

try
   RAISERROR ('Account Code already existed',16,1)

I always use severity level 16 to return an error to the client and that seems to work.
0
 
LVL 7

Expert Comment

by:sukumar_diya
ID: 12592170
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
0
 
LVL 13

Expert Comment

by:KarinLoos
ID: 12592419
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

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question