No returned value from Stored Procedure

MartinHouse
MartinHouse used Ask the Experts™
on
Hi experts,

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
      @ParamIN  VARCHAR(30),
      @ParamOUT VARCHAR(60) OUTPUT
AS
BEGIN
      SET @ParamOUT = 'Result(' + @ParamIN + ')'
END

and my vb code which calls this procedure is a follows :-

        dbConn = New SqlConnection(dbConnectionString)
        dbConn.Open()

        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

        dbCmd.Parameters.Add(ParamIn)
        dbCmd.Parameters.Add(ParamOut)
       
        dbCmd.ExecuteScalar()

        Label1.Text = ParamOut.Value

        dbConn.Close()

[ 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.

Thanks

Mart
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You need to do

SET @ParamOUT = 'Result(' + @ParamIN + ')'
SELECT @ParamOUT
Hi,

I am not sure that how you should call the procedure with in/out parameters in VB.Net. But while talking from SQL Point of view, the output parameter must be called with an OUTPUT clause.

So, kindly change your below code:
dbCmd.CommandText = "EXEC RF.TestProc @ParamIn, @ParamOut"

Open in new window

to this:
dbCmd.CommandText = "EXEC RF.TestProc @ParamIn, @ParamOut OUTPUT"

Open in new window

And then try
Shouldn't it be

dbCmd.CommandType=CommandType.StoredProcedure
Basically a custom class is a blueprint for creating an object.
It can have local member variables that only it can see.
Property accessors to get/set those values. This helps because you can control how they are accessed, what values they are set to and if they are just readable or just writable or both. Also if the underlying implementation changes, it may be they it can still be returned to the user in the expected way and not break their code that uses objects created by your class.

A class also has methods to let it do stuff to the internal data or something logically related to what it does.

Example:

Class Line
    Private _X1 As Integer = 1
    Private _X2 As Integer = 100
    Private _Y1 As Integer = 10
    Private _Y2 As Integer = 10

    Property X1 As Integer
        Get
            Return _X1
        End Get
        Set(value As Integer)
            _X1 = value
        End Set
    End Property

    Property X2 As Integer
        Get
            Return _X2
        End Get
        Set(value As Integer)
            _X2 = value
        End Set
    End Property

    Property Y1 As Integer
        Get
            Return _Y1
        End Get
        Set(value As Integer)
            _Y1 = value
        End Set
    End Property

    Property Y2 As Integer
        Get
            Return _Y2
        End Get
        Set(value As Integer)
            _Y2 = value
        End Set
    End Property

    'constructor used to set the value when New is called
    'Example:
    'dim MyLine as new Line(5,100,200,100)
    Sub New(x1, y1, x2, y2)
        Me.X1 = x1
        Me.Y1 = y1
        Me.X2 = x2
        Me.Y2 = y2
    End Sub

    'now it needs a method to do something
    Sub Draw()
        'inside here would be code used to draw it
    End Sub
End Class

'to call this
dim MyLine as New Line(5,100,500,100)
'if you had code in the draw routine it would draw the line
MyLine.Draw()

Open in new window


So with the class defined we could make an array of lines easily and not have to think of how to draw them, etc.
We could add a distance or Measure method to line to tell the length of the line, etc.
We could make a box class that used four variables, each holding a line object if we liked.

Author

Commented:
That did the trick for me.  Simple and effective :-)

Many thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial