[RESOLVED] default date vs null

[Pasvorto]

I have the following code:

 If Trim(txtConfirmationDate.Text) <> "" Then
     sqlinsert2.Parameters.AddWithValue("@CONFIRMDATE", CDate(txtConfirmationDate.Text))
Else
     sqlinsert2.Parameters.AddWithValue("@CONFIRMDATE", VB.vbNull)
End If

Unfortunately, it writes "01/01/1900" to the database. How do I get it to just leave the field as NULL?

[Hack]

Why not try the easy way and do

Else
    sqlinsert2.Parameters.AddWithValue("@CONFIRMDATE", "None")

[sparrow1]

Hi,

You can find some information, here.

[Pasvorto]

Hack: The field in the db is set up as a date format. Won't "NONE" give me a type mismatch error?

[Hack]

Hack: The field in the db is set up as a date format. Won't "NONE" give me a type mismatch error?

Then drop the Else clause and let the database handle it.

If Trim(txtConfirmationDate.Text) <> "" Then
     sqlinsert2.Parameters.AddWithValue("@CONFIRMDATE", CDate
End If

[Pasvorto]

Due to how the Insert command is constructed, wouldn't I have to account for that field?

sqlinsert2.CommandText = "INSERT INTO POHEADER ([PONUM],[PODATE],[REVISION],[REVDATE],[PAGE],[FREIGHT],[BUYER],[BPHONE],[BEMAIL],[BFAX],[VENDOR1],[VENDOR2],[VENDOR3],[VENDOR4],[CONTACT],[ITEMS],[STATUS],[CUSTOMER],[SALESREP],[COMMENTS],[FOB],[CONFIRMDATE]) "

sqlinsert2.CommandText &= "VALUES (@PONUM,@PODATE,@REVISION,@REVDATE,@PAGE,@FREIGHT,@BUYER,@BPHONE,@BEMAIL,@BFAX,@VENDOR1,@VENDOR2,@VENDOR3,@VENDOR4,@CONTACT,@ITEMS,@STATUS,@CUSTOMER,@SALESREP,@COMMENTS,@FOB,@CONFIRMDATE)"

[wild_bill]

Try this

Dim confirmationDate As Date
If Date.TryParse(txtConfirmationDate.Text.Trim, confirmationDate) Then
    sqlinsert2.Parameters.AddWithValue("@CONFIRMDATE", confirmationDate)
Else
    sqlinsert2.Parameters.AddWithValue("@CONFIRMDATE", DBNull.Value)
End If

[kleinma]

vbNull returns nothing, which for dates will give you their default value.

Perhaps you want to use DBNull.Value?

Or a nullable datetime instance.

[Pasvorto]

I will give that a try, Wild Bill. Thanks all.