Why is sql server giving a conversion error when submitting date.today to a datetime column?
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)
开发者_如何学C 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.
Try
DateTime.Now()
Or
DateTime.Today;
Instead of
Date.Today
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)"
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)"
精彩评论