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

I cannot for the life of me figure out why im still getting this error. I have tried everything i can think of from using dbnull.value to setDOBnull() to datetime.minvalue. Can anyone help me?

 

Here's the code on my BLL:

    Public Function AddEmployee(ByVal SSN As String, ByVal FirstName As String, ByVal LastName As String, ByVal PreferredName As String, ByVal MaidenName As String, ByVal MaritalStatus As Nullable(Of Char), ByVal DOB As Nullable(Of Date), ByVal Gender As Nullable(Of Char), ByVal Ethnicity As String, ByVal StartDate As Nullable(Of Date), ByVal TerminationDate As Nullable(Of Date)) As Boolean

        ' Create a new employeeRow instance
        Dim employees As New HRMS.EmployeesDataTable()
        Dim employee As HRMS.EmployeesRow = employees.NewEmployeesRow()
        Dim sqldatenull As SqlDateTime
        sqldatenull = SqlDateTime.MinValue.Value

        employee.SSN = SSN
        employee.FirstName = FirstName
        employee.LastName = LastName


        If PreferredName Is Nothing Then employee.SetPreferredNameNull() Else employee.PreferredName = PreferredName
        If MaidenName Is Nothing Then employee.SetMaidenNameNull() Else employee.MaidenName = MaidenName
        If Not MaritalStatus.HasValue Then employee.SetMaritalStatusNull() Else employee.MaritalStatus = MaritalStatus.Value
        If Not DOB.HasValue Then employee.SetDOBNull() Else employee.DOB = DOB.Value
        If Not Gender.HasValue Then employee.SetGenderNull() Else employee.Gender = Gender.Value
        If Ethnicity Is Nothing Then employee.SetEthnicityNull() Else employee.Ethnicity = Ethnicity
        If StartDate = DateTime.MinValue Then employee.StartDate = SqlDateTime.MinValue.Value Else employee.StartDate = StartDate.Value
        'If StartDate = DateTime.MinValue Then employee.SetDOBNull() Else employee.StartDate = StartDate.Value
        'If Not StartDate.HasValue Then employee.StartDate = sqldatenull Else employee.StartDate = StartDate.Value
        If TerminationDate = DateTime.MinValue Then employee.TerminationDate = SqlDateTime.MinValue.Value Else employee.TerminationDate = TerminationDate.Value P


Traumatized by .NET
but gotta love it
0
sisieko
2/21/2007 6:22:57 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

7 Replies
961 Views

Similar Articles

[PageSpeed] 47

sisieko:

I cannot for the life of me figure out why im still getting this error. I have tried everything i can think of from using dbnull.value to setDOBnull() to datetime.minvalue. Can anyone help me?

 

Here's the code on my BLL:

    Public Function AddEmployee(ByVal SSN As String, ByVal FirstName As String, ByVal LastName As String, ByVal PreferredName As String, ByVal MaidenName As String, ByVal MaritalStatus As Nullable(Of Char), ByVal DOB As Nullable(Of Date), ByVal Gender As Nullable(Of Char), ByVal Ethnicity As String, ByVal StartDate As Nullable(Of Date), ByVal TerminationDate As Nullable(Of Date)) As Boolean

        ' Create a new employeeRow instance
        Dim employees As New HRMS.EmployeesDataTable()
        Dim employee As HRMS.EmployeesRow = employees.NewEmployeesRow()
        Dim sqldatenull As SqlDateTime
        sqldatenull = SqlDateTime.MinValue.Value

        employee.SSN = SSN
        employee.FirstName = FirstName
        employee.LastName = LastName


        If PreferredName Is Nothing Then employee.SetPreferredNameNull() Else employee.PreferredName = PreferredName
        If MaidenName Is Nothing Then employee.SetMaidenNameNull() Else employee.MaidenName = MaidenName
        If Not MaritalStatus.HasValue Then employee.SetMaritalStatusNull() Else employee.MaritalStatus = MaritalStatus.Value
        If Not DOB.HasValue Then employee.SetDOBNull() Else employee.DOB = DOB.Value
        If Not Gender.HasValue Then employee.SetGenderNull() Else employee.Gender = Gender.Value
        If Ethnicity Is Nothing Then employee.SetEthnicityNull() Else employee.Ethnicity = Ethnicity
        If StartDate = DateTime.MinValue Then employee.StartDate = SqlDateTime.MinValue.Value Else employee.StartDate = StartDate.Value
        'If StartDate = DateTime.MinValue Then employee.SetDOBNull() Else employee.StartDate = StartDate.Value
        'If Not StartDate.HasValue Then employee.StartDate = sqldatenull Else employee.StartDate = StartDate.Value
        If TerminationDate = DateTime.MinValue Then employee.TerminationDate = SqlDateTime.MinValue.Value Else employee.TerminationDate = TerminationDate.Value P

