VB 6 does not get error from RAISERROR in SQL stored procdure


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: VB 6 does not get error from RAISERROR in SQL stored procdure

  1. #1
    Allison Boughton Guest

    VB 6 does not get error from RAISERROR in SQL stored procdure


    I'm calling a stored procedure to update the MS-SQL database (2000)
    from VB 6 using the ADO command object.

    My problem is that if the stored procedure fails, the error that is
    raised does not get returned back to VB. The stored procedure uses
    the RAISERROR command.

    I know that the stored procedure
    works as expected because when I call it from the Query Analyzer the error
    message
    that I raise is displayed. But for some reason this is not picked
    up by VB error handling.
    --------------------------------------------------------------------------
    This is the VB routine that executes the SP, and fails to pick up on the
    error:

    Private Function f_save() As Integer
    On Error GoTo here

    Dim adoCmd As ADODB.Command
    Dim errloop As ADODB.Error, errMsg As String

    Dim super As String, i As Integer, updateArrayX As Integer


    f_save = -1

    If cboSuper.ListIndex <> -1 Then
    super = supv(cboSuper.ListIndex).empcode
    End If

    Set adoCmd = Nothing
    Call combFrmShiftsSave(txtShift, txtHours, txtDescr, super, _
    IIf(chkMonday = 1, "1", "0"), txtStartHH(0), txtStartMM(0), txtEndHH(0),
    txtEndMM(0), _
    .... more parameters for each day of week
    adoCmd)

    Set adoCmd.ActiveConnection = SQLcn
    adoCmd.Execute
    Set adoCmd = Nothing

    ..... more screen updates

    f_save = 0

    Exit Function

    here:
    If SQLcn.Errors.Count > 0 Then
    'ADO ERROR
    For Each errloop In SQLcn.Errors
    errMsg = errMsg & errloop.Description & " (" & errloop.NativeError
    & ") "
    Next
    Screen.MousePointer = 0
    MsgBox errMsg, vbCritical, "ADO Error in f_save "
    Exit Function
    Else
    'VB ERROR
    errMsg = errMsg & Error & " (" & Err.Number & ")"
    Screen.MousePointer = 0
    MsgBox errMsg, vbCritical, "VB Error in f_save"
    Exit Function
    End If

    End Function
    -------------------------------------------------------------------
    This is the vb code that sets up the command object:

    Function combFrmShiftsSave(pshift As String, pHrsPerShift As Long, pShiftDesc
    As String, _
    pShiftSupv As String, _
    pMonday As String, pStartHHMon As Variant, pStartMMMon As Variant, _
    pEndHHMon As Variant, pEndMMMon As Variant, _
    ... more parameters for each day of week
    adoCmd As ADODB.Command) As Integer
    'builds command to update tables eff_shifts_sched and eff_shift_detail
    'ab010221
    Dim adoparam As ADODB.Parameter

    On Error GoTo here

    Set adoCmd = New ADODB.Command

    With adoCmd
    .CommandText = "frmShiftsSave" 'stored procedure name
    .CommandType = adCmdStoredProc

    'PARAMETERS - create and fill value
    Set adoparam = .CreateParameter _
    ("pShift", adVarChar, adParamInput, 10, pshift)
    .Parameters.Append adoparam

    Set adoparam = .CreateParameter _
    ("pHrsPerShift", adInteger, adParamInput, , pHrsPerShift)
    .Parameters.Append adoparam

    Set adoparam = .CreateParameter _
    ("pShiftDesc", adVarChar, adParamInput, 32, pShiftDesc)
    .Parameters.Append adoparam

    Set adoparam = .CreateParameter _
    ("pShiftSupv", adVarChar, adParamInput, 10, pShiftSupv)
    .Parameters.Append adoparam

    '--- Monday
    Set adoparam = .CreateParameter _
    ("pMonday", adVarChar, adParamInput, 1, pMonday)
    .Parameters.Append adoparam

    Set adoparam = .CreateParameter _
    ("pStartHHMon", adSmallInt, adParamInput, , AllowNulls(pStartHHMon))
    .Parameters.Append adoparam

    Set adoparam = .CreateParameter _
    ("pStartMMMon", adSmallInt, adParamInput, , AllowNulls(pStartMMMon))
    .Parameters.Append adoparam

    Set adoparam = .CreateParameter _
    ("pEndHHMon", adSmallInt, adParamInput, , AllowNulls(pEndHHMon))
    .Parameters.Append adoparam

    Set adoparam = .CreateParameter _
    ("pEndMMMon", adSmallInt, adParamInput, , AllowNulls(pEndMMMon))
    .Parameters.Append adoparam

    '--- repeated for other days of week

    'OTHER command properties
    .Prepared = False

    End With

    Exit Function

    here:
    MsgBox Error$, vbCritical, "Error in combFrmShiftsSave"
    Exit Function

    End Function


    ------------------------------------------------------------------
    This is the stored procedure.

    /* AB 010221 frmShiftsSave

    For SFCEFF - Shifts form - all updates in transaction
    tables: eff_shifts_sched
    eff_shift_detail

    RETURN VALUE 0=OK;
    -101=error, did not save any changes;

    */


    CREATE PROCEDURE frmShiftsSave

    ( @pShift varchar (10)

    , @pHrsPerShift integer
    , @pShiftDesc varchar (32)
    , @pShiftSupv varchar (10)

    , @pMonday varchar (1)
    , @pStartHHMon smallint
    , @pStartMMMon smallint
    , @pEndHHMon smallint
    , @pEndMMMon smallint
    ..... other days are defined the same way

    )

    AS

    DECLARE @returnVar integer


    BEGIN TRAN

    /* UPDATE TABLES IN ALPHABETICAL ORDER */

    -- update eff_shift_detail table

    --first delete existing records
    EXEC @returnVar = effShiftDetailDel @pShift
    IF @returnVar <> 0
    BEGIN
    ROLLBACK TRAN
    RAISERROR ('Error deleting existing eff_shift_detail', 16, 1)
    SELECT null
    RETURN -101
    END

    -- add back in
    if @pMonday = '1'
    BEGIN

    EXEC @returnVar = effShiftDetailAdd @pShift, 'Monday', @pStartHHMon,
    @pStartMMMon, @pEndHHMon, @pEndMMMon
    IF @returnVar <> 0
    BEGIN
    ROLLBACK TRAN
    RAISERROR ('Could not save Monday detail', 16, 1)
    RETURN -101
    END
    END

    --------------------------------------------------------------
    This is not the complete SP but the last EXEC is the one that
    fails (because a column was null). The transaction is rolled back
    correctly, but the VB program behaves as if no error occured.

    Sorry this was so long winded. Any ideas?

  2. #2
    Matthew Solnit Guest

    Re: VB 6 does not get error from RAISERROR in SQL stored procdure

    "Allison Boughton" <allison.l.boughton@us.arthurandersen.com> wrote in message
    news:3a9d09bd$1@news.devx.com...
    >
    > I'm calling a stored procedure to update the MS-SQL database (2000)
    > from VB 6 using the ADO command object.
    >
    > My problem is that if the stored procedure fails, the error that is
    > raised does not get returned back to VB. The stored procedure uses
    > the RAISERROR command.


    You can do two things:
    1) After the Command.Execute() methods returns, check the Connection.Errors
    collection to see if it has anything for you.
    2) <guess>Try changing the @severity parameter of RAISERROR</guess>. Maybe if
    the error is severe enough, it will get ADO's attention.

    -- Matthew Solnit



  3. #3
    Dennis Rehm Guest

    Re: VB 6 does not get error from RAISERROR in SQL stored procdure


    I always define my first parameter for the return value:

    With cmd.Parameters
    .Append cmd.CreateParameter("return_value", adInteger, adParamReturnValue)
    .Append cmd.CreateParameter(...)
    End With

    Dennis


    "Allison Boughton" <allison.l.boughton@us.arthurandersen.com> wrote:
    >
    >I'm calling a stored procedure to update the MS-SQL database (2000)
    >from VB 6 using the ADO command object.
    >
    >My problem is that if the stored procedure fails, the error that is
    >raised does not get returned back to VB. The stored procedure uses
    >the RAISERROR command.
    >
    >I know that the stored procedure
    >works as expected because when I call it from the Query Analyzer the error
    >message
    >that I raise is displayed. But for some reason this is not picked
    >up by VB error handling.
    >--------------------------------------------------------------------------
    >This is the VB routine that executes the SP, and fails to pick up on the
    >error:
    >
    >Private Function f_save() As Integer
    >On Error GoTo here
    >
    >Dim adoCmd As ADODB.Command
    >Dim errloop As ADODB.Error, errMsg As String
    >
    >Dim super As String, i As Integer, updateArrayX As Integer
    >
    >
    >f_save = -1
    >
    >If cboSuper.ListIndex <> -1 Then
    > super = supv(cboSuper.ListIndex).empcode
    >End If
    >
    >Set adoCmd = Nothing
    >Call combFrmShiftsSave(txtShift, txtHours, txtDescr, super, _
    >IIf(chkMonday = 1, "1", "0"), txtStartHH(0), txtStartMM(0), txtEndHH(0),
    >txtEndMM(0), _
    >.... more parameters for each day of week
    >adoCmd)
    >
    >Set adoCmd.ActiveConnection = SQLcn
    >adoCmd.Execute
    >Set adoCmd = Nothing
    >
    >..... more screen updates
    >
    >f_save = 0
    >
    >Exit Function
    >
    >here:
    >If SQLcn.Errors.Count > 0 Then
    >'ADO ERROR
    > For Each errloop In SQLcn.Errors
    > errMsg = errMsg & errloop.Description & " (" & errloop.NativeError
    >& ") "
    > Next
    > Screen.MousePointer = 0
    > MsgBox errMsg, vbCritical, "ADO Error in f_save "
    > Exit Function
    >Else
    >'VB ERROR
    > errMsg = errMsg & Error & " (" & Err.Number & ")"
    > Screen.MousePointer = 0
    > MsgBox errMsg, vbCritical, "VB Error in f_save"
    > Exit Function
    >End If
    >
    >End Function
    >-------------------------------------------------------------------
    >This is the vb code that sets up the command object:
    >
    >Function combFrmShiftsSave(pshift As String, pHrsPerShift As Long, pShiftDesc
    >As String, _
    > pShiftSupv As String, _
    > pMonday As String, pStartHHMon As Variant, pStartMMMon As Variant, _
    > pEndHHMon As Variant, pEndMMMon As Variant, _
    > ... more parameters for each day of week
    >adoCmd As ADODB.Command) As Integer
    >'builds command to update tables eff_shifts_sched and eff_shift_detail
    >'ab010221
    >Dim adoparam As ADODB.Parameter
    >
    >On Error GoTo here
    >
    >Set adoCmd = New ADODB.Command
    >
    >With adoCmd
    > .CommandText = "frmShiftsSave" 'stored procedure name
    > .CommandType = adCmdStoredProc
    >
    > 'PARAMETERS - create and fill value
    > Set adoparam = .CreateParameter _
    > ("pShift", adVarChar, adParamInput, 10, pshift)
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pHrsPerShift", adInteger, adParamInput, , pHrsPerShift)
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pShiftDesc", adVarChar, adParamInput, 32, pShiftDesc)
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pShiftSupv", adVarChar, adParamInput, 10, pShiftSupv)
    > .Parameters.Append adoparam
    >
    >'--- Monday
    > Set adoparam = .CreateParameter _
    > ("pMonday", adVarChar, adParamInput, 1, pMonday)
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pStartHHMon", adSmallInt, adParamInput, , AllowNulls(pStartHHMon))
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pStartMMMon", adSmallInt, adParamInput, , AllowNulls(pStartMMMon))
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pEndHHMon", adSmallInt, adParamInput, , AllowNulls(pEndHHMon))
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pEndMMMon", adSmallInt, adParamInput, , AllowNulls(pEndMMMon))
    > .Parameters.Append adoparam
    >
    >'--- repeated for other days of week
    >
    > 'OTHER command properties
    > .Prepared = False
    >
    >End With
    >
    >Exit Function
    >
    >here:
    > MsgBox Error$, vbCritical, "Error in combFrmShiftsSave"
    > Exit Function
    >
    >End Function
    >
    >
    >------------------------------------------------------------------
    >This is the stored procedure.
    >
    >/* AB 010221 frmShiftsSave
    >
    >For SFCEFF - Shifts form - all updates in transaction
    >tables: eff_shifts_sched
    > eff_shift_detail
    >
    >RETURN VALUE 0=OK;
    > -101=error, did not save any changes;
    >
    >*/
    >
    >
    >CREATE PROCEDURE frmShiftsSave
    >
    >( @pShift varchar (10)
    >
    >, @pHrsPerShift integer
    >, @pShiftDesc varchar (32)
    >, @pShiftSupv varchar (10)
    >
    >, @pMonday varchar (1)
    >, @pStartHHMon smallint
    >, @pStartMMMon smallint
    >, @pEndHHMon smallint
    >, @pEndMMMon smallint
    > ..... other days are defined the same way
    >
    >)
    >
    >AS
    >
    >DECLARE @returnVar integer
    >
    >
    >BEGIN TRAN
    >
    >/* UPDATE TABLES IN ALPHABETICAL ORDER */
    >
    >-- update eff_shift_detail table
    >
    >--first delete existing records
    >EXEC @returnVar = effShiftDetailDel @pShift
    >IF @returnVar <> 0
    >BEGIN
    > ROLLBACK TRAN
    > RAISERROR ('Error deleting existing eff_shift_detail', 16, 1)
    > SELECT null
    > RETURN -101
    >END
    >
    >-- add back in
    >if @pMonday = '1'
    >BEGIN
    >
    > EXEC @returnVar = effShiftDetailAdd @pShift, 'Monday', @pStartHHMon,
    > @pStartMMMon, @pEndHHMon, @pEndMMMon
    > IF @returnVar <> 0
    > BEGIN
    > ROLLBACK TRAN
    > RAISERROR ('Could not save Monday detail', 16, 1)
    > RETURN -101
    > END
    >END
    >
    >--------------------------------------------------------------
    >This is not the complete SP but the last EXEC is the one that
    >fails (because a column was null). The transaction is rolled back
    >correctly, but the VB program behaves as if no error occured.
    >
    >Sorry this was so long winded. Any ideas?



  4. #4
    a nil Guest

    Re: VB 6 does not get error from RAISERROR in SQL stored procdure


    Do Onething
    Check whether you are getting the error when you are running the stored procedure
    in Querry Analyzer.

    I also faced the same problem.

    Bye

    A nil

    "Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
    >"Allison Boughton" <allison.l.boughton@us.arthurandersen.com> wrote in message
    >news:3a9d09bd$1@news.devx.com...
    >>
    >> I'm calling a stored procedure to update the MS-SQL database (2000)
    >> from VB 6 using the ADO command object.
    >>
    >> My problem is that if the stored procedure fails, the error that is
    >> raised does not get returned back to VB. The stored procedure uses
    >> the RAISERROR command.

    >
    >You can do two things:
    >1) After the Command.Execute() methods returns, check the Connection.Errors
    >collection to see if it has anything for you.
    >2) <guess>Try changing the @severity parameter of RAISERROR</guess>. Maybe

    if
    >the error is severe enough, it will get ADO's attention.
    >
    >-- Matthew Solnit
    >
    >



  5. #5
    Dave Trimble Guest

    Re: VB 6 does not get error from RAISERROR in SQL stored procdure


    There is a "feature" of OLE DB that dicates that each separate SQL statement
    returns a status. This can mean that even if your stored procedure raises
    a user error, ADO does not populate its errors collection.

    Often the cure is to make

    SET NOCOUNT ON

    the VERY first line of your stored procedure. Note that this will still not
    work if you have a SELECT statement that returns rows.

    Hope this helps
    Dave


    "Matthew Solnit" <msolnit@nospam.yahoo-com> wrote:
    >"Allison Boughton" <allison.l.boughton@us.arthurandersen.com> wrote in message
    >news:3a9d09bd$1@news.devx.com...
    >>
    >> I'm calling a stored procedure to update the MS-SQL database (2000)
    >> from VB 6 using the ADO command object.
    >>
    >> My problem is that if the stored procedure fails, the error that is
    >> raised does not get returned back to VB. The stored procedure uses
    >> the RAISERROR command.

    >
    >You can do two things:
    >1) After the Command.Execute() methods returns, check the Connection.Errors
    >collection to see if it has anything for you.
    >2) <guess>Try changing the @severity parameter of RAISERROR</guess>. Maybe

    if
    >the error is severe enough, it will get ADO's attention.
    >
    >-- Matthew Solnit
    >
    >



  6. #6
    Dave Trimble Guest

    Re: VB 6 does not get error from RAISERROR in SQL stored procdure


    Allison

    There is a "feature" of OLE DB that dicates that each separate SQL statement
    returns a status. This can mean that even if your stored procedure raises
    a user error, ADO does not populate its errors collection.

    Often the cure is to make

    SET NOCOUNT ON

    the VERY first line of your stored procedure. Note that this will still not
    work if you have a SELECT statement that returns rows.

    Hope this helps
    Dave


    "Allison Boughton" <allison.l.boughton@us.arthurandersen.com> wrote:
    >
    >I'm calling a stored procedure to update the MS-SQL database (2000)
    >from VB 6 using the ADO command object.
    >
    >My problem is that if the stored procedure fails, the error that is
    >raised does not get returned back to VB. The stored procedure uses
    >the RAISERROR command.
    >
    >I know that the stored procedure
    >works as expected because when I call it from the Query Analyzer the error
    >message
    >that I raise is displayed. But for some reason this is not picked
    >up by VB error handling.
    >--------------------------------------------------------------------------
    >This is the VB routine that executes the SP, and fails to pick up on the
    >error:
    >
    >Private Function f_save() As Integer
    >On Error GoTo here
    >
    >Dim adoCmd As ADODB.Command
    >Dim errloop As ADODB.Error, errMsg As String
    >
    >Dim super As String, i As Integer, updateArrayX As Integer
    >
    >
    >f_save = -1
    >
    >If cboSuper.ListIndex <> -1 Then
    > super = supv(cboSuper.ListIndex).empcode
    >End If
    >
    >Set adoCmd = Nothing
    >Call combFrmShiftsSave(txtShift, txtHours, txtDescr, super, _
    >IIf(chkMonday = 1, "1", "0"), txtStartHH(0), txtStartMM(0), txtEndHH(0),
    >txtEndMM(0), _
    >.... more parameters for each day of week
    >adoCmd)
    >
    >Set adoCmd.ActiveConnection = SQLcn
    >adoCmd.Execute
    >Set adoCmd = Nothing
    >
    >..... more screen updates
    >
    >f_save = 0
    >
    >Exit Function
    >
    >here:
    >If SQLcn.Errors.Count > 0 Then
    >'ADO ERROR
    > For Each errloop In SQLcn.Errors
    > errMsg = errMsg & errloop.Description & " (" & errloop.NativeError
    >& ") "
    > Next
    > Screen.MousePointer = 0
    > MsgBox errMsg, vbCritical, "ADO Error in f_save "
    > Exit Function
    >Else
    >'VB ERROR
    > errMsg = errMsg & Error & " (" & Err.Number & ")"
    > Screen.MousePointer = 0
    > MsgBox errMsg, vbCritical, "VB Error in f_save"
    > Exit Function
    >End If
    >
    >End Function
    >-------------------------------------------------------------------
    >This is the vb code that sets up the command object:
    >
    >Function combFrmShiftsSave(pshift As String, pHrsPerShift As Long, pShiftDesc
    >As String, _
    > pShiftSupv As String, _
    > pMonday As String, pStartHHMon As Variant, pStartMMMon As Variant, _
    > pEndHHMon As Variant, pEndMMMon As Variant, _
    > ... more parameters for each day of week
    >adoCmd As ADODB.Command) As Integer
    >'builds command to update tables eff_shifts_sched and eff_shift_detail
    >'ab010221
    >Dim adoparam As ADODB.Parameter
    >
    >On Error GoTo here
    >
    >Set adoCmd = New ADODB.Command
    >
    >With adoCmd
    > .CommandText = "frmShiftsSave" 'stored procedure name
    > .CommandType = adCmdStoredProc
    >
    > 'PARAMETERS - create and fill value
    > Set adoparam = .CreateParameter _
    > ("pShift", adVarChar, adParamInput, 10, pshift)
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pHrsPerShift", adInteger, adParamInput, , pHrsPerShift)
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pShiftDesc", adVarChar, adParamInput, 32, pShiftDesc)
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pShiftSupv", adVarChar, adParamInput, 10, pShiftSupv)
    > .Parameters.Append adoparam
    >
    >'--- Monday
    > Set adoparam = .CreateParameter _
    > ("pMonday", adVarChar, adParamInput, 1, pMonday)
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pStartHHMon", adSmallInt, adParamInput, , AllowNulls(pStartHHMon))
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pStartMMMon", adSmallInt, adParamInput, , AllowNulls(pStartMMMon))
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pEndHHMon", adSmallInt, adParamInput, , AllowNulls(pEndHHMon))
    > .Parameters.Append adoparam
    >
    > Set adoparam = .CreateParameter _
    > ("pEndMMMon", adSmallInt, adParamInput, , AllowNulls(pEndMMMon))
    > .Parameters.Append adoparam
    >
    >'--- repeated for other days of week
    >
    > 'OTHER command properties
    > .Prepared = False
    >
    >End With
    >
    >Exit Function
    >
    >here:
    > MsgBox Error$, vbCritical, "Error in combFrmShiftsSave"
    > Exit Function
    >
    >End Function
    >
    >
    >------------------------------------------------------------------
    >This is the stored procedure.
    >
    >/* AB 010221 frmShiftsSave
    >
    >For SFCEFF - Shifts form - all updates in transaction
    >tables: eff_shifts_sched
    > eff_shift_detail
    >
    >RETURN VALUE 0=OK;
    > -101=error, did not save any changes;
    >
    >*/
    >
    >
    >CREATE PROCEDURE frmShiftsSave
    >
    >( @pShift varchar (10)
    >
    >, @pHrsPerShift integer
    >, @pShiftDesc varchar (32)
    >, @pShiftSupv varchar (10)
    >
    >, @pMonday varchar (1)
    >, @pStartHHMon smallint
    >, @pStartMMMon smallint
    >, @pEndHHMon smallint
    >, @pEndMMMon smallint
    > ..... other days are defined the same way
    >
    >)
    >
    >AS
    >
    >DECLARE @returnVar integer
    >
    >
    >BEGIN TRAN
    >
    >/* UPDATE TABLES IN ALPHABETICAL ORDER */
    >
    >-- update eff_shift_detail table
    >
    >--first delete existing records
    >EXEC @returnVar = effShiftDetailDel @pShift
    >IF @returnVar <> 0
    >BEGIN
    > ROLLBACK TRAN
    > RAISERROR ('Error deleting existing eff_shift_detail', 16, 1)
    > SELECT null
    > RETURN -101
    >END
    >
    >-- add back in
    >if @pMonday = '1'
    >BEGIN
    >
    > EXEC @returnVar = effShiftDetailAdd @pShift, 'Monday', @pStartHHMon,
    > @pStartMMMon, @pEndHHMon, @pEndMMMon
    > IF @returnVar <> 0
    > BEGIN
    > ROLLBACK TRAN
    > RAISERROR ('Could not save Monday detail', 16, 1)
    > RETURN -101
    > END
    >END
    >
    >--------------------------------------------------------------
    >This is not the complete SP but the last EXEC is the one that
    >fails (because a column was null). The transaction is rolled back
    >correctly, but the VB program behaves as if no error occured.
    >
    >Sorry this was so long winded. Any ideas?



  7. #7
    Allison Boughton Guest

    Re: VB 6 does not get error from RAISERROR in SQL stored procdure


    Dave -
    Thanks , SET NOCOUNT ON did the trick!
    Hope others will read this and avoid the
    problems I experienced.

    "Dave Trimble" <david.trimble@pendola.co.uk> wrote:
    >
    >Allison
    >
    >There is a "feature" of OLE DB that dicates that each separate SQL statement
    >returns a status. This can mean that even if your stored procedure raises
    >a user error, ADO does not populate its errors collection.
    >
    >Often the cure is to make
    >
    >SET NOCOUNT ON
    >
    >the VERY first line of your stored procedure. Note that this will still

    not
    >work if you have a SELECT statement that returns rows.
    >
    >Hope this helps
    >Dave
    >
    >
    >"Allison Boughton" <allison.l.boughton@us.arthurandersen.com> wrote:
    >>
    >>I'm calling a stored procedure to update the MS-SQL database (2000)
    >>from VB 6 using the ADO command object.
    >>
    >>My problem is that if the stored procedure fails, the error that is
    >>raised does not get returned back to VB. The stored procedure uses
    >>the RAISERROR command.
    >>
    >>I know that the stored procedure
    >>works as expected because when I call it from the Query Analyzer the error
    >>message
    >>that I raise is displayed. But for some reason this is not picked
    >>up by VB error handling.
    >>--------------------------------------------------------------------------
    >>This is the VB routine that executes the SP, and fails to pick up on the
    >>error:
    >>
    >>Private Function f_save() As Integer
    >>On Error GoTo here
    >>
    >>Dim adoCmd As ADODB.Command
    >>Dim errloop As ADODB.Error, errMsg As String
    >>
    >>Dim super As String, i As Integer, updateArrayX As Integer
    >>
    >>
    >>f_save = -1
    >>
    >>If cboSuper.ListIndex <> -1 Then
    >> super = supv(cboSuper.ListIndex).empcode
    >>End If
    >>
    >>Set adoCmd = Nothing
    >>Call combFrmShiftsSave(txtShift, txtHours, txtDescr, super, _
    >>IIf(chkMonday = 1, "1", "0"), txtStartHH(0), txtStartMM(0), txtEndHH(0),
    >>txtEndMM(0), _
    >>.... more parameters for each day of week
    >>adoCmd)
    >>
    >>Set adoCmd.ActiveConnection = SQLcn
    >>adoCmd.Execute
    >>Set adoCmd = Nothing
    >>
    >>..... more screen updates
    >>
    >>f_save = 0
    >>
    >>Exit Function
    >>
    >>here:
    >>If SQLcn.Errors.Count > 0 Then
    >>'ADO ERROR
    >> For Each errloop In SQLcn.Errors
    >> errMsg = errMsg & errloop.Description & " (" & errloop.NativeError
    >>& ") "
    >> Next
    >> Screen.MousePointer = 0
    >> MsgBox errMsg, vbCritical, "ADO Error in f_save "
    >> Exit Function
    >>Else
    >>'VB ERROR
    >> errMsg = errMsg & Error & " (" & Err.Number & ")"
    >> Screen.MousePointer = 0
    >> MsgBox errMsg, vbCritical, "VB Error in f_save"
    >> Exit Function
    >>End If
    >>
    >>End Function
    >>-------------------------------------------------------------------
    >>This is the vb code that sets up the command object:
    >>
    >>Function combFrmShiftsSave(pshift As String, pHrsPerShift As Long, pShiftDesc
    >>As String, _
    >> pShiftSupv As String, _
    >> pMonday As String, pStartHHMon As Variant, pStartMMMon As Variant, _
    >> pEndHHMon As Variant, pEndMMMon As Variant, _
    >> ... more parameters for each day of week
    >>adoCmd As ADODB.Command) As Integer
    >>'builds command to update tables eff_shifts_sched and eff_shift_detail
    >>'ab010221
    >>Dim adoparam As ADODB.Parameter
    >>
    >>On Error GoTo here
    >>
    >>Set adoCmd = New ADODB.Command
    >>
    >>With adoCmd
    >> .CommandText = "frmShiftsSave" 'stored procedure name
    >> .CommandType = adCmdStoredProc
    >>
    >> 'PARAMETERS - create and fill value
    >> Set adoparam = .CreateParameter _
    >> ("pShift", adVarChar, adParamInput, 10, pshift)
    >> .Parameters.Append adoparam
    >>
    >> Set adoparam = .CreateParameter _
    >> ("pHrsPerShift", adInteger, adParamInput, , pHrsPerShift)
    >> .Parameters.Append adoparam
    >>
    >> Set adoparam = .CreateParameter _
    >> ("pShiftDesc", adVarChar, adParamInput, 32, pShiftDesc)
    >> .Parameters.Append adoparam
    >>
    >> Set adoparam = .CreateParameter _
    >> ("pShiftSupv", adVarChar, adParamInput, 10, pShiftSupv)
    >> .Parameters.Append adoparam
    >>
    >>'--- Monday
    >> Set adoparam = .CreateParameter _
    >> ("pMonday", adVarChar, adParamInput, 1, pMonday)
    >> .Parameters.Append adoparam
    >>
    >> Set adoparam = .CreateParameter _
    >> ("pStartHHMon", adSmallInt, adParamInput, , AllowNulls(pStartHHMon))
    >> .Parameters.Append adoparam
    >>
    >> Set adoparam = .CreateParameter _
    >> ("pStartMMMon", adSmallInt, adParamInput, , AllowNulls(pStartMMMon))
    >> .Parameters.Append adoparam
    >>
    >> Set adoparam = .CreateParameter _
    >> ("pEndHHMon", adSmallInt, adParamInput, , AllowNulls(pEndHHMon))
    >> .Parameters.Append adoparam
    >>
    >> Set adoparam = .CreateParameter _
    >> ("pEndMMMon", adSmallInt, adParamInput, , AllowNulls(pEndMMMon))
    >> .Parameters.Append adoparam
    >>
    >>'--- repeated for other days of week
    >>
    >> 'OTHER command properties
    >> .Prepared = False
    >>
    >>End With
    >>
    >>Exit Function
    >>
    >>here:
    >> MsgBox Error$, vbCritical, "Error in combFrmShiftsSave"
    >> Exit Function
    >>
    >>End Function
    >>
    >>
    >>------------------------------------------------------------------
    >>This is the stored procedure.
    >>
    >>/* AB 010221 frmShiftsSave
    >>
    >>For SFCEFF - Shifts form - all updates in transaction
    >>tables: eff_shifts_sched
    >> eff_shift_detail
    >>
    >>RETURN VALUE 0=OK;
    >> -101=error, did not save any changes;
    >>
    >>*/
    >>
    >>
    >>CREATE PROCEDURE frmShiftsSave
    >>
    >>( @pShift varchar (10)
    >>
    >>, @pHrsPerShift integer
    >>, @pShiftDesc varchar (32)
    >>, @pShiftSupv varchar (10)
    >>
    >>, @pMonday varchar (1)
    >>, @pStartHHMon smallint
    >>, @pStartMMMon smallint
    >>, @pEndHHMon smallint
    >>, @pEndMMMon smallint
    >> ..... other days are defined the same way
    >>
    >>)
    >>
    >>AS
    >>
    >>DECLARE @returnVar integer
    >>
    >>
    >>BEGIN TRAN
    >>
    >>/* UPDATE TABLES IN ALPHABETICAL ORDER */
    >>
    >>-- update eff_shift_detail table
    >>
    >>--first delete existing records
    >>EXEC @returnVar = effShiftDetailDel @pShift
    >>IF @returnVar <> 0
    >>BEGIN
    >> ROLLBACK TRAN
    >> RAISERROR ('Error deleting existing eff_shift_detail', 16, 1)
    >> SELECT null
    >> RETURN -101
    >>END
    >>
    >>-- add back in
    >>if @pMonday = '1'
    >>BEGIN
    >>
    >> EXEC @returnVar = effShiftDetailAdd @pShift, 'Monday', @pStartHHMon,
    >> @pStartMMMon, @pEndHHMon, @pEndMMMon
    >> IF @returnVar <> 0
    >> BEGIN
    >> ROLLBACK TRAN
    >> RAISERROR ('Could not save Monday detail', 16, 1)
    >> RETURN -101
    >> END
    >>END
    >>
    >>--------------------------------------------------------------
    >>This is not the complete SP but the last EXEC is the one that
    >>fails (because a column was null). The transaction is rolled back
    >>correctly, but the VB program behaves as if no error occured.
    >>
    >>Sorry this was so long winded. Any ideas?

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center