System.Data.SqlTypes.SqlTypeException:

[Mike Albanese]

I am building an ASP.Net application with VB.Net. I have
several date fields in my form and some of them are
optional. If ther is no data in the form field i set the
sql parameter to dbnull.value. Still the insert bombs out.
The error i am getting is

System.Data.SqlTypes.SqlTypeException: SqlDateTime
overflow. Must be between 1/1/1753 12:00:00 AM and
12/31/9999 11:59:59 PM

Any insights would be apprecaited.

Thanks,

Mike

 

[Trajero]

Do you have your fields in your database set to accept
null values?

 

[Tian Min Huang]

Hi Mike,

Thanks for your post. In addition to Trajero's suggestion, I'd like to
check the value of DateTime field you attempting to insert to database.
Please make sure either SqlDateTime value is a valid value between 1/1/1753
and 12/31/9999, or it is assigned as SqlDateTime.Null.

I look forward to your response.

Have a nice day!

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Michael Albanese]

Hello and thanks for your replies.

The SQL database fields are of type datatime and are set to accept null
values.

The value of the form field is not set to NULL as i had thought, but to
#1/1/0001#. Once I realized this i was able to write the function below
that would test the Form value and conditionally set the SQL parameter
value.

I have it working now. Thanks for your help.

Michael

Here is the cinction i wrote:

Public Function chkDateParam(ByVal d As Date, ByRef sqlParam As
SqlParameter)
Try
If d = System.DateTime.MinValue Then
sqlParam.Value = DBNull.Value
Else
sqlParam.Value = d
End If

Catch ex As NullReferenceException

'if the field is blank, str will = null. so set the param to
null too!
sqlParam.Value = DBNull.Value

End Try
End Function