Returning Value from Stored Procedure

I've found MANY articles but have yet to solve this one:

SP:
ALTER PROCEDURE [dbo].[ElementInRange] 
(
	@HighLow as int OUTPUT, 
	@Element varchar(2), 
	@Amount real, 
	@Material varchar(60)
)

AS
SET NOCOUNT ON

SELECT @HighLow = COALESCE( 
	CASE WHEN ElementMin > @Amount THEN -1 END,
	CASE WHEN ElementMax < @Amount Then 1 END
	)
FROM tblElements
WHERE Material = @Material AND Element = @Element AND ElementMin <> -10000 AND ElementMax <> -10000

Open in new window


Running the SP:
DECLARE	@return_value int,
		@HighLow int

SELECT	@HighLow = -1

EXEC	@return_value = [dbo].[ElementInRange]
		@HighLow = @HighLow OUTPUT,
		@Element = N'Cr',
		@Amount = 28,
		@Material = N'2205'

SELECT	@HighLow as N'@HighLow'

SELECT	'Return Value' = @return_value

Open in new window


Result is -1 which what it should be

MS Access Function:
Public Function RunSP(StoredProcedureName As String, ParamString As Variant, DataType As Variant, ParamValue As Variant)
         Dim ADOCon  As New ADODB.Connection
         Dim ADORs  As ADODB.recordset
         Dim ADOPrm As ADODB.Parameter
         Dim strSQL As String
         Dim stMessage As String
         Dim fld As ADODB.Field
         Dim ADOCmd As ADODB.Command
         Dim sParmName As String
         Dim strReturn As String
         Dim MyOutput As String
         
         
         Dim strPWD As String
10       On Error GoTo ErrorHandler
20       strPWD = [MyPassword]  
30       AppState.ConnectionString = "driver={SQL Server};server=[MyServer];database=[MyDatabase];uid=[MyUserName];pwd=" & strPWD
         
40       Set ADOCon = New ADODB.Connection
         
50       With ADOCon
60          .Provider = "MSDASQL"
70          .CursorLocation = adUseServer
80          .ConnectionString = AppState.ConnectionString
90          .Open
100      End With
         
110      Set ADOCmd = New ADODB.Command
         
120      Set ADOCmd.ActiveConnection = ADOCon
130      With ADOCmd
140         .CommandType = adCmdStoredProc
150         .CommandText = StoredProcedureName
160      End With

170      MyOutput = "HighLow"
180      Set ADOPrm = ADOCmd.CreateParameter(MyOutput, adInteger, adParamOutput, , 0)
190      ADOCmd.Parameters.Append ADOPrm

200      Set ADOPrm = ADOCmd.CreateParameter(ParamString(0), adVarChar, adParamInput, 20)
210      ADOCmd.Parameters.Append ADOPrm
220      ADOCmd.Parameters(ParamString(0)).Value = ParamValue(0)

230      Set ADOPrm = ADOCmd.CreateParameter(ParamString(1), DataType(1), adParamInput, 255)
240      ADOCmd.Parameters.Append ADOPrm
250      ADOCmd.Parameters(ParamString(1)).Value = ParamValue(1)
         
260      Set ADOPrm = ADOCmd.CreateParameter(ParamString(2), DataType(2), adParamInput)
270      ADOCmd.Parameters.Append ADOPrm
280      ADOCmd.Parameters(ParamString(2)).Value = ParamValue(2)
        
        Dim I As Integer
        Dim rstr As String
        
290     ADOCmd.Execute

300    Debug.Print ADOCmd.Parameters(0)
       

310      On Error GoTo 0
320      Exit Function

ErrorHandler:
         Dim strErrMsg As String
330      strErrMsg = "Error " & Err.Number & " (" & Err.Description & ") " & vbCrLf & _
         "In procedure: RunSP of modStoredProcedure" & vbCrLf & _
         "Error Line: " & Erl
      '
340      Debug.Print strErrMsg

End Function

Open in new window


Immediate Window:
?runsp("ElementInRange",Array("Element","Amount","Material"),Array(10,3,18),Array("Cr",22,"2205"))

Open in new window


Stored Procedure works as expected and returns '-1' as the result as expected
Function returns Null


Stumped on how to get the return value
Kurt BergmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
All you need to do is to assign the return value of the RunSP function to the ADOCmd.Parameters(0). Put this line to the near the end of the RunSP function:
RunSP = ADOCmd.Parameters(0).Value

Open in new window

0
Kurt BergmanAuthor Commented:
hi chaau,
it's still returning null
0
chaauCommented:
On the line 300 you have this line
Debug.Print ADOCmd.Parameters(0)

Open in new window

What happens if you replace it with
Debug.Print ADOCmd.Parameters(0).Value

Open in new window

Does it print anything in the debug window?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kurt BergmanAuthor Commented:
YAY!!!
you rock Chauu
Can I go home now?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.