I recently ran into this problem when using a FormView and and ObjectDataSource where the FormView was binding to nullable data types…

Here is a challenging question, as I have not found any suitable information on this after two days of searching:

Background:

I have a custom Business Class library that includes custom objects with nullable int and DateTime properties. In order to update this data, I have created a FormView control bound to an ObjectDataSource that retrieves a generic List collection of my business objects. The reflection that is intrinsically called by the FormView/ObjectDataSource combination dutifully builds my basic Select, Update, and Insert templates. Now, when you edit the various (TextBoxes by default) input fields and then call the Update method referenced in the ODS (ObjectDataSource), the ODS attempts to convert the data contained in the input fields to the corresponding Type of the property bound to said input field. This is where we run into our little problem…

Even though I have a nullable Integer property type (let’s call it int? ClassNumber), if the corresponding TextBox is empty (since it’s not required), an exception is thrown by the ODS since it tries to convert an empty string value to an integer value before trying to set the object’s Property. Hah! If ODS was able to determine that the integer is in fact nullable, it should pass in a null value instead! But alas, it throws a System.IndexOutOfRangeException: “Index was outside the bounds of the array” error. And further down the stack: “Exception: is not a valid value for Int32″. Hmmm. Since this is a FormView control, we don’t have the ability to use a BoundField control with 2 very useful properties: NullDisplayText and ConvertEmptyStringToNull. It would be nice to tell ODS to enable sending null values from any control we want.

The above was posted originally here.  I used his clipping because it describes perfectly the problem I had, along with many, many others.

After hours and hours of Googling and trial and error I finally found the solution to this.  There was no way in hell I was going to accept the solution of manually re-populating each field I had bound in my edit and insert templates for my FormView.  The solution was much easier and left all of the heavy lifting to the FormView.

Basically, all you have to do is all insert and update parameters in your ObjectDataSource for each field you’re binding.  For these fields, you just need to supply the field name, the data type and set the ConvertEmptyStringToNull property to TRUE.  Also, you’ll need to be sure to set the ConvertNullToDBNul property to TRUE in the ObjectDataSource itself, like below.

<InsertParameters>
<asp:Parameter Name="TimeZoneOffset" Type="int32" ConvertEmptyStringToNull="true" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="TimeZoneOffset" Type="int32" ConvertEmptyStringToNull="true" />
</UpdateParameters>

So just by adding these parameters for the insert and update you can have your FormView’s databinding work with nullable types.  Not too bad, huh?

Comments (from previous blog):

Don,

