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!
LVL 1
sataAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bukkoCommented:
IF @@ERROR
   BEGIN
   -- Do whatever
   END
0
bukkoCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sukumar_diyaCommented:
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
KarinLoosCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.