when i'm inserting blank value into a sql server database, for a datetime column, my database it is inserting 1/1/1900 even if i assign null to the variable in my application. here is an example:
string xx = null; //t-sql = "insert into temptable (datetimecolumn) values (xx);"
can you help me with this problem?
thanks & regards,
ravi
answer:
ravi, you're in luck. this is actually an easy fix - you just have to know what to do (and unfortunately sometimes finding the right thing to do is half the battle).
when you are inserting data into a database, the ado.net data providers and your database may distinguish between a null object and an uninitialized value. in your case, inserting a null into a datetime column causes the database to seed the field with the default initialized value - 1/1/1900. in reality you want to tell the database that the field in question should remain uninitialized. to do that there is a singleton class in the .net framework that is designed for passing to a database to represent an uninitialized value. that class is system.dbnull; and specifically the value property of the class.
to insert a record into your database, and maintain the uninitialized state of the datetime fields you would do something like this:
[c#] sqlcommand cmd = new sqlcommand(); cmd.connection = con; cmd.commandtext = "insert into mytable (name, registereddate, canceldate) " + "values (@name, @registereddate, @canceldate)"; cmd.parameters.add("@name", "doug seven"); cmd.parameters.add("@registereddate", datetime.today); //use system.dbnull.value to leave the field uninitialized cmd.parameters.add("@canceldate", system.dbnull.value);
[visual basic .net] dim cmd as new sqlcommand() cmd.connection = con cmd.commandtext = "insert into mytable (name, registereddate, canceldate) " & _ "values (@name, @registereddate, @canceldate)" cmd.parameters.add("@name", "doug seven") cmd.parameters.add("@registereddate", datetime.today) 'use system.dbnull.value to leave the field uninitialized cmd.parameters.add("@canceldate", system.dbnull.value)
in many cases i will actually perform checks on the value passed to my insert or update operation to determine if dbnull.value should be sent to the stored procedure i am invoking. if you were to spend some time looking at the code i write in the real world, you would see something like this (this is code direct from a client application):
if user.firstname = string.empty then cmd.parameters("@firstname").value = system.dbnull.value else cmd.parameters("@firstname").value = user.firstname end if
if user.lastname = string.empty then cmd.parameters("@lastname").value = system.dbnull.value else cmd.parameters("@lastname").value = user.lastname end if
if user.registereddate = nothing then cmd.parameters("@registereddate").value = system.dbnull.value else cmd.parameters("@registereddate").value = user.registereddate end if
summary
to set values in a database to their uninitialized state, use the system.dbnull.value structure. you can pass this value in using a t-sql command, or a stored procedure - passing dbnull.value as a parameter.
