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