?
Solved

Returning Value from Stored Procedure

Posted on 2014-11-26
4
Medium Priority
?
154 Views
Last Modified: 2014-11-26
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
0
Comment
Question by:Kurt Bergman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40468119
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
 

Author Comment

by:Kurt Bergman
ID: 40468126
hi chaau,
it's still returning null
0
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 40468133
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
 

Author Closing Comment

by:Kurt Bergman
ID: 40468145
YAY!!!
you rock Chauu
Can I go home now?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question