Conversion failed: SqlParameter and DateTime
- by Tim
I'm changing old,vulnerable sqlcommands with SqlParameters but get a SqlException:
System.Data.SqlClient.SqlException {"Conversion failed when converting datetime from character string."} on sqlCommand.ExecuteScalar()
Dim sqlString As String = _
"SELECT TOP 1 " & _
"fiSL " & _
"FROM " & _
"tabData AS D " & _
"WHERE " & _
"D.SSN_Number = '@SSN_Number' " & _
"AND D.fiProductType = 1 " & _
"AND D.Repair_Completion_Date > '@Repair_Completion_Date' " & _
"ORDER BY " & _
"D.Repair_Completion_Date ASC"
Dim obj As Object
Dim sqlCommand As SqlCommand
Try
sqlCommand = New SqlCommand(sqlString, Common.MyDB.SqlConn_RM2)
sqlCommand.CommandTimeout = 120
sqlCommand.Parameters.AddWithValue("@SSN_Number", myClaim.SSNNumber)
sqlCommand.Parameters.AddWithValue("@Repair_Completion_Date", myClaim.RepairCompletionDate)
If Common.MyDB.SqlConn_RM2.State <> System.Data.ConnectionState.Open Then Common.MyDB.SqlConn_RM2.Open()
obj = sqlCommand.ExecuteScalar()
Catch ex As Exception
Dim debug As String = ex.ToString
Finally
Common.MyDB.SqlConn_RM2.Close()
End Try
myClaim.RepairCompletionDate is a SQLDateTime.
Do i have to remove the quotes in the sqlString to compare Date columns? But then i dont get a exception but incorrect results.
