Forcing a Null value for a date in Insert Statement
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
SpecificallY, Can I get rid of the 1/1/1900 and replace with null
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?
INSERT INTO table VALUES (val1,val2,NULL,'val3', etc ...)
is that what you're after?
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Roger:
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
& " 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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can handle NULL in a DateTimePicker. See an article I wrote in January 2005 at http://emoreau.s2i.com/
Cheers!