Solved

Returning Value from Stored Procedure

Posted on 2014-11-26
4
136 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
  • 2
  • 2
4 Comments
 
LVL 24

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 24

Accepted Solution

by:
chaau earned 500 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now