?
Solved

Insert Null Date value in Access Database

Posted on 2007-08-07
4
Medium Priority
?
486 Views
Last Modified: 2013-11-05
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.
0
Comment
Question by:jholmes724
4 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 19650530
Can you post the code for your PassDateToInsertQuery function?
0
 
LVL 39

Expert Comment

by:appari
ID: 19650577
did you try passing
DBNull.value
0
 
LVL 58

Accepted Solution

by:
harfang earned 375 total points
ID: 19650877
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
 
LVL 27

Expert Comment

by:Sammy Ageil
ID: 19651622
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 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