Stored procedure return varchar

Question

Hi! I am trying to create stored procedure that gone return varchar value, and that value I need to display in textbox.

This is the code for stored procedure:

Create PROCEDURE Status @id_doc int, @Name varchar(50) OUTPUT
AS
select  @Name =items.name    
from Doc,Items where @id_doc=IDN and doc.IDN=Items.ID 
return @Name 

This is the code in vb.net where i need to display returned value from procedure in textbox:

Public Sub Current()
        Dim dtc As New Data.DataTable
        Dim dr As SqlClient.SqlDataReader
        Dim da As New SqlClient.SqlDataAdapter
        Dim cmd As New SqlClient.SqlCommand
        Dim id_doc As Integer
        Dim dset As New DataSet
        Dim recordset As DataRow
        Dim Name As String

        Try
            id_doc = idDocExplorer
            cmd.Connection = pubCon
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "Status"
            cmd.Parameters.Add("@id_doc", id_doc)
            cmd.Parameters.Add("@Name ", SqlDbType.VarChar)
            cmd.Parameters("@Name ").Direction = ParameterDirection.Output
            cmd.ExecuteScalar()

            Name = cmd.Parameters("@Name ").Value
           TextBox1.text=Name
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            dtc = Nothing
            dr = Nothing
            da = Nothing
        End Try
    End Sub

When I try to execute this code I get this message in exception:

"String[1]: the Size property has an invalid size of 0."

What I am doing wrong? Thanks!

Answer 1

You need to specify the size of the @Name parameter when creating it.

You also have an extra space in the "@Name " string - it should be "@Name":

cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50)

Answer 2

Try changing:

cmd.Parameters.Add("@Name", SqlDbType.VarChar)

to:

cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50)

to define the size of the output parameter

Answer 3

you should try to call ExecuteNonQuery not ExecuteScalar