Problem with DateTime column

Hi All,
I faced this strange problem with sql server.
I have datetime column and storing value from asp.net. If the user doesnt enter any date, then we dont want any value to be stored in the database. But when we checked SQL Server, it default takes this value "01/01/1900". When users clicks on edit button, this value is fetched from database and stored in front end.
I dont want this value to be stored in database if i didnt provide value. But in 1 particular page, there are 5 different date columns. I am not sure about how to go ahead. I tried the following way, but again ended up with same problem.
insert into tablename......values (..., txtFromDate.text, txtToDate.text,...).
Since value is not there, '' is sent to database and above said value is stored. i want NULL to be stored in teh database when user didnt specify any value.
i didnt encounter this problem in Oracle.

appreciate your reply.

In the table design window for this table put a check in the column for allow nulls for the datetime field and make sure that you have not set the field with a default value. Also validate the textbox before data insertion or update like so.
if(txtFromDate.Text.Trim(' ') != "")
(
Place your database insertion code.
}
This will make sure that the textbox actually has something in it before you insert the value. Also you should have to convert the value of the textbox to a datetime before the data is inserted like this.
yadayadayada = Convert.ToDateTime(txtFromDate.Text);
I hope that this helps.

|||Check ifthisarticle helps.
|||

hi,
thanks for your reply. I thought of doing it like check the textbox value. But the problem is i am having 5 date control in that particular page.
So the condition for NULL checking, i need to try out with all probability. I believe, in that case, i should use around 25 if statement with each statement checking for all 5 condidtion.
Is there any other way to doing it.
rgds
ramu

|||you can check for nulls at the time of passing the value to the stored proc. you will have only 5 conditions. just before you pass the value to the parameter check for null. if you post some code we can help you out.|||You can just do iif(me.txtDate1.text.length=0,dbNull.value, me.txtDate1.text) if you are using parms. If not iif(me.txtDate1.text.length=0,"NULL", me.txtDate1.text)

Nick|||hi nick
dbnull.value does not seem to work. we need to use a special sqldatetime.null and import the sqltypes namespace for it.|||hi all,
once again, thanks for your reply.
since it was getting complicated and time was main constraint for me, so i tried it in the following way.
If the value is null, i let it get stored as 01/01/1900 itself.
but while fetching record for edit/view, i am checking those date columns for for above. If yes, i display null value, otherwise the stored value is displayed.
the above one temporarily solved my problem.
based on your solution, i think iif will solve my issue.
rgds
ramu|||

ndinakar wrote:

hi nick
dbnull.value does not seem to work. we need to use a special sqldatetime.null and import the sqltypes namespace for it.

Maybe its because of the way its being used? I always pass dbNull.value to my stored procs if the value is blank....

Nick|||It works for all columns except datetime columns. Leaving datetime columns empty will throw errors.