Conversion failed: SqlParameter and DateTime

Posted by Tim on Stack Overflow See other posts from Stack Overflow or by Tim
Published on 2010-06-14T09:18:08Z Indexed on 2010/06/14 9:22 UTC
Read the original article Hit count: 341

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.

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about vb.net