Use 1/1/1753 as a default system value understood to be "null" and insert that.

 


http://www.NetBrainer.com
0
mkamoski
2/21/2007 7:32:49 PM

 where in my code do i insert 1973?

mkamoski:
sisieko:

I cannot for the life of me figure out why im still getting this error. I have tried everything i can think of from using dbnull.value to setDOBnull() to datetime.minvalue. Can anyone help me?

 

Here's the code on my BLL:

    Public Function AddEmployee(ByVal SSN As String, ByVal FirstName As String, ByVal LastName As String, ByVal PreferredName As String, ByVal MaidenName As String, ByVal MaritalStatus As Nullable(Of Char), ByVal DOB As Nullable(Of Date), ByVal Gender As Nullable(Of Char), ByVal Ethnicity As String, ByVal StartDate As Nullable(Of Date), ByVal TerminationDate As Nullable(Of Date)) As Boolean

        ' Create a new employeeRow instance
        Dim employees As New HRMS.EmployeesDataTable()
        Dim employee As HRMS.EmployeesRow = employees.NewEmployeesRow()
        Dim sqldatenull As SqlDateTime
        sqldatenull = SqlDateTime.MinValue.Value

        employee.SSN = SSN
        employee.FirstName = FirstName
        employee.LastName = LastName


        If PreferredName Is Nothing Then employee.SetPreferredNameNull() Else employee.PreferredName = PreferredName
        If MaidenName Is Nothing Then employee.SetMaidenNameNull() Else employee.MaidenName = MaidenName
        If Not MaritalStatus.HasValue Then employee.SetMaritalStatusNull() Else employee.MaritalStatus = MaritalStatus.Value
        If Not DOB.HasValue Then employee.SetDOBNull() Else employee.DOB = DOB.Value
        If Not Gender.HasValue Then employee.SetGenderNull() Else employee.Gender = Gender.Value
        If Ethnicity Is Nothing Then employee.SetEthnicityNull() Else employee.Ethnicity = Ethnicity
        If StartDate = DateTime.MinValue Then employee.StartDate = SqlDateTime.MinValue.Value Else employee.StartDate = StartDate.Value
        'If StartDate = DateTime.MinValue Then employee.SetDOBNull() Else employee.StartDate = StartDate.Value
        'If Not StartDate.HasValue Then employee.StartDate = sqldatenull Else employee.StartDate = StartDate.Value
        If TerminationDate = DateTime.MinValue Then employee.TerminationDate = SqlDateTime.MinValue.Value Else employee.TerminationDate = TerminationDate.Value P

Use 1/1/1753 as a default system value understood to be "null" and insert that.

 


Traumatized by .NET
but gotta love it
0
sisieko
2/21/2007 7:59:21 PM
please help
Traumatized by .NET
but gotta love it
0
sisieko
2/21/2007 11:33:46 PM

sisieko:
where in my code do i insert 1973?

There are many ways to do it. 

A simple way is to call a helper function to fix each date before using it.

So, before you use myDate1 just do something like this where you "fix" the date before you use it to make sure it is within bounds...

myDate1 = DateUtility.FixDate(myDate1)

...and then use myDate1 to insert.

The code for a sample DateUtility is below.

Note that you can refactor the FixDate() method to do whatever you want, based on your system requirements. For example, the sample code below sets a date that is out of bounds to be DefaultDateTimeNull. However, it could be that you want to change the code so that dates that are greater-than max should be set to DefaultDateTimeMax and dates that less-than min are set to DefaultDateTimeMin. And so on. The code below is just a sample of one simple way to do it.

Here is the sample code... 

 


Imports Microsoft.VisualBasic

Public NotInheritable Class DateUtility

 ''' <summary>
 ''' This is the custom max-date value.
 ''' </summary>
 Public Const DefaultDateTimeMax = #12/30/9999#

 ''' <summary>
 ''' This is the custom min-date value.
 ''' </summary>
 Public Const DefaultDateTimeMin = #1/2/1753#

 ''' <summary>
 ''' This is the custom null date value.
 ''' </summary>
 Public Const DefaultDateTimeNull = #1/1/1753#

 ''' <summary>
 ''' This will fix the given date, if necessary.
 ''' </summary>
 ''' <param name="dateTimeValue">This is the date to fix.</param>
 ''' <returns>A date that is within max/min bounds.</returns>
 ''' <remarks>
 ''' Note that DateTime.CompareTo returns...
 ''' Less than zero - This instance is less than value.
 ''' Zero - This instance is equal to value.
 ''' Greater than zero - This instance is greater than value.
 '''
 ''' Note that SQL Server 2000 datetime holds...
 ''' Date and time data from January 1, 1753, through December 31, 9999, with
 ''' an accuracy of three-hundredths of a second, or 3.33 milliseconds.
 ''' </remarks>
 Public Shared Function FixDate(ByVal dateTimeValue As DateTime) As DateTime
  Dim myDateTimeFixed = DateUtility.DefaultDateTimeNull

  Try
   Dim myCompareResultForMin As Integer = Integer.MinValue

   'Check the min.
   myCompareResultForMin = dateTimeValue.CompareTo(DateUtility.DefaultDateTimeMin)

   If (myCompareResultForMin < 0) Then
    'The date given is < min, so use null.
    myDateTimeFixed = DateUtility.DefaultDateTimeNull
   Else
    'The date given is >= min. Now, check the max.
    Dim myCompareResultForMax As Integer = Integer.MinValue
    myCompareResultForMax = dateTimeValue.CompareTo(DateUtility.DefaultDateTimeMax)

    If (myCompareResultForMax <= 0) Then
     'The date given is OK <= max, so use it.
     myDateTimeFixed = dateTimeValue
    Else
     'The date given is > max, so use null.
     myDateTimeFixed = DateUtility.DefaultDateTimeNull
    End If
   End If
  Catch
   myDateTimeFixed = DateUtility.DefaultDateTimeNull
  End Try

  Return dateTimeValue
 End Function

End Class
 

 

HTH.

Thank you.

-- Mark Kamoski


http://www.NetBrainer.com
0
mkamoski
2/22/2007 5:36:21 PM
Thanks Mkamoski!! Smile
Traumatized by .NET
but gotta love it
0
sisieko
3/30/2007 10:01:46 PM

Mark, I really cannot thank you enough!!!

I think the final line from the FixDate function should be "Return myDateTimeFixed" instead of "Return dateTimeValue (which is passed by value)"

I'm sleepy and could be mistaken.

 

Thanks,

Nubs

0
Nubs
3/31/2008 8:56:23 PM

Nubs:

I think the final line from the FixDate function should be "Return myDateTimeFixed" instead of "Return dateTimeValue (which is passed by value)"

You are right. I am wrong. That is a bug in my code above.

Here is the corrected version.

 

Imports Microsoft.VisualBasic

Public NotInheritable Class DateUtility

 

 ''' <summary>
 ''' This is the custom max-date value.
 ''' </summary>
 Public Const DefaultDateTimeMax = #12/30/9999#

 

 ''' <summary>
 ''' This is the custom min-date value.
 ''' </summary>
 Public Const DefaultDateTimeMin = #1/2/1753#

 

 ''' <summary>
 ''' This is the custom null date value.
 ''' </summary>
 Public Const DefaultDateTimeNull = #1/1/1753#

 ''' <summary>
 ''' This will fix the given date, if necessary.
 ''' </summary>
 ''' <param name="dateTimeValue">This is the date to fix.</param>
 ''' <returns>A date that is within max/min bounds.</returns>
 ''' <remarks>
 ''' Note that DateTime.CompareTo returns...
 ''' Less than zero - This instance is less than value.
 ''' Zero - This instance is equal to value.
 ''' Greater than zero - This instance is greater than value.
 '''
 ''' Note that SQL Server 2000 datetime holds...
 ''' Date and time data from January 1, 1753, through December 31, 9999, with
 ''' an accuracy of three-hundredths of a second, or 3.33 milliseconds.
 ''' </remarks>
 Public Shared Function FixDate(ByVal dateTimeValue As DateTime) As DateTime
  Dim myDateTimeFixed = DateUtility.DefaultDateTimeNull

  Try
   Dim myCompareResultForMin As Integer = Integer.MinValue

   'Check the min.
   myCompareResultForMin = dateTimeValue.CompareTo(DateUtility.DefaultDateTimeMin)
 

   If (myCompareResultForMin < 0) Then
    'The date given is < min, so use null.
    myDateTimeFixed = DateUtility.DefaultDateTimeNull
   Else
    'The date given is >= min. Now, check the max.
    Dim myCompareResultForMax As Integer = Integer.MinValue
    myCompareResultForMax = dateTimeValue.CompareTo(DateUtility.DefaultDateTimeMax)

    If (myCompareResultForMax <= 0) Then
     'The date given is OK <= max, so use it.
     myDateTimeFixed = dateTimeValue
    Else
     'The date given is > max, so use null.
     myDateTimeFixed = DateUtility.DefaultDateTimeNull
    End If
   End If
  Catch
   myDateTimeFixed = DateUtility.DefaultDateTimeNull
  End Try

  Return myDateTimeFixed
 End Function

End Class

 


http://www.NetBrainer.com
0
mkamoski
4/3/2008 12:18:17 PM
Reply: