SQL Parameter value is getting truncated

Question

I have the following code which simply executes a stored procedure which accepts 1 parameter.

Public Function GetData(ByVal Faccode As String, Optional ByRef s As String = "") As DataSet

    Dim params As SqlParameter() = {New SqlParameter("@aFacilityCode", SqlDbType.VarChar, ParameterDirection.Input)}

    ' Set the value
    params(0).Value = "SW29" 'Faccode

    Try
        Dim DSet As DataSet = RunProcedure("usp_FL_GetAllData", params, "ContactData")
        Return DSet
    Catch ex As Exception
        Return Nothing
    End Try

End Function

Protected Overloads Function RunProcedure( _
  ByVal storedProcName As String, _
  ByVal parameters As IDataParameter(), _
  ByVal tableName As String) _
  As DataSet

  Dim dataSet As New dataSet

  Try
    myConnection.Open()
    Dim sqlDA As New SqlDataAdapter
    sqlDA.SelectCommand = BuildQueryCommand(storedProcName, parameters)
    sqlDA.Fill(dataSet, tableName)

    Return dataSet
  Catch ex As Exception
    Return Nothing
  Finally
    If myConnection.State = ConnectionState.Open Then
      myConnection.Close()
    End If
  End Try
End Function

Private Function BuildQueryCommand( _
  ByVal storedProcName As String, _
  ByVal parameters As IDataParameter()) _
  As SqlCommand

  Dim command As New SqlCommand(storedProcName, myConnection)
  command.CommandType = CommandType.StoredProcedure

  Dim parameter As SqlParameter
  For Each parameter In parameters
    command.Parameters.Add(parameter)
  Next

  Return command

End Function

The SQL procedure is defined like so:

CREATE PROCEDURE [dbo].[usp_FL_GetAllData]
(
   @aFacilityCode VARCHAR(10)
)

When I run the software, SQL Profiler shows this call is being made:

exec usp_FL_GetAllData @aFacilityCode='S'

Initially, I was assigning the value Faccode for my parameter in the GetData function but noticed this weird truncation, which is why I'm now hardcoding the value.

The only thing I could think of is that the SQL procedure defined the parameter as a varchar(1) but it's defined as 10 so I don't know why this is happening. RunProcedure is used in many places which do not exhibit this behavior.

What else could be causing this?

Answer 1

To see why removing the parameter direction from your constructor call solves the problem, take a look at the list of constructors defined by the SqlParameter class. Note that there is no constructor that takes a parameter name, SqlDbType, and ParameterDirection; the constructor you're actually invoking is this one, whose third parameter is the parameter size. Because the backing value of ParameterDirection.Input is 1, you are explicitly setting the size of the parameter to one character.

When you instead invoke a constructor that doesn't explicitly give a size, the object infers the size of the parameter from the value that you assign, as described in the documentation for that property.