Ado.net - the Size property has an invalid size of 0

Question

Hello.
I'm trying to get output value from DB via ADO.net. There's a client code:

    using (var connection = new SqlConnection(ConnectionString))
    {
        connection.Open();
        SqlCommand command = new SqlCommand("pDoSomethingParamsRes", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@i", 1);
        var outParam = new SqlParameter("@out", SqlDbType.VarChar);
        outParam.Direction = ParameterDirection.Output;
        command.Parameters.Add(outParam);
        command.ExecuteNonQuery();
        Console.WriteLine(command.Parameters["@out"].Value.ToString());
    }

When I run this I get the following exception:

the Size property has an invalid size of 0

According to manual SqlParameter.Size Property I might omit size. Why do I get this exception? How to make it work without passing size?
Thank you for your help!

Answer 1

Parameter Size is required for variable size Output parameters. Generally ADO.NET decides the size of the parameter based on the Value assigned to the parameter (hence it is optional), but in output parameter since no value is Set, you need provide the size required for the parameter

Set the Parameter size to size of the output variable from the DB... Say 50

outParam.Size = 50;

Answer 2

I'm not sure if this is the same problem i've had before, but using a byte for a parameter can sometimes lead to this error.

Try this. Explicitly declare the i parameter as a variable. THEN assign it's value with the Value property.

Answer 3

VarChar and NVarChar are variable width character fields (thus var+char). You have to set the length, otherwise the default is zero.

Answer 4

Check MSDN : SqlParameter.Size Property

For bidirectional and output parameters, and return values, you must set the value of Size. This is not required for input parameters, and if not explicitly set, the value is inferred from the actual size of the specified parameter when a parameterized statement is executed.