Dates and Null Values

eliza · September 30th, 2005, 08:44 AM

Can anyone shed light on how to allow null values for dates using ASP.NET2 FormView Data Control? Is it possible to step into the Update Command and create an optional statement when using the data controls?

I have a form where several dates are optional. Whenever the field is blank or if you type-in or use the calendar contorl to insert a standard #/##/#### date I get the following error "The string was not recognized as a valid DateTime" error. Database is SQL fields are smalldatetime.

Any help or direction would be greatly appreciated.

eliza · September 30th, 2005, 04:23 PM

I found the code on another Forum compliments of Neil. Neil whoever you are THANK YOU!!! I have been trying to figure it out for days. Here is the code, so hopefully you or someone else doesn't have to waste valuable time trying to figure it out....

In order to allow null dates using Asp.net 2 you have to provide Event Handlers for the Item Updating and Item Inserting Events....

Code:

 Protected Sub FormView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewUpdateEventArgs) Handles FormView1.ItemUpdating

        If e.NewValues("DateField").ToString() = "" Then
            e.NewValues("DateField") = DBNull.Value
        End If

    End Sub

    Protected Sub FormView1_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertEventArgs) Handles FormView1.ItemInserting
        If e.Values("DateField").ToString() = "" Then
            e.Values("DateField") = DBNull.Value
        End If
    End Sub


<asp:FormView ID="FormView1" runat="server"   DataKeyNames="EventID" OnItemInserting="FormView1_ItemInserting" OnItemUpdating="FormView1_ItemUpdating" DataSourceID="AccessDataSource1"
     Width="85%">

aish · October 4th, 2005, 04:46 AM

I have a same problem. I tried to use DBNull.Value then the database update like this "01/01/1900" insted of "<Null>" but I want to add "<Null>". how can I do this? how is your output? is it 01/01/1900 or "<Null>" ? :(

Imar · October 4th, 2005, 05:09 AM

Can you show us the code you use to update the database?

And does the column in the database have a default value?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

aish · October 4th, 2005, 06:07 AM

no,there's no defalut value in the table coloum.
this is my code(this is test code).

string Mydate;

if(cboDateofBirth_Day.SelectedIndex!=0 && cboDateofBirth_Month.SelectedIndex!=0 && cboDateofBirth_Year.SelectedIndex!=0)
{
Mydate=cboDateofBirth_Day.SelectedValue+"/"+cboDateofBirth_Month.SelectedValue+"/"+cboDateofBirth_Year.SelectedValue;

}
else
{
Mydate=DBNull.Value.ToString();

}
command=sqlConnection.CreateCommand();
command.CommandText="set dateformat dmy;exec p_Insert_Test '"+Mydate+"' ";
sqlConnection.Open();
command.ExecuteNonQuery();
Response.Write("Save");

previously I used Mydate as a datetime then system did not allow me to add 25/12/2005 type of values, I want to add datetime dd/MM/yyyy format.

Imar · October 4th, 2005, 06:37 AM

DBNull.Value only makes sense when you pass it as an object to, for example, a parameter for a stored procedure. In your case, you try to pass it as a string representation using ToString which probably won't work.

Since you're using a direct in-line SQL statement, you can simply pass null like this:

"UPDATE MyTable SET SomeColum = NULL"

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Polar_Bear · February 2nd, 2007, 01:18 PM

Three cheers for Neil! I had a problem with a date in a DetailsView, but the DBNull.Value bit worked like a charm.