Using Date with nullables

[PedroA]

Hi,

I have a MS Access table. One of the field's data types is set as 'Date/Time'. This cannot be changed.

I want to update this field based on a text box value. If the text box value = "" then I want to put this or nothing in the database.

I was looking at using nullables but ran into the error 'Data type mismatch in criteria expression.'

This is the code that I have been trying:

[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Dim [FONT=Consolas][SIZE=2]date2 [/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]DateTime[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]?[/SIZE][/FONT][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]date2 = Nothing
[/SIZE][/FONT][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]
Query = [FONT=Consolas][SIZE=2][COLOR=#a31515]"UPDATE tdata SET dob = '"[/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] & date2 & [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]"' "[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] + tcondition[/SIZE][/FONT][/SIZE][/FONT]
cmd = [COLOR=#0000ff]New[/COLOR][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]OleDbCommand[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2](Query, conn_tdata)[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]cmd.ExecuteNonQuery()
[/SIZE][/FONT][/SIZE][/FONT][/SIZE][/FONT][/SIZE][/FONT][/SIZE][/FONT]

Any suggestion here will be greatly appreciated.

Thanks

 

[jmcilhinney]

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

To do it properly, your code should look something like this:

Dim dob As Date?

If Me.dobTextBox.TextLength > 0 Then
    dob = Date.Parse(Me.dobTextBox.Text)
End If

Dim cmd As New OleDbCommand("UPDATE tdata SET dob = @dob ")
Dim prm = cmd.Parameters.Add("@dob", OleDbType.Date)

If dob.HasValue Then
    prm.Value = dob.Value
Else
    prm.Value = DBNull.Value
End If

Keep in mind that that will fail if the TextBox doesn't contain a valid representation of a date, so you probably ought to use TryParse or TryParseExact unless you've already validated. With that in mind, I would recommend using a DateTimePicker instead of a TextBox. You can configure it to display a check box and then use whether or not that is checked to decide whether to use its Value or not, e.g.

Dim cmd As New OleDbCommand("UPDATE tdata SET dob = @dob ")
Dim prm = cmd.Parameters.Add("@dob", OleDbType.Date)

If dobDateTimePicker.Checked Then
    prm.Value = dobDateTimePicker.Value
Else
    prm.Value = DBNull.Value
End If

 

[PedroA]

Thanks a lot

I'll check it out.