none
Save Error sqldatetime overflow 1/1/1753 RRS feed

  • Question

  • I am trying to retrieve a date from a datetime2 column from one table and then save it to a datetime2 column in another table.

    Program crashes when the date is 6/30/1209, for example, producing the error message:  "Save Error sqldatetime overflow 1/1/1753..."

    The line of code that triggers the error is:  CommandSave1.Parameters.AddWithValue("@due_date", Due_Date)

    Have no idea why I am getting this error message.

     Public Sub SaveMasterAudit(ByVal Account_No As String, ByVal Classify As String, ByVal L_Code As String,
                             ByVal Last_Done As Date, ByVal Due_Date As Date, ByVal Activity As String, ByVal SAcct As String, ByVal DataTable As String)
    
    
            ' This procedure is used by both the client master audit and the foster parent one.  Called from clreports.vb and fpreports.vb
    
            Dim CommandSave1 As SqlCommand = New SqlCommand()
            Dim ConnectionSave1 As New SqlConnection(ConnectionString)
            Dim MasterAudit_Id = 0
    
            ' Obtain new id value 
            If DataTable = "clmasterauditrpt" Then
                MasterAudit_Id = GetNew_Id("MasterAudit_Id", "clmasterauditrpt")
            Else
                MasterAudit_Id = GetNew_Id("MasterAudit_Id", "fpmasterauditrpt")
            End If
    
            CommandSave1.Connection = ConnectionSave1
    
            CommandSave1.CommandText = "INSERT INTO " & DataTable & " " &
                "(user_name, masteraudit_id, account_no, classify, l_code, last_done, due_date, activity, sacct)" &
                "VALUES(@user_name, @masteraudit_id, @account_no, @classify, @l_code, @last_done, @due_date, @activity, @sacct)"
    
            CommandSave1.Parameters.AddWithValue("@user_name", strUser_Name)
            CommandSave1.Parameters.AddWithValue("@masteraudit_id", MasterAudit_Id)
            CommandSave1.Parameters.AddWithValue("@account_no", Account_No)
            CommandSave1.Parameters.AddWithValue("@classify", Classify)
            CommandSave1.Parameters.AddWithValue("@l_code", L_Code)
    
            If Last_Done = #01/01/1900# Then
                CommandSave1.Parameters.AddWithValue("@last_done", SqlDateTime.Null)
            Else
                CommandSave1.Parameters.AddWithValue("@last_done", Last_Done)
            End If
    
            CommandSave1.Parameters.AddWithValue("@due_date", Due_Date)
            CommandSave1.Parameters.AddWithValue("@activity", Activity)
            CommandSave1.Parameters.AddWithValue("@sacct", SAcct)
    
            ' Open connection
            If ConnectionSave1.State = ConnectionState.Closed Then ConnectionSave1.Open()
    
            ' Execute 
            Try
                CommandSave1.ExecuteNonQuery()
            Catch SqlExceptionErr As SqlException
                MessageBox.Show(SqlExceptionErr.Message)
            End Try
    
            ' Close connection
            If ConnectionSave1.State = ConnectionState.Open Then ConnectionSave1.Close()
    
            ' Dispose of objects
            If Not IsNothing(CommandSave1) Then CommandSave1.Dispose()
            If Not IsNothing(ConnectionSave1) Then ConnectionSave1.Dispose()
    
        End Sub


    ISV using VB.net and SQL Server

    Tuesday, August 27, 2019 3:13 PM

Answers

  • Try Add instead of AddWithValue:

    CommandSave1.Parameters.Add( "@due_date", SqlDbType.DateTime2 ).Value = Due_Date

    • Marked as answer by Jeff07 Tuesday, August 27, 2019 4:11 PM
    • Unmarked as answer by Jeff07 Tuesday, August 27, 2019 4:12 PM
    • Marked as answer by Jeff07 Tuesday, August 27, 2019 4:13 PM
    Tuesday, August 27, 2019 4:02 PM

All replies

  • Try Add instead of AddWithValue:

    CommandSave1.Parameters.Add( "@due_date", SqlDbType.DateTime2 ).Value = Due_Date

    • Marked as answer by Jeff07 Tuesday, August 27, 2019 4:11 PM
    • Unmarked as answer by Jeff07 Tuesday, August 27, 2019 4:12 PM
    • Marked as answer by Jeff07 Tuesday, August 27, 2019 4:13 PM
    Tuesday, August 27, 2019 4:02 PM
  • That worked.  Thank you very much.


    ISV using VB.net and SQL Server

    Tuesday, August 27, 2019 4:13 PM