Error not being trapped with @@error

Error not being trapped with @@error

Post by Matt Wilkinso » Wed, 29 Jan 2003 18:23:43



Could anybody suggest what might prevent the following code from failing
to trap an insert error successfully? When I encounter a Index violation
error, the code in the Err_out:
block to rollback is never run for some reason.

Thanks
Matt

..lots of other code here
PRINT 'Web Only members inserted'

-- insert Online members
INSERT FFGSuppliers(
         sup_org_id
        ,sup_per_id)
SELECT  DISTINCT
         org_id
        ,per_id
FROM
        Organisations                  
        INNER JOIN persons ON per_id = odt_per_id
WHERE
        odt_prod_id IN ('01010200','01020200','01011100')
        AND (org_enddate IS NULL)      
        AND NOT EXISTS (SELECT sup_org_id FROM ffgsuppliers WHERE sup_org_id =
org_id )        



BEGIN
        RAISERROR ('Error inserting online members',16,1)      
        RETURN GOTO err_out
END
....code removed here!
Err_out:
 Rollback Tran

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Error not being trapped with @@error

Post by Tibor Karasz » Wed, 29 Jan 2003 18:31:56


You have RETURN before GOTO. RETURN terminates the batch.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> Could anybody suggest what might prevent the following code from failing
> to trap an insert error successfully? When I encounter a Index violation
> error, the code in the Err_out:
> block to rollback is never run for some reason.

> Thanks
> Matt

> ..lots of other code here
> PRINT 'Web Only members inserted'

> -- insert Online members
> INSERT FFGSuppliers(
> sup_org_id
> ,sup_per_id)
> SELECT  DISTINCT
> org_id
> ,per_id
> FROM
> Organisations
> INNER JOIN persons ON per_id = odt_per_id
> WHERE
> odt_prod_id IN ('01010200','01020200','01011100')
> AND (org_enddate IS NULL)
> AND NOT EXISTS (SELECT sup_org_id FROM ffgsuppliers WHERE sup_org_id =
> org_id )



> BEGIN
> RAISERROR ('Error inserting online members',16,1)
> RETURN GOTO err_out
> END
> ....code removed here!
> Err_out:
>  Rollback Tran

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!


 
 
 

Error not being trapped with @@error

Post by Matt Wilkinso » Wed, 29 Jan 2003 18:47:35


Ooops! Thanks.
Matt

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Error Trapping: MS Access errors not trapped in VB code

I have an error trap in my VB 5.0 code that doesn't work when an error
is coming from and MS Access db.  These are errors that occur when
there is a primary key violation or a referential integrity violation.
I have done similar error trapping in the past and it worked fine.
The only difference in code is that I was using a data control in the
code that worked and now I'm working directly with the recordsets.  

The following is the code sample.  The error occus on rsPallet.Update:

        On Error GoTo ErrHandler

        rsPallet.AddNew
        rsPallet![StockNum] = txtPaperType.Text
        rsPallet![LoadTicket] = txtLoadNum.Text
        rsPallet![LinYds] = txtTotYards.Text
        rsPallet![DateRec] = txtTruckDate.Text
        rsPallet![RollCount] = txtNumRolls.Text
        rsPallet![UserId] = gUserID

ErrHandler:
    Dim errorNumber
    errorNumber = Err.Number
    Select Case errorNumber
    Case 3022   'primary key violation - load number is not unique for
the truck date given
        message = "Load number " & txtLoadNum.Text & " has been used
on " & txtTruckDate.Text _
                    & ".  Would you like me to assign a unique load
number?"
        answer = MsgBox(message, vbYesNo, "Primary Key Violation")
        If answer = vbNo Then
            txtStockNum.SetFocus
            txtStockNum.SelStart = 0
            txtStockNum.SelLength = Len(txtStockNum.Text)
        Else
            loadNumber = rsLoadNum![UniqueNum]
            txtStockNum.Text = loadNumber
            rsLoadNum![UniqueNum] = (loadNumber + 1)
        End If
    Case 3201   'invalid paper type, number given doesn't exit in
Paper table
        message = "The stock number " & txtStockNum.Text & " does not
exist." & _
                    "  Please enter a valid stock number."
        MsgBox message, vbExclamation, "Invalid Stock Number"
    Case Else
        message = "The following error has occured:  " & Err.Number &
", " & Err.Description

    End Select

Has anyone else encounter this problem or know the solution?

Thanks.

Sharon Stokesbary

2. BDE Settings from Delphi....

3. Error Trapping - Can you trap a DTS Package error using SQLDMO running a job

4. CA-ORANGE COUNTY-58133--ORACLE Financials-DBA Skills-Customer Support-Oracle Financials Specialists/DBAs

5. ado errors not trapped by vb error object???

6. Efficiency dealing with dupes

7. @@error not trapping errors?

8. Info Advantage Question

9. error-scenarios that can not be trapped with @@error

10. FoxPro, printer not ready error trap

11. Object variable or With block variable not set (Trapping SQL errors)

12. VB brRead - item missing not invoking on error trap

13. Errors not being trapped while changing DBC's