sp return values failed to show up in variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sp return values failed to show up in variables

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-12-22 : 14:42:43

I am using ado 2.6 in Access 97 to call sp running at sql2k. The code below gave me the return values in my two output paramenters under debug view, but the values did not get assign into the variables( intRetCode & strRetMes). What went wrong?

Thanks!

Dim intRetCode As Integer
Dim strRetMes As String
....
With cmd
Set .ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "OP_checkValidDiscounts"
.Parameters.Append cmd.CreateParameter("@CO_Number", adVarChar, adParamInput, 15, rsTmp!head_order_nbr)
.Parameters.Append cmd.CreateParameter("@RetCode", adInteger, adParamOutput, 15, intRetCode)
.Parameters.Append cmd.CreateParameter("@RetMsg", adVarChar, adParamOutput, 100, strRetMes)
.Execute

End With

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-12-23 : 08:57:48

It turned out that I have to spesifically assign the parameters into my variables like this.

With cmd
...
.Execute
ntRetCode = .Parameters("@RetCode").Value
ntRetMsg = .Parameters("@RetMsg").Value
End With

Credit to Sergey Poberezovskiy from Microsoft Access forum, and here is his explaination:

the variables that you have used to create parameters are not sent by reference - they are passed ByVal - that is the reason that they are not receiving the returned values after the command execution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources