Set a datatime column to NULL instead of default database value in ASP.NET


When you set a parameter value to empty string, the datetime column in the table may use 1/1/1900 as a value. It works in many cases but sometime you just want a NULL value in that place. Here is a code snippet in VB.NET to show how to update the column to NULL:

Dim ESOConnection As SqlConnection

Dim MyUpdate As SqlCommand

ESOConnection =

New SqlConnection(ConfigurationManager.ConnectionStrings("constring").ToString)

Dim strSQL As String = "UPDATE ARREST_INDEX SET LNAME = @LNAME,DOB = NULLIF(@DOB,”), " & _

"OCA = @OCA WHERE ARRESTNO = @ARRESTNO"

MyUpdate =

New SqlCommand(strSQL, ESOConnection)

MyUpdate.CommandType = CommandType.Text

MyUpdate.Parameters.AddWithValue(

"@LNAME", tx_lname.Text)

MyUpdate.Parameters.AddWithValue(

"@DOB", tx_dob.Text)

MyUpdate.Parameters.AddWithValue(

"@OCA", tx_oca.Text)

MyUpdate.Parameters.AddWithValue(

"@ARRESTNO", 1) ‘assign some value here

ESOConnection.Open()

MyUpdate.ExecuteNonQuery()

ESOConnection.Close()

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s