Conversion failed: SqlParameter and DateTime

Question

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.

Answer 1

Yes, the quotes should be removed. You should never have quotes around parameters in T-SQL - not even when using strings. Hence, you should remove the quotes around @SSN_Number and @Repair_Completion_Date.