[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Forcing a Null value for a date in Insert Statement

Posted on 2006-05-05
Medium Priority
Last Modified: 2008-02-01
I am trying to Insert a row and within the list of elements (columns) being inserted is a date ina textbox going to a datetime column in sql server.  I am checking the checked property of a companion (linked) control to see if checked.  If it is not, then I set the textbox = "".  On sql Server side I set to allow nulls.  When I insert with textbox = "", I get 1/1/1900.  I would like to have null.  Is there an easy way to do this.  The reason for dtime picker and textbox is that the dtp is not nullable and I just using a textbox to prevent it and update from the textbox but user will work off datetime picker.

SpecificallY, Can I get rid of the 1/1/1900 and replace with null
Question by:garyinmiami2003
  • 3
  • 2
  • 2
  • +1
LVL 70

Expert Comment

by:Éric Moreau
ID: 16616949
Hi garyinmiami2003,

You can handle NULL in a DateTimePicker. See an article I wrote in January 2005 at http://emoreau.s2i.com/


Expert Comment

ID: 16617213
i don't know how your insert statement is built and structured, but you can use the word NULL as in
INSERT INTO table VALUES (val1,val2,NULL,'val3', etc ...)

is that what you're after?


Author Comment

ID: 16617297
I'm tryint to use what Eric provided.  Using the Null in the insert does not look good to me.  I have too many combinations.  Anyone have a nice easy way?  
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!

LVL 34

Accepted Solution

Sancler earned 600 total points
ID: 16617556
Assuming that (1) as you are talking about an Insert, this starts off as a new row in a DataTable in your app, and (2) from what I remember from previous questions, you are using a dataadapter to communicate with your database ...

Set the default value of the relevant DataColumn to DBNull, do not bind the column to anything, and simply leave it untouched unless you get a valid date that you want to pass to the database.  Then let the dataadapter do the work.


Author Comment

ID: 16617758

I'm not using a data adapter but an Insert SQL stmt.  I had set up a textbox for each date time picker control and used the checkbox.  I believe I will not have a problem with the users over the checkbox.  The problem I have with that is on an add, How can a set the value of a variable to dbnull when no date was selected, allowing the use of only one insert.  I am just evaluating the checkbox for each dtp, and setting the companion textbox as need be.  The problem is when no date is selected,  how to pass along a dbnull.  I don't want 4-5 insert statements.  Can you help me with the dbnull problem?

Eric's app is difficult for me to use.

Assisted Solution

g_johnson earned 200 total points
ID: 16617936
if you use a parametrized insert statement, then you will have only one "if" as you're setting parameters to decide if you are passing an actual date or a null

Author Comment

ID: 16618002
ctInsCust.CommandText = "Insert Into Customers " _
        & " Mail_Out_Date, Followup_Date, Mark_Coordinator_ID, Referred_By, AppRcvd ) " _
        & "Values (" _
         & "'" & txtCMMail.Text & "', " _
        & "'" & txtCMFollow.Text & "', " _
         & "'" & cbCMCoord.SelectedValue & "', " _
        & "'" & cbCMReferBy.SelectedValue & "', " _
        & "'" & cbCMAppRec.SelectedValue & "') "
        '  & "'"

This is part of the insert..  txtCMMail & txtCMFollow are textboxes that I put the values from date time picker controls.  I am evaluating the checkboxes to determine what to put in there.  What I want to know how to do, if easily done is how to pass a dbnull thru the insert to store in "Mail_Out_Date" and Followup_Date.  When I pass textbox.text = "", I get 1/1/1900 as default on sql server.

I want null  
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 1200 total points
ID: 16618006
>> but an Insert SQL stmt
>> I don't want 4-5 insert statements

How do you construct your insert statement? It could be something like this (say your date goes into field 3):

strsql = "insert into tablename (field1, field2, field3) values("
strsql += YourValue1
strsql += ", "
strsql += YourValue2
strsql += ", "
if YourDateTextBox.Text.length = 0 then
   strsql += "NULL"
   strsql += "'" + YourValue1 "'"
strsql += ")"

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month19 days, 12 hours left to enroll

872 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