[RESOLVED] NULL DATETIME & SQL database

[thered]

Hi,
I am trying to get to grips with the “NULL” dates in SQL database.

Q1) The PrepareDateForDb code works OK on an update of a record, but when I try add a record with a null date it throws an exception?

Q2) The date time when I retrieve the datetime stored in the database as ToOADate format, the date time is a day out. I assume as I use a UK date time it is placing my date two hours behind. My customers may not have the same datetime location.

Thanks

This works OK

strSQL = "UPDATE [Business] SET" & _
...
" ,DateVerified = " & PrepareDateForDb(mtbDateVerified.Text) & _
...

This throws an exception

...
With cmSQL
...
.Parameters.Add(New SqlParameter("@DateVerified", SqlDbType.DateTime)).Value = PrepareDateForDb(mtbDateVerified.Text)

‘ Prepare date for inserting into database
    Function PrepareDateForDb(ByVal strValue As String) As String
        'Empty MaskedTextBox field gives “  /  /”
        If strValue.StartsWith(" ") Then
            Return "NULL"
            Exit Function
        End If
        'Empty date field
        If strValue.Trim() = "" Then
            Return "NULL"
        Else
            'Convert date to ToOADate 
            Dim strDt As String = CDate(strValue).ToOADate
            Return strDt
        End If
    End Function

[jmcilhinney]

You're specifying that your parameter is type DateTime yet you're assigning a String object to it. If the parameter is type DateTime then you need to assign a DateTime value to it. Also, a String containing "NULL" is absolutely NOT a null value. It is a string containing four characters.

Dim dt As Date
 
If Date.TryParse(mtbDateVerified.Text, dt) Then
    cmSQL.Parameters.AddWithValue("@DateVerified", dt)
Else
    cmSQL.Parameters.Add("@DateVerified", SqlDbType.DateTime).Value = DBNull.Value
End If

Better still, use a DateTimePicker control for dates and/or times. If you need to be able to specify null values then set its ShowCheckBox property to True:

If myDateTimePicker.Checked Then
    cmSQL.Parameters.AddWithValue("@DateVerified", myDateTimePicker.Value.Date)
Else
    cmSQL.Parameters.Add("@DateVerified", SqlDbType.DateTime).Value = DBNull.Value
End If

[thered]

Excellent thanks,
I will have to use the first option.

Thanks again; what would I do with out you