Setting Database Values to their uninitialized Sta

Question:

When I ' m inserting blank value to a SQL Server database, for a DateTime column, my database it is inserting 1/1/1900 Eve N if I assign Null to the variable in my application. This is example:


string xx = null;
T-sql = "INSERT into temptable (datetimecolumn) VALUES (xx);"

Can you help me with this problem?

& Regards,

Ravi

Answer:

Ravi, you ' re in luck. This is actually a easy fix-you just have to know what to doing (and unfortunately sometimes finding the right thing Todo is half the battle).

When you are inserting data to a database, the ado.net data providers and your database may distinguish between a null O Bject and an uninitialized value. In your case, inserting a null into a DateTime column causes the "Database to seed" field with the default initialized V alue-1/1/1900. In reality, you want to tell the database, the field in question should remain uninitialized. To do so there is a singleton class in the. NET Framework This is designed for passing to a database to represent a uni nitialized value. that class is system.dbnull; and specifically the Value of the class.

To insert a in your database, and maintain the uninitialized state of the DateTime fields your 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'll actually perform checks on the ' value passed to me insert or update operation to determine if DBNULL.V Alue should is sent to the stored procedure I am invoking. If you are were to spend some time looking at the "Code I" write in "The Real world", you would the "like" (This is C Ode 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 and 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.