Solved

How to set smalldatetime field to null in database using EntityDataSource and FormView?

Posted on 2009-04-13
4
1,719 Views
Last Modified: 2013-11-11
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=N'Smith',@3=N'123 Market Street',@4=N'Springfield',@5=N'Illinois',@6=N'61107',@7=N'815-222-5599',@8=N'MollyR001@site.com',@9='1981-05-15 00:00:00',@10=N'',@11=N'',@12=5

Here is the EntityDataSource definition:

<asp:EntityDataSource ID="edsProspect" runat="server" ConnectionString="name=FiresideDnnEntitiesCn" DefaultContainerName="FiresideDnnEntities" EnableInsert="True" EnableUpdate="True" EntitySetName="JOINELIST" Where="it.JOINELISTID = @id">
 <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

0
Comment
Question by:rdogmartin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:Kyle_BCBSLA
ID: 24286361
Are you using an ADO.Net Entity Data Model?  
0
 
LVL 6

Author Comment

by:rdogmartin
ID: 24290178
Yes I am.
0
 
LVL 2

Expert Comment

by:Kyle_BCBSLA
ID: 24291567
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...  
0
 
LVL 6

Accepted Solution

by:
rdogmartin earned 0 total points
ID: 24296379
Yes, the column is set to nullable, both in the data model and in the database. The problem seems to be with EntityDataSource rather than the entity model.

In the end, I have a workaround that manually sets the field to null after the EntityDataSource performs the update. I assigned an event handler to the Inserted and Updated events as seen below. As you can see in the code, the entity correctly has the null value, but for some reason the EntityDataSource decides not to include that column in the auto-generated SQL.

protected void edsProspect_Inserted(object sender, EntityDataSourceChangedEventArgs e)
{
  PerformPostUpdateProcessing(e);
}
 
protected void edsProspect_Updated(object sender, EntityDataSourceChangedEventArgs e)
{
  PerformPostUpdateProcessing(e);
}
 
private void PerformPostUpdateProcessing(EntityDataSourceChangedEventArgs e)
{
	// For some reason the EntityDataSource will not set the datetime field to null when a user clears one of
	// the date textboxes; the field is left out of the auto-generated UPDATE statement, thus leaving the original
	// value in the database. To fix this, we explicitly load the item and set the fields to null if needed.
	var prospectThatWasUpdated = (JOINELIST)e.Entity;
 
	int id = Convert.ToInt32(Request.QueryString["id"]);
 
	if (id > 0)
	{
		using (FiresideDnnEntities ctx = new FiresideDnnEntities())
		{
			var prospect = (from n in ctx.JOINELIST where n.JOINELISTID == id select n).First();
 
			if (prospectThatWasUpdated.Birthday == null)
				prospect.Birthday = null;
 
			ctx.SaveChanges();
		}
	}
}

Open in new window

0

Featured Post

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question