I am experiencing a problem whereby I am unable to retrive an value from a stored procedure. I have a small test app to demonstrate the problem that I am having with my main application.
I am using SQL2008 and vb.net in VS2008.
In my database I have created a stored procedure :-
CREATE PROCEDURE [RF].[TestProc]
-- Add the parameters for the stored procedure here
@ParamOUT VARCHAR(60) OUTPUT
SET @ParamOUT = 'Result(' + @ParamIN + ')'
and my vb code which calls this procedure is a follows :-
dbConn = New SqlConnection(dbConnectionString)
dbCmd = New SqlCommand
dbCmd.Connection = dbConn
dbCmd.CommandType = CommandType.Text
dbCmd.CommandText = "EXEC RF.TestProc @ParamIn, @ParamOut"
Dim ParamIn As New SqlParameter("@ParamIn", SqlDbType.NVarChar, 30, ParameterDirection.Input)
Dim ParamOut As New SqlParameter("@ParamOut", SqlDbType.NVarChar, 60, ParameterDirection.Output)
ParamIn.Value = "Hello"
ParamOut.Direction = ParameterDirection.Output
Label1.Text = ParamOut.Value
[ I have removed extraneous stuff like try...catch, connection string setup etc ]
The test app failed to return a value. It returns DBNull, which causes an exception when the label1.text value is set.
However, if I execute the stored procedure under SQL2008 management studio, the stored procedure works as expected.
Any guidance on what I may be doignwrong and which is preventing me from getting the return value would be greatfully received.