Stored Procedure Output Value

Can some please tell what I am doing wrong.  I cannot get this to output a value.  If I dont assign a value to @SystemsAssigned parameter it gives me this error "Procedure or Function 'sp_CG_LB_SelectGrower' expects parameter '@SystemsAssigned', which was not supplied."  If I do assign a value it runs and returns nothing.

ALTER PROCEDURE [dbo].[sp_CG_LB_SelectGrower]
      -- Add the parameters for the stored procedure here
      @Operator varchar(50),
      @SystemsAssigned varchar(100) OUTPUT
AS
BEGIN
    Blah blah
END

            'Define command
            Using cmd As New SqlCommand(sql, New SqlConnection(My.Settings.MaterialsConnectionString))
                With cmd
                    'Set command type
                    .CommandType = CommandType.StoredProcedure
                    'Add parameters
                    .Parameters.Add(New SqlParameter("@Operator", SqlDbType.VarChar)).Value = Barcode
                    .Parameters.Add(New SqlParameter("@SystemsAssigned", SqlDbType.VarChar, 100, ParameterDirection.Output))

                    If .Connection.State = ConnectionState.Closed Then .Connection.Open()
                    'Execute command
                    .ExecuteNonQuery()
==>             SelectSystem = .Parameters("@SystemsAssigned").Value
                    'Close connection
                    .Connection.Dispose()
                End With
            End Using
pat_cunninghamAsked:
Who is Participating?
 
JimBrandleyCommented:
First, I have always sent a value with out parameters, 0 for integers and the empty string for varchars. I seem to remember a similar problem when I was first trying to use OUT parameters.

Second, when you say it doesn't return anything,
1. What does the SP assign to that variable? and
2. what is the value of that parameter when it gets back?

Jim
 
0
 
JimBrandleyCommented:
I just noticed one other issue: Parameter.Value is an object. Since you are retrieving a string, so you need to cast Parameters("@SystemsAssigned").Value to a string before you can assign it.

Jim



0
 
MrRobotCommented:
Hi there,

Have you checked if the SP returns a value for that given parameters using management studio?

Have you viewed .Parameters("@SystemsAssigned").Value after the execution in a debug session, is it an unintended result?

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with JimBrandley's first comment:
>First, I have always sent a value with out parameters, 0 for integers and the empty string for varchars. I seem to remember a similar problem when I was first trying to use OUT parameters.

do that:
 'Define command
            Using cmd As New SqlCommand(sql, New SqlConnection(My.Settings.MaterialsConnectionString))
                With cmd
                    'Set command type
                    .CommandType = CommandType.StoredProcedure
                    'Add parameters
                    .Parameters.Add(New SqlParameter("@Operator", SqlDbType.VarChar)).Value = Barcode
                    .Parameters.Add(New SqlParameter("@SystemsAssigned", SqlDbType.VarChar, 100, ParameterDirection.Output)).Value = ""

                    If .Connection.State = ConnectionState.Closed Then .Connection.Open()
                    'Execute command
                    .ExecuteNonQuery()
==>             SelectSystem = .Parameters("@SystemsAssigned").Value
                    'Close connection
                    .Connection.Dispose()
                End With
            End Using
0
 
MrRobotCommented:
btw,

I'm assuming you're already feeding your output variable like

.Parameters.Add(New SqlParameter("@SystemsAssigned", SqlDbType.VarChar, 100, ParameterDirection.Output)).Value = ""

since you've mentioned you already tested it assigning a value and it doesn't send the value and throws an exception otherwise.
0
 
pat_cunninghamAuthor Commented:
Thanks for your help I was able to get it to work like this.  I am still not sure why the other one would not work.

            Using cmd As New SqlCommand(sql, New SqlConnection(My.Settings.MaterialsConnectionString))
                With cmd
                    'Set command type
                    .CommandType = CommandType.StoredProcedure
                    'Add parameters
                    .Parameters.Clear()
                    .Parameters.AddWithValue("@Operator", Barcode)
                    .Parameters.AddWithValue("@SystemsAssigned", SelectSystem)
                    .Parameters("@SystemsAssigned").Direction = ParameterDirection.Output
                    .Parameters("@SystemsAssigned").Size = 100
                    If .Connection.State = ConnectionState.Closed Then .Connection.Open()
                    'Execute command
                    .ExecuteNonQuery()
                    SelectSystem = .Parameters("@SystemsAssigned").Value
                    'Close connection
                    .Connection.Dispose()
                End With
            End Using
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.