[AccessD] adodb returning error code from stored procedures - CODE

James Barash James at fcidms.com
Thu May 14 13:01:18 CDT 2009


John:

If you just want to get a Return value, you need to add a ReturnParameter to
your command object:

cmd.Parameters.Append cmd.CreateParameter("Return", adInteger,
adParamReturnValue)

As I recall, it must be declared as the first parameter and must be an
integer.  To retrieve the value, after the Execute method use:

cmd.Parameters("Return")

In your Stored Procedure, just add a value to the Return statement:

return 0

Or

return 1

Or

declare @ret int
set @ret = 1

return @ret

Output parameters work similarly but adding that to a generic procedure
would be more complicated. You'd probably have to pass 2 param arrays, one
for input and one for output.

The output parameters are declared:

cmd.Parameters.Append cmd.CreateParameter("Total", adDouble, adParamOutput)

After the Execute method, the parameter value is available with

cmd.Parameters("Total")


In your stored procedure, add OUTPUT to the parameter declaration:

Create Procedure [dbo].[sp_GetTotal]
	(
		@id int,
		@Total float OUTPUT
	)

That should do it.

Hope that helps.

James Barash

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, May 14, 2009 11:08 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] adodb returning error code from stored procedures - CODE

The following is the code I currently use for executing stored procedures.
As I mentioned in the 
previous email, it works, but it does not return any value from the SP.  I
have played around trying 
to make it do so but I think this is one of those cases where I could play
for months and never get 
results without help.

Any assistance is greatly appreciated.

'---------------------------------------------------------------------------
------------
' Procedure : CallADOStoredProc
' Author    : jwcolby
' Date      : 1/29/2009
' Purpose   : Calls a stored procedure passing in only "INPUT" parameters to
the SP
'               SPs can have INPUT parameters and OUTPUT parameters.
Passing OUTPUT
'               parameters requires a different syntax when creating the
parameter.
'               This function can only pass INPUT parameters.
'
'               I am leaving this function as is because much of the time
that is all
'               that is required.  I will build another function to handle
calls to
'               stored procedures that can handle both.
'---------------------------------------------------------------------------
------------
'
Public Function CallADOStoredProcIn(strServerName As String, strDatabase As
String, _
                                     ByVal SPName As String, _
                                     ParamArray Params() As Variant) As
Boolean
   'Created by Charlotte Foust 9/30/2000
   'last modified 1/12/2001
   'Calls a saved query or stored procedure
   On Error GoTo Proc_err
   Dim varValue As Variant
   Dim strSQL As String
   Dim intLoop As Integer
   Dim varPrmType As Variant
   Dim lngRecords As Long


   Dim cnn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim errCurr As ADODB.Error
   Dim colErrs As ADODB.Errors
   Dim lTimeoutSeconds As Long

   Const ERR_OPER_ON_INVALID_CONNECTION = 3709
   Const ERR_RECORD_IS_DELETED = -2147467259
   Const ERR_Timeout = -2147217871

   Set cnn = New ADODB.Connection
   cnn.ConnectionString = mTrustedConnection(strServerName, strDatabase)
   cnn.CursorLocation = adUseClient

   'this simplifies accessing the errors
   'collection, which belongs to the
   'connection
   Set colErrs = cnn.Errors

   cnn.Open

   Set cmd = New ADODB.Command
   lTimeoutSeconds = 55000
   With cmd
     .ActiveConnection = cnn
     'this could also be written as
     'colErrs.Clear
     .ActiveConnection.Errors.Clear
     .CommandType = adCmdStoredProc
     .CommandText = SPName
     .CommandTimeout = lTimeoutSeconds

     For intLoop = LBound(Params) To UBound(Params)
       Select Case VarType(Params(intLoop))
         Case vbString
           varPrmType = adVarWChar
         Case vbLong
           varPrmType = adBigInt
         Case vbDate
           varPrmType = adDate
           'if SQL Server, use adDBTimeStamp
         Case vbInteger
           varPrmType = adSmallInt
         Case vbDouble
           varPrmType = adDouble
         Case vbSingle
           varPrmType = adSingle
         Case vbBoolean
           varPrmType = adBoolean
         Case vbCurrency
           varPrmType = adCurrency
            Case vbByte
           varPrmType = adUnsignedTinyInt
         Case vbNull
           varPrmType = Null
         Case Else
           'WARNING!  Not supported in ADO 2.5
           varPrmType = adVariant
       End Select
       If varPrmType = adVarWChar Then
         .Parameters.Append .CreateParameter( _
               "prm" & intLoop, varPrmType, adParamInput,
Len(Params(intLoop)) + 2, Params(intLoop))
       Else
         'you have to create ALL the parameters
         .Parameters.Append .CreateParameter( _
               "prm" & intLoop, varPrmType, adParamInput, , Params(intLoop))
       End If 'varPrmType = adVarWChar
     Next intLoop
     .Execute RecordsAffected:=lngRecords, Options:=adCmdStoredProc
     CallADOStoredProcIn = True
   End With
Proc_exit:
   On Error Resume Next
   CallADOStoredProcIn = lngRecords
   Set cmd = Nothing
   Exit Function
Proc_err:
   'ADO errors and Jet errors aren't the same
   'collection, so this handles them differently
   If colErrs.Count > 0 Then
     'There are ADO errors
     For Each errCurr In colErrs
       Select Case errCurr
         Case ERR_OPER_ON_INVALID_CONNECTION
         Stop
           Resume Proc_exit
         Case "Timeout expired" 'Timeout
             Debug.Print "Command timeout in CallAdoStoredProcIn: " &
lTimeoutSeconds & " Seconds"
             If lTimeoutSeconds > 60000 Then
                 MsgBox errCurr.Number & "--" _
                         & errCurr.Description & " (" _
                         & errCurr.Source & ")"
                 Resume Proc_exit
             Else
                 lTimeoutSeconds = lTimeoutSeconds * 2
                 Resume 0
             End If
         Case Else
             MsgBox errCurr.Number & "--" _
                     & errCurr.Description & " (" _
                     & errCurr.Source & ")"
             Resume Proc_exit
       End Select
     Next errCurr
     colErrs.Clear
   Else
     'there is an other error
     MsgBox Err.Number & "--" & Err.Description
     Resume Proc_exit
   End If
     Resume 0
End Function

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list