insert Null values into DateTime column

[sukarso]

Hi, Guys..

I'm trying to insert null value into datetime column in SQL, this scenario when no date was entered, so let the column's date inserted by null value. Basically there is no error when inserting process, but SQL will convert it to "1900-01-01 00:00:00.000" instead what i need just "NULL".

sqlstring = "INSERT INTO TBL_TRANSACTION (TransNo, TransDate, ItemCode, Qty, DateReq) " & _
                    "VALUES ('" & txtTransNo.Text & "', " _
                            & "'" & txtTransDate.Text & "'," _
                            & "'" & txtItemCode.Text & "'," _
                            & "" & txtQTY.Text & "," _
                            & "'" & IIf(IsDBNull(txtDateReq.Text) = True, DBNull.Value, txtDateReq.Text & "')"

any help will be greatly appreciated..

Thanks..

 

[jmcilhinney]

Don't ever use string concatenation like that to insert values into SQL code. ALWAYS use parameters. To learn why and how, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET.

You are right to use DBNull.Value but it will only worl if you do use parameters. Because you're using string concatenation, DBNull.Value would be converted to a String, which results in an empty string. Also, the Text of a TextBox can never be DBNull so that test is never going to be True. Furthermore, we don't use IIf anymore. Finally, why use a TextBox at all when you're dealing with dates? Use a DateTimePicker, which was created for that purpose. You can set the ShowCheckBox property to True and then use the check box to determine whether it contains a value or not.

Putting all that together, you can end up with code like this:

mySqlCommand.Parameters.Add("@DateReq", SqlDbType.DateTime2).Value = If(Me.DateTimePicker1.Checked, Me.DateTimePicker1.Value, CObj(DBNull.Value))

If the DTP is checked then use the Value, which is a Date object, otherwise use NULL.

 

[sukarso]

Hi, jmcilhinney..
Thanks for your reply and suggestion, I will work around with all those thing and fix it. I used text box to display date from DTP, when DTP change value will be passed it to text box and set DTP.Visible=False. Meanwhile when DTP is not checked the text box is letting it blank.

Have a nice day..

 

[jmcilhinney]

Why use a TextBox at all? Just display the DateTimePicker and be done with it. If you really want a blank control then you can use the formatting properties of the DTP to display nothing when the control is unchecked.

 

[jmcilhinney]

E.g.

Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles DateTimePicker1.ValueChanged
    With Me.DateTimePicker1
        If .Checked Then
            .Format = DateTimePickerFormat.Short
        Else
            .CustomFormat = " "
            .Format = DateTimePickerFormat.Custom
        End If
    End With
End Sub

 

[sukarso]

Dear, jmcilhinney.

I have followed as you suggested above, but the result looks like still the same, which is Null Value can't be inserted to datetime field in SQL server. Any idea? or what i've done still missing?

Dim sql As String = "INSERT INTO TBL_TRANSACTION (TransNo, TransDate, ItemCode, Qty, DateReq) " & _
       "VALUES (@TransNo, @TransDate, @ItemCode, @Qty, @DateReq)"


Dim myCommand As New SqlCommand(sql, CN)
        With myCommand.Parameters

            .Add("@TransNo", SqlDbType.NChar, 10)
            .Add("@TransDate", SqlDbType.DateTime)
            .Add("@ItemCode", SqlDbType.NVarChar, 50)
            .Add("@Qty", SqlDbType.Float)
            .Add("@DateReq", SqlDbType.DateTime)

        End With

        myCommand.Parameters("@TransNo").Value = txtTransNo.Text.ToString
        myCommand.Parameters("@TransDate").Value = DTP_Req.Value
        myCommand.Parameters("@ItemCode").Value = txtItemCode.Text.ToString
        myCommand.Parameters("@Qty").Value = txtQty.Text.ToString
        myCommand.Parameters("@DateReq").Value = If(Me.DTP_Req.Checked, Me.DTP_Req.Value, CObj(DBNull.Value))

        myCommand.ExecuteNonQuery()
        MessageBox.Show("Data Inserted")

Here is the capture of sql's table:




Thanks for your time.

 

[jmcilhinney]

Um, you have no check box displayed on your DateTimePicker so Checked will always be True, so you will always save a value. If you want Checked to be False then you have to show the check box and uncheck it.

 

[sukarso]

:agree:..yes..it is working fine, null value inserted in sql field.

Thank you very much for your help.:beer: