InvalidOperationException was caught: The Size property has an invalid size of 0

ObjectDataSource update and insert operations throw an error when output parameters are used.

Added on 10 Jul 2008

Scenarios:

Scenario Summary:

Binding a FormView to an ObjectDataSource, inserting and updating records throws the error: InvalidOperationException was caught:
String[14]: the Size property has an invalid size of 0.

Scenario Details:

The stored procedure parameters are set as follows:

CREATE PROCEDURE [dbo].[app_Areas_Update]
(
     -- Add the parameters for the stored procedure here

     @AreaId int,
     @Description varchar(255) = '',
     @Enabled bit,
     @Name varchar(50) = '',
     @Message varchar(255) OUTPUT,
     @Error bit OUTPUT
)
AS
BEGIN
     -- do something here
END

ASPX ObjectDatasource code: (For the sake of brevity, only showing updateparameters)

    <asp:ObjectDataSource ID="ods_Groups" runat="server" DeleteMethod="DeleteAreas"
        InsertMethod="InsertAreas" SelectMethod="SelectArea"
        TypeName="WebHelper.DataAccess.Db_Areas" UpdateMethod="UpdateAreas">
        <UpdateParameters>
            <asp:Parameter Name="AreaId" Type="Int32" />
            <asp:Parameter Name="Description" Type="String" />
            <asp:Parameter Name="Name" Type="String" />
            <asp:ControlParameter ControlID="fv_Group$EnabledCheckBox" PropertyName="Checked"  Name="Enabled" Type="Boolean" />
            <asp:Parameter Direction="Output" Name="Error" Type="Boolean" />
            <asp:Parameter Direction="Output" Name="Message" Type="String" Size="255" />
        </UpdateParameters>
    </asp:ObjectDataSource>

Data Access Layer:

        <DataObjectMethod(DataObjectMethodType.Update)> _
        Shared Function UpdateAreas(ByVal AreaId As Int32, ByVal Description As String, ByVal Enabled As Boolean, _
                                    ByVal Name As String, ByRef [Error] As Boolean, ByRef Message As String) As Integer

            Dim connectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("connString").ConnectionString
            Dim dbConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)

            Dim dbCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand
            dbCommand.CommandText = "app_Areas_Update"
            dbCommand.CommandType = CommandType.StoredProcedure
            dbCommand.Connection = dbConnection

            Dim dbParam_AreaId As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter
            dbParam_AreaId.ParameterName = "@AreaId"
            dbParam_AreaId.Value = AreaId
            dbParam_AreaId.DbType = DbType.Int32
            dbParam_AreaId.Direction = ParameterDirection.Input
            dbCommand.Parameters.Add(dbParam_AreaId)

            Dim dbParam_Description As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter
            dbParam_Description.ParameterName = "@Description"
            dbParam_Description.Value = Description
            dbParam_Description.DbType = DbType.String
            dbParam_Description.Direction = ParameterDirection.Input
            dbCommand.Parameters.Add(dbParam_Description)

            Dim dbParam_Enabled As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter
            dbParam_Enabled.ParameterName = "@Enabled"
            dbParam_Enabled.Value = Enabled
            dbParam_Enabled.DbType = DbType.Boolean
            dbParam_Enabled.Direction = ParameterDirection.Input
            dbCommand.Parameters.Add(dbParam_Enabled)

            Dim dbParam_Name As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter
            dbParam_Name.ParameterName = "@Name"
            dbParam_Name.Value = Name
            dbParam_Name.DbType = DbType.String
            dbParam_Name.Direction = ParameterDirection.Input
            dbCommand.Parameters.Add(dbParam_Name)

            Dim dbParamOut_Error As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter
            dbParamOut_Error.ParameterName = "@Error"
            dbParamOut_Error.Direction = ParameterDirection.Output
            dbParamOut_Error.DbType = DbType.Boolean
            Dim pError As SqlParameter = dbCommand.Parameters.Add(dbParamOut_Error)

            Dim dbParamOut_Message As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter
            dbParamOut_Message.ParameterName = "@Message"
            dbParamOut_Message.Direction = ParameterDirection.Output
            dbParamOut_Message.DbType = DbType.String
            Dim pMessage As SqlParameter = dbCommand.Parameters.Add(dbParamOut_Message)

            dbConnection.Open()

            Try
                dbCommand.ExecuteNonQuery()
                [Error] = Convert.ToBoolean(pError.Value)
                Message = Convert.ToString(pMessage.Value)
            Catch
                [Error] = True
                Message = "An unhandled exception occurred."
            Finally
                dbConnection.Close()
            End Try

            Return True
        End Function

Added on 10 Jul 2008

Solution Summary:

Varchar and Nvarchar Output parameters require size property to be defined.

Solution Details:

Simply specify the size for Varchar and Nvarchar output parameters. If the size is not defined, the size will default to Zero.

In the above example the @Message outuput parameter needs to have its size specified.

            Dim dbParamOut_Message As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter
            dbParamOut_Message.ParameterName = "@Message"
            dbParamOut_Message.Direction = ParameterDirection.Output
            dbParamOut_Message.DbType = DbType.String
            dbParamOut_Message.Size = 255
            Dim pMessage As SqlParameter = dbCommand.Parameters.Add(dbParamOut_Message)

Was this solution useful? Yes No

Added on 10 Jul 2008

Rating: