How to set smalldatetime field to null in database using EntityDataSource and FormView?
I used the Entity Framework in .NET 3.5 SP1 to build an entity object from a table where one of the columns is a nullable smalldatetime. This column - among others - is exposed on a web page using a FormView with an EntityDataSource providing the plumbing. When the user is editing an existing record and deletes the date (that is, clears the TextBox), I need the column to be set to null in the database. Currently, the field is not included in the auto-generated UPDATE statement. (I used SQL Profiler to inspect it). It appears the SQL only includes fields that are mapped to non-empty textboxes.
I tried intercepting the FormView's ItemUpdating event and manually setting the field to null, but no SQL is generated for that field. Here is some example SQL that is generated after clearing the date text box and clicking save. The database column that should be set to null is named [Anniversary], and notice it is not mentioned anywhere:
exec sp_executesql N'update [dbo].[JOINELIST]
set [Customer Number] = @0, [First Name] = @1, [Last Name] = @2, [Address] = @3, [City] = @4, [State] = @5, [Zip] = @6, [Phone] = @7, [Email] = @8, [Birthday] = @9, [Spouse First Name] = @10, [Spouses Last Name] = @11
where ([JOINELISTID] = @12)
',N'@0 nvarchar(4000),@1 nvarchar(5),@2 nvarchar(7),@3 nvarchar(17),@4 nvarchar(8),@5 nvarchar(8),@6 nvarchar(5),@7 nvarchar(12),@8 nvarchar(17),@9 datetime,@10 nvarchar(4000),@11 nvarchar(4000),@12 int',@0=N'',@1=N'Molly',@2
Here is the EntityDataSource definition:
<asp:EntityDataSource ID="edsProspect" runat="server" ConnectionString="name=Fir
<WhereParameters>
<asp:QueryStringParameter DbType="Int32" Name="id" QueryStringField="id" />
</WhereParameters>
</asp:EntityDataSource>
How do I force the EntityDataSource to set nullable fields to null when the TextBox is empty?
Roger
I tried intercepting the FormView's ItemUpdating event and manually setting the field to null, but no SQL is generated for that field. Here is some example SQL that is generated after clearing the date text box and clicking save. The database column that should be set to null is named [Anniversary], and notice it is not mentioned anywhere:
exec sp_executesql N'update [dbo].[JOINELIST]
set [Customer Number] = @0, [First Name] = @1, [Last Name] = @2, [Address] = @3, [City] = @4, [State] = @5, [Zip] = @6, [Phone] = @7, [Email] = @8, [Birthday] = @9, [Spouse First Name] = @10, [Spouses Last Name] = @11
where ([JOINELISTID] = @12)
',N'@0 nvarchar(4000),@1 nvarchar(5),@2 nvarchar(7),@3 nvarchar(17),@4 nvarchar(8),@5 nvarchar(8),@6 nvarchar(5),@7 nvarchar(12),@8 nvarchar(17),@9 datetime,@10 nvarchar(4000),@11 nvarchar(4000),@12 int',@0=N'',@1=N'Molly',@2
Here is the EntityDataSource definition:
<asp:EntityDataSource ID="edsProspect" runat="server" ConnectionString="name=Fir
<WhereParameters>
<asp:QueryStringParameter DbType="Int32" Name="id" QueryStringField="id" />
</WhereParameters>
</asp:EntityDataSource>
How do I force the EntityDataSource to set nullable fields to null when the TextBox is empty?
Roger
Kyle_BCBSLA
Are you using an ADO.Net Entity Data Model?
ASKER
Yes I am.
Have you checked your data model? Make sure that the column property for nullable is set to true. If it is not and it is in your database then make sure to update you data model. Right-click on the model and select update data model. You do not need to do anything special for null values to be put into the database. If you have a null value in your control then "null" is put into your database. Make sure your model is correct. Let me know your findings...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.