Insert Null Date value in Access Database

I am using an MS Access Database but this question also pertains to SQL Server as well.

I have a date field in the database which is not required, as sometimes a date is not needed.  So in my code I have something like this:

protected void InsertDate()
{

      DateTime _dateUpdated  // create DateTime Variabble
     if(txtUpdateDate.Text == "")
     {
           _dateUpdated = null;
     }
     else
     {
           _dateUpdated = Convert.ToDateTime(txtUpdateDate.Text);
     }

     PassDateToInsertQuery(_dateUpdated);

}

basically what I am trying to get across is that when I try to assign a null value to DateTime I get an error.  I want to know the best method to pass a null value to an insert query to have it insert a null value for a Date Field.  I am sure this is a common occurance but it is the first time I have had to actually deal with it.
jholmes724Asked:
Who is Participating?
 
harfangConnect With a Mentor Commented:
That really depends on how you pass the value to the database.

If you have direct access to a field (e.g. using DAO, ADO, ODBC), you can set the field's value property to null.

    fldCurrentField = null;

However, your function name suggests an insert query. You will have to build your query to look like this:

Access:

    INSERT INTO tblSomeTable(TextField, NumericField, DateField)
    VALUES( 'some text', 123, #2007-08-08# )

SQL-Server

    INSERT INTO tblSomeTable(TextField, NumericField, DateField)
    VALUES( 'some text', 123, '2007-08-08' )

or

    INSERT INTO tblSomeTable(TextField, NumericField, DateField)
    VALUES( 'some text', 123, cast('2007-08-08' as datetime) )

In both engines, you insert a null value by using the 'Null' keyword:

    INSERT INTO tblSomeTable(TextField, NumericField, DateField)
    VALUES( Null, Null, Null )

In other words, when building your SQL insert query, generate a string for your date value: either a string representing the date using the correct delimiters, or the string 'null' without any delimiters, so that the engine will recognize the keyword.

Cheers!
(°v°)
0
 
mbizupCommented:
Can you post the code for your PassDateToInsertQuery function?
0
 
appariCommented:
did you try passing
DBNull.value
0
 
SammyCommented:
You can set the value of _dateUpdated to DBNull.value or a better option is to use DateTime.MinValue and check against the MinValue of the datetime field

protected void InsertDate()
{

      DateTime _dateUpdated  // create DateTime Variabble
     if(txtUpdateDate.Text == "")
     {
           _dateUpdated = DateTime.MinValue;
     }
     else
     {
           _dateUpdated = Convert.ToDateTime(txtUpdateDate.Text);
     }

     PassDateToInsertQuery(_dateUpdated);

}
and in your  PassDateToInsertQuery method you check if the passed datetime has the MinValue, if yes then replace it with a DBNull.value and send it to the DB


Hope this helps

Sammy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.