#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jul 2005
    Posts
    833
    Rep Power
    11

    Question SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM


    Hi. I am getting the error described in the title - SqlDateTime overflow. I have read what is available online but am still not able to get rid of it. Help much appreciated... thanks.

    The Full Error Is:
    Code:
    System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value) at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value) at System.Data.SqlTypes.SqlDateTime..ctor(DateTime value) at System.Data.SqlClient.MetaType.FromDateTime(DateTime dateTime, Byte cb) at System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.audiobook_viewdetails_aspx.DetailsView1_ItemInserting(Object Src, DetailsViewInsertEventArgs Args) in C:\Inetpub\wwwroot\dushkinmedia\supportweb\PresentationTier\audiobook_services\audiobook_viewdetails.aspx:line 151
    And, the method that is throwing the error is:

    Code:
      Sub DetailsView1_ItemInserting(ByVal Src As Object, ByVal Args As DetailsViewInsertEventArgs)
    
            'validate the controls
            
            If Args.Values("ISBN") = "" Then
                Args.Cancel = True
                MSG.Text = "-- Missing ISBN number"
            End If
    
            If Args.Values("author_first") = "" Then
                Args.Cancel = True
                MSG.Text = "-- Missing first name"
            End If
            
            If Args.Values("author_last") = "" Then
                Args.Cancel = True
                MSG.Text = "-- Missing last name"
            End If
    
            If Args.Values("publisher") = "" Then
                Args.Cancel = True
                MSG.Text = "-- Missing Publisher"
            End If
    
            If Args.Values("text_description") = "" Then
                Args.Cancel = True
                MSG.Text = "-- Missing item description"
            End If
    
            If Not IsNumeric(Args.Values("regular_price")) Then
                Args.Cancel = True
                MSG.Text = "-- regular price is not numeric"
            Else
                If Args.Values("regular_price") < 0 Then
                    Args.Cancel = True
                    MSG.Text = "-- regular price is out of range"
                End If
            End If
    
            If Not IsNumeric(Args.Values("discount_price")) Then
                Args.Cancel = True
                MSG.Text = "-- Discount price is not numeric"
            Else
                If Args.Values("discount_price") < 0 Then
                    Args.Cancel = True
                    MSG.Text = "-- Discount price is out of range"
                End If
            End If
            
            'if all the controls are valid, decalre the types
            Dim audiobookID As Integer
            Dim title As String
            Dim author_first As String
            Dim author_last As String
            Dim publisher As String
            Dim ISBN As String
            Dim regular_price As String
            Dim discount_price As String
            Dim text_description As String
            Dim created_date As DateTime
            
            'Dim imgStream As System.IO.Stream = _coverImage.PostedFile.InputStream
            'Dim imgLen As Integer = _coverImage.PostedFile.ContentLength
            'Dim imgcontenttype As String = _coverImage.PostedFile.ContentType
            'Dim imgName As String = _coverImage.FileName
            'Dim imgBinaryData(imgLen) As Byte
    
            'Dim idis As String = Request.QueryString("id")
            'Dim pubid As Integer = System.Convert.ToInt32(idis)
            'Dim RowsAffected As Integer = SaveImage(imgName, imgBinaryData, imgContentType, pubid)
            'Dim n As Integer = imgStream.Read(imgBinaryData, 0, imgLen)
    
            'If Not _coverImage.HasFile Then
            '    '-- Missing file selection
            '    MSG.Text = "Please choose a file to upload"
            '    Else
            '        If InStr(UCase(_coverImage.FileName), ".JPG") = 0 Then
            '            '-- Selection of non-JPG file
            '            MSG.Text = "You can upload only JPG files"
            '        Else
            '            If _coverImage.PostedFile.ContentLength > 100000 Then
            '                '-- File too large
            '                MSG.Text = "Uploaded file size must be less than 100 KB"
            '            Else
            '                '-- File upload
            '                _coverImage.SaveAs(Server.MapPath("C:/Inetpub/wwwroot/images/") & _coverImage.FileName)
            '                MSG.Text = "<b>File Uploaded</b><br/>"
            '                MSG.Text &= "File Name: " & _coverImage.FileName & "<br/>"
            '                MSG.Text &= "File Size: " & _coverImage.PostedFile.ContentLength & " bytes<br/>"
            '            End If
            '        End If
            '    End If
    
            '    Dim audStream As Stream = _audioDescription.PostedFile.InputStream
            '    Dim audLen As Integer = _audioDescription.PostedFile.ContentLength
            '    Dim audcontenttype As String = _audioDescription.PostedFile.ContentType
            '    Dim audName As String = _audioDescription.FileName
            '    Dim audBinaryData(imgLen) As Byte
    
            '   |||||   Set up a Connection Object to the SQL DB
                Dim MyConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection("server=ARIA;database=dushkinmedia;Integrated Security=SSPI")
                '   |||||   Pass in the StoreProcedure or Command String, as well as the Connection object
                Dim MyCmd As New System.Data.SqlClient.SqlCommand("sp_RegisterNewAudioBook", MyConn)
                '   |||||   Set the Command Type (Stored Procedure, Text, etc)
                MyCmd.CommandType = System.Data.CommandType.StoredProcedure
                '   |||||   Create Parameter Objects for values passed in
                Dim objParam1, objParam2, objParam3, objParam4, objParam5, objParam6, _
                objParam7, objParam8, objParam9, objParam10, objParam11, objParam12, _
                objParam13, objParam14, objParam15 As System.Data.SqlClient.SqlParameter
                '   |||||   Add your parameters to the parameters Collection
            objParam1 = MyCmd.Parameters.Add("@audiobookID", Data.SqlDbType.Int)
            objParam2 = MyCmd.Parameters.Add("@title", Data.SqlDbType.VarChar)
            objParam3 = MyCmd.Parameters.Add("@author_first", Data.SqlDbType.VarChar)
            objParam4 = MyCmd.Parameters.Add("@author_last", Data.SqlDbType.VarChar)
            objParam5 = MyCmd.Parameters.Add("@publisher", Data.SqlDbType.VarChar)
            objParam6 = MyCmd.Parameters.Add("@ISBN", Data.SqlDbType.VarChar)
            objParam7 = MyCmd.Parameters.Add("@regular_price", Data.SqlDbType.VarChar)
            objParam8 = MyCmd.Parameters.Add("@discount_price", Data.SqlDbType.VarChar)
            objParam9 = MyCmd.Parameters.Add("@text_description", Data.SqlDbType.Text)
            objParam10 = MyCmd.Parameters.Add("@created_date", Data.SqlDbType.DateTime)
                'objParam10 = MyCmd.Parameters.AddWithValue("@img_name", imgName)
            objParam11 = MyCmd.Parameters.AddWithValue("@img_data", System.Data.SqlDbType.Image)
                'objParam12 = MyCmd.Parameters.AddWithValue("@img_contenttype", imgcontenttype)
                'objParam13 = MyCmd.Parameters.AddWithValue("@aud_name", audName)
            objParam14 = MyCmd.Parameters.AddWithValue("@aud_data", System.Data.SqlDbType.VarBinary)
                'objParam15 = MyCmd.Parameters.AddWithValue("@aud_contenttype", audcontenttype)
                '   |||||   Set the Parameter values to the passed in values
            objParam1.Value = audiobookID
            objParam2.Value = title
            objParam3.Value = author_first
            objParam4.Value = author_last
            objParam5.Value = publisher
            objParam6.Value = ISBN
            objParam7.Value = regular_price
            objParam8.Value = discount_price
            objParam9.Value = text_description
            objParam10.Value = created_date
                'objParam11.Value = imgBinaryData
                'objParam14.Value = audBinaryData
    
                Try
    
                    '   |||||   Check if Connection to DB is already open, if not, then open a connection
                    '   |||||   DB not already Open...so open it
    
                    MyConn.Open()
                    MyCmd.ExecuteNonQuery()
    
                    '   |||||   Close the Connection Closes with it
                    MyConn.Close()
    
                
            Catch e As Exception
                Beep()
                MSG.Text = e.ToString
                
            End Try
            
            'Response.Redirect("~/audiobook_services/audiobook_view.aspx")
            
        End Sub
    Thanks Again...
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jul 2005
    Posts
    833
    Rep Power
    11
    hi. i have reposted this under MS SQL b/c i am not getting any responses. tnks
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Location
    Germany
    Posts
    166
    Rep Power
    10
    You did:

    Dim created_date As DateTime
    objParam10 = MyCmd.Parameters.Add("@created_date", Data.SqlDbType.DateTime)
    objParam10.Value = created_date
    Where did you actually set the DateTime? It's probably 00/00/0000 00:00:00 and that's why it throws this error Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

    Try this:

    Code:
    Dim created_date As DateTime
    created_date = DateTime.Now
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jul 2005
    Posts
    833
    Rep Power
    11

    thanks...


    Originally Posted by reteep
    You did:



    Where did you actually set the DateTime? It's probably 00/00/0000 00:00:00 and that's why it throws this error Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

    Try this:

    Code:
    Dim created_date As DateTime
    created_date = DateTime.Now
    Ah, yes... silly me. That was it. Thanks for your help.

IMN logo majestic logo threadwatch logo seochat tools logo