Setting Database Values to Their Uninitialized Sta
2024-07-21 02:15:30
供稿：網友
question:
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.