Great! I am so glad you’ve found a solution to this annoying issue. Your solution is both simple and elegant. Since my posting (from which this post was based), I have by and large moved on from the FormView control in most cases and have instead used a number of different input methods, particular to the types of applications I have been building. This finding of yours may have sparked a new interest, and now I’ll have to play around with it some more. Thanks for posting a reply to my original post: (http://www.eggheadcafe.com/community/aspnet/2/81350/previous-community-conver.aspx).

—

I am having a problem like this one. However, I am using the Protected Sub GridView1_RowDataBound in order to control how the row will look like depending on the data. The problem is that when there is no data, I get this message:

Specified argument was out of the range of valid values.

It points to: datasch = e.Row.Cells(1).Text

Obvisly, if there is no data, there is no Row(1)… I tried to make a condition with that idea but nothen so far… help!!

—

Me again…

I foud this solution.

If e.Row.RowType = DataControlRowType.DataRow Then

‘all the code…

end if

Thanks for being part of the solution.

—

Gosh, thanks for your post. I hit into such problem and I was so puzzled for what was happening. For the reason that I had an ID field which supposed to be generated by the system. So, it was null during Insert. :-O

—

I ran into this issue as well and broke my head over it for a day or so. Then I discovered the FormView_OnItemInserting event and realized that I could validate the data there. I wrote code that changes empty strings to null for my decimal properties. Here’s the code:
protected void FormView1_ItemInserting(object sender, FormViewInsertEventArgs e)
{
//do the check for empty strings instead of decimals here and make the value null if necessary
// Iterate through the items in the Values collection
// if key=FeesFirst or key=FeesSecond and value=”", change value to null
foreach (DictionaryEntry entry in e.Values)
{
if (entry.Value.Equals(“”) && (entry.Key.ToString() == “FeesFirst” || entry.Key.ToString() == “FeesSecond”))
{
e.Values[entry.Key] = null;
}
}
}

—

Don,

I am having the same problem. But in my case I have a custom object as my parameter. I am using a data entity class to transport data between the layers in my application. I have the same scenario whereby I have bound my formview with an objectdatasource.

My update method in my business object looks a bit like this

Public Function Update(ByVal NewEmployee As Employee, ByVal OldEmployee As Employee) As Boolean

—

JP,

What do the parameters for your datasource look like?

—

Don,

The parameters for my datasource are the two objects that are being passed to my update function (NewEmployee and OldEmployee). The objectdatasource works with a list of individual parameters representing the individual fields in the database that you may want to update or it can also work with custom objects that holds these parameters as properties. In this case I have taken into consideration of optimistic concurrency. That is why I have two parameters which the objectdatasource picks up as objects. One passes in the new values and the other retains the old values. This works by setting the ConflictDetection property of the objectdatasource to CompareAllValues.

By the way for some reason the whole scenario works without a fuss with a details view. But I need the UI design flexibility of the formview

Thanks

—

Don

Here is a cut down version of the Employee object being passed as new and old parameters in my business object

”’ <summary>
”’ This is the class representing a single instance of Employee
”’ </summary>
”’ <remarks>Primary key of this class is <c>EmployeeId</c></remarks>
<Serializable()> _
Public Class Employee
Inherits BaseDataEntity
Implements IDataEntity

#Region ” Private Declarations ”
Private _EmployeeId As Nullable(Of Int32)
Private _OrgTreeId As Nullable(Of Int32)
Private _PayNumber As String = String.Empty
Private _FirstName As String = String.Empty
Private _LastName As String = String.Empty
#End Region

#Region ” Constructor/Initialisation ”
Sub New()
init()
End Sub

Protected Overrides Sub init()
‘ Initialization Implementation if needed
End Sub
#End Region

#Region ” Entity Primary Keys ”
<DataObjectField(True, True, False), Browsable(False), Description(“Employee Id”)> _
Public Property EmployeeId() As Int64 Implements IDataEntity.Id
Get
Return _EmployeeId.GetValueOrDefault(0)
End Get
Set(ByVal Value As Int64)
_EmployeeId = Value
End Set
End Property
#End Region

#Region ” Public Properties ”
<DataObjectField(False, False, False), Browsable(False), Description(“Org Tree Id”)> _
Public Property OrgTreeId() As Nullable(Of Int32)
Get
Return _OrgTreeId.GetValueOrDefault(0)
End Get
Set(ByVal Value As Nullable(Of Int32))
_OrgTreeId = Value
End Set
End Property

<DataObjectField(False, False, False, 50), Browsable(False), Description(“Pay Number”)> _
Public Property PayNumber() As String
Get
Return _PayNumber
End Get
Set(ByVal Value As String)
_PayNumber = Value
End Set
End Property

<DataObjectField(False, False, False, 50), Browsable(False), Description(“First Name”)> _
Public Property FirstName() As String
Get
Return _FirstName
End Get
Set(ByVal Value As String)
_FirstName = Value
End Set
End Property

<DataObjectField(False, False, False, 50), Browsable(False), Description(“Last Name”)> _
Public Property LastName() As String
Get
Return _LastName
End Get
Set(ByVal Value As String)
_LastName = Value
End Set
End Property

#End Region

End Class

—

Hi Don,

Have u managed to find a work around to my problem?

Regards

JP

—

JP,

I guess I didn’t understand completely what you were asking. In my example, the parameters were nullable database fields that I was trying to bind to in a formview. You indicated that your two parameters are custom objects. The two aren’t really the same thing.

Is your problem that you can’t bind to the properties of the custom objects or that your properties that the capability of being null aren’t retaining their null values?

—

JP, I had the same problem. I worked around the issue by using the Formview ItemUpdating event to see if the field value was String.Empty, if it was I set it to a null value. In my business object the fields are declared as Nullable<int>.

—

It is not working with DateTime data type. it is giving me following error
Object of type ‘System.DBNull’ cannot be converted to type ‘System.Nullable`1[System.DateTime]‘.

I have scenario as you have i have connected my objectdatasource with my formview and sending data from BLL to DAL. Can you suggest me any idea

—

Milind,

Based on the instructions in my original post, it should work fine with a datetime data type. You need to make sure that your insert and update parameters are correct. Here’s a snippet from an ObjectDataSource in one of my projects, which uses datetime datatypes. This works fine:

Keep in mind, I’ve removed some extra insert and update parameters to keep the comment shorter. They keys here to remember are to make sure you have ConvertNullToDbNull=”true” in the object datasource and to be sure to have your nullable field in the insert and/or update parameters collection with ConvertEmptyStringToNull=”true” set.

<asp:ObjectDataSource ID=”EmployeeInfoDataSource” runat=”server” DataObjectTypeName=”HeeHawApp.Entities.EmployeeInfoView”
InsertMethod=”InsertEmployeeInfo” SelectMethod=”GetEmployeeInfoByEmployeeId”
TypeName=”HeeHawApp.Business.EmployeeManager” UpdateMethod=”UpdateEmployeeInfo”
ConvertNullToDBNull=”true”>
<SelectParameters>
<asp:QueryStringParameter Name=”EmployeeId” QueryStringField=”EmployeeId” Type=”Int32″
DefaultValue=”0″ />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name=”EmployeeDOB” Type=”datetime” ConvertEmptyStringToNull=”true” />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name=”EmployeeDOB” Type=”datetime” ConvertEmptyStringToNull=”true” />
</UpdateParameters>
</asp:ObjectDataSource>

—

I tried to use a FormView and there is a checkbox field to display boolean value. It causes exception when the database value is null. How to solve this?

—

Thanks man, it solved my problem. I was just about to kick my computer

—

Can anyone help with this one? Similar problem, but I’m using a GridView. My Select method on the ObjectDataSource is taking it’s parameters from the QueryString.

Example Parameter:
<asp:QueryStringParameter DefaultValue=”Null” Name=”pItemNo” QueryStringField=”Item”
Type=”Int32″ ConvertEmptyStringToNull=”true” />

I also have ConvertNullToDBNull=”True” on the ObjectDataSource.

When I try to run it, I get the following:

[FormatException: Input string was not in a correct format.]
System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2755599
System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +112
System.String.System.IConvertible.ToInt32(IFormatProvider provider) +43
System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider) +293
System.Web.UI.WebControls.Parameter.GetValue(Object value, String defaultValue, TypeCode type, Boolean convertEmptyStringToNull, Boolean ignoreNullableTypeChanges) +264
System.Web.UI.WebControls.Parameter.get_ParameterValue() +66
System.Web.UI.WebControls.ParameterCollection.GetValues(HttpContext context, Control control) +254
System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +257
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

Thanks!!

-Brice

—

Never mind! I found my problem. I removed DefaultValue=”Null” from each of my parameters. Also, in my case, I needed to set ConvertNullToDBNull=”false” on the ObjectDataSource.

Thanks! Great Article!! It really helped!