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

[Allison Boughton]

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?

[Matthew Solnit]

"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

[Dennis Rehm]

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?

[a nil]

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
>
>

[Dave Trimble]

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
>
>

[Dave Trimble]

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?

[Allison Boughton]

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?
>