Why is sql server giving a conversion error when submitting date.today to a datetime column?

Question

I am getting a conversion error every time I try to submit a date value to sql server. The column in sql server is a datetime and in vb I'm using Date.today to pass to my parameterized query. I keep getting a sql exception

Conversion failed when converting datetime from character string.

Here's the code

Public Sub ResetOrder(ByVal connectionString As String)
        Dim strSQL As String
        Dim cn As New SqlConnection(connectionString)
        cn.Open()
        strSQL = "DELETE Tasks WHERE ProjID = @ProjectID"
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.AddWithValue("ProjectID", 5)
        cmd.ExecuteNonQuery()

        strSQL = "INSERT INTO Tasks (ProjID, DueDate, TaskName) VALUES " & _
        " (@ProjID, @TaskName, @DueDate)"
        Dim cmd2 As New SqlCommand(strSQL, cn)
        cmd2.CommandText = strSQL

        cmd2.Parameters.AddWithValue("ProjID", 5)
        cmd2.Parameters.AddWithValue("DueDate", Date.Today)
        cmd2.Parameters.AddWithValue("TaskName", "bob")
        cmd2.ExecuteNonQuery()
        cn.Close()
        DataGridView1.DataSource = ds.Projects
        DataGridView2.DataSource = ds.Tasks
    End Sub

Any thoughts would be greatly appreciated.

Answer 1

Try

DateTime.Now()

Or

DateTime.Today;

Instead of

Date.Today

Answer 2

Could be an issue with different regional settings between your application and SQL server.

Although it doesn't resolve the underlying issue, a straight-forward way to avoid this is by explicitly formatting the date you pass to SQL

cmd2.Parameters.AddWithValue("DueDate", DateTime.Today.ToString("yyyyMMdd"));

EDIT: I read your code more carefully: you've got your parameters and values in different orders. Try:

strSQL = "INSERT INTO Tasks (ProjID, DueDate, TaskName) VALUES " & _
" (@ProjID, @DueDate, @TaskName)"

Answer 3

Parameters were in the wrong order. Doh!

@TaskName and @DueDate were swapped. I believe all of the above suggestion are correct.

strSQL = "INSERT INTO Tasks (ProjID, DueDate, TaskName) VALUES " & _         " (@ProjID, @TaskName, @DueDate)"