none
Getting: Conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value. RRS feed

  • Question

  • I am trying to execute the delete event, the MDate is smalldatetime data type in the SQL table, and the selectSQL variable statement during the debuge display the proper result.

    DELETE FROM Courier_Trans_Detail WHERE (StaffID = 4) AND (MailDate = '27-02-2018')

    Yet I still get the an out-of-range-value error message.

    I appreciate any help on this issue.

    code:

        Protected Sub grvDetail_RowDeleting(sender As Object, e As GridViewDeleteEventArgs) Handles grvDetail.RowDeleting
            Dim connectionString As String
            Dim selectSQL As String

            Dim StaffID As Integer = grvDetail.DataKeys(e.RowIndex).Values("StaffID")
            Dim MailDate As Date = grvDetail.DataKeys(e.RowIndex).Values("MailDate")


            selectSQL = "DELETE FROM Courier_Trans_Detail WHERE (StaffID = " & StaffID & ") AND (MailDate = '" & MailDate.ToString("dd-MM-yyyy") & "')"

            connectionString = WebConfigurationManager.ConnectionStrings("CallManagerCStr").ConnectionString
            Dim con As New SqlConnection(connectionString)
            Dim cmd As New SqlCommand(selectSQL, con)
            Dim adapter As New SqlDataAdapter(cmd)

            Dim ds As New DataSet()
            adapter.Fill(ds, "Courier_Trans_Detail")

            grvDetail.DataSource = ds
            grvDetail.DataBind()
        End Sub

    Thanks in advance

    Sunday, February 4, 2018 4:50 AM

Answers

All replies

  • Solved by changing the date format to "yyyy-MM-dd"
    • Marked as answer by Matt_90 Sunday, February 4, 2018 7:55 AM
    Sunday, February 4, 2018 7:55 AM
  • Good to hear you resolved the issue yet using parameters would be a better way to go.

    Public Class Operations
        Private Server As String = "KARENS-PC"
        Private Catalog As String = "SomeDatabase"
        Private SqlConnectionString As String = ""
        Public Exception As Exception
        ''' <summary>
        ''' Setup the connection string
        ''' </summary>
        Public Sub New()
            SqlConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
        End Sub
        Public Function RemoveRecord(ByVal pStaffIdentifier As Integer, ByVal pMailDate As DateTime) As Boolean
    
    
            Using cn As New SqlConnection() With {.ConnectionString = SqlConnectionString}
    
                Dim statement = "DELETE FROM Courier_Trans_Detail " &
                                "WHERE (StaffID = @StaffID) AND (MailDate = @MailDate)"
    
                Using cmd As New SqlCommand() With {.Connection = cn, .CommandText = statement}
                    cmd.Parameters.AddWithValue("@StaffID", pStaffIdentifier)
                    cmd.Parameters.AddWithValue("@MailDate", pMailDate)
    
                    Try
    
                        cn.Open()
                        Return cmd.ExecuteNonQuery = 1
    
                    Catch ex As Exception
                        Exception = ex
                        Return False
                    End Try
                End Using
            End Using
        End Function
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, February 4, 2018 11:24 AM
    Moderator
  • @Karen Payne have you read this article https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/

    I try to always use .Add with the SQLDBType


    Mary

    Monday, February 5, 2018 4:55 AM
  • @Karen Payne have you read this article https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/

    I try to always use .Add with the SQLDBType


    Mary

    Thanks for pointing this out yet I've never had a problem using AddWithValue. When I was developing desktop solutions about one percent of the time I used Add which was generally with a data type of binary or blob while strings, numeric s and dates never had any issues. And these apps handles vast amounts of data where many times the data needed validation prior to getting to the data layer. I believe the key here is to make sure one is sending appropriate data to the DAL rather than simply taking for granted the data is good to go.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, February 5, 2018 11:26 AM
    Moderator
  • Mary,

    The same kind as blog like articles from around 1990 when Wordperfect got spelling control. There was told to set it off because there was a chance if would go wrong. Are you also still using only notepad for editing letters? Be aware that can be the case, somehow I always write change as I mean chance. It sound different as well for me, but still I do it. Yea stupid, but does it help when I write =change.

    .Add had probably the problem in version 1.0 where the writer of the blog was talking about. It became ambiguous because there was as well a new .Add created.  


    Success Cor



    Monday, February 5, 2018 12:09 PM