Link to home
Start Free TrialLog in
Avatar of tis9700
tis9700

asked on

Sqldatetime overflow error

Hi,

The attached file is some code I wrote that has me stymied. I get a SqlDateTime Overflow error.

The user is supposed to be able to enter a report date, save the date which will pass the current user id and the date entered through the business logic, business object to the data access layer.

Then I wanted  it to return the reportId from my stored procedure back to a textbox on my UI.

All code is in the attached file including the error I sent to my log file.

Thanks ActivityCode.pdf
Avatar of DeviantSeev
DeviantSeev
Flag of United States of America image

It's complaining because the DateTime field that you're passing to generate the report is outside of the allowed datetime sql field. Please keep in mind that by default, DateTime is not nullable and if nothing is passed in to it, then it will be automatically set to DateTime.MinValue (1/1/0001 12:00:00 AM) which is actually outside the SQL datetime field range.
Avatar of tis9700
tis9700

ASKER

Hi DeviantSeev,

Thanks for responding so quickly.

So are you saying the value entered into my textbox is not being passed to the parameter @reportDate in my data access layer?
Yes, it seems that reportDate is coming in as a date which is outside the range of the allowed dates for insertion. It's most likely coming in as DateTime.MinValue. Two ways to troubleshoot:

Debug the application or change the field in SQL from datetime to datetime2 (which accommodates the DateTime.MinValue and see what you get)
Avatar of Stephan
You could use SqlDateTime.MinValue for checking.
Avatar of tis9700

ASKER

Ok,

I'm on to something! I set some breakpoints all along my layers to track the reportDate. And it showed #10/20/2010# all the way to the ...

Public Shared Function ActivityReportDate(ByVal uId as string, Byval rDate as Date) as ActivityReport

in my data access layer but went to nothing at my parameter..

("@reportDate", a.reportDate)

I changed a.reportDate to rDate and a.UserId to uId

and the recorded was stored in the database and the reportId value was returned and displayed in the UI.

Only problem is the reportDate is now displayed in the Ui as  12:00:00 AM
Does this mean I need to format the reportDate in the public property of the object before returning it to the UI? If so, please give example.

Thanks
It depends where you are binding the data. If you are doing this on the front, you could use the .ToString(format of how you want to view the datetime) like: <%# Eval("reportDate").ToString("yyyy MM dd HH:mm")
Avatar of tis9700

ASKER

Hi Stephanonline,

I tried your suggestion and no luck.

IThe reportDate is supposed to return though an instance of my object ....

Public Sub NewReport()

        Dim currentUser As MembershipUser = Membership.GetUser(HttpContext.Current.User.Identity.Name)
        Dim currentUserId As String = currentUser.ProviderUserKey.ToString

        Dim StartReport As New ActivityInfo.ActivityReport

        StartReport = ReportBl.getReportId(currentUserId, txtReportDate.Text)

        txtReportDate.Text = StartReport.ReportDate
        txtReportId.Text = StartReport.ReportId

End Sub
 Thanks
My suggestion would be to format the date right before you're displaying it in the UI.

txtReportDate.Text = StartReport.ReportDate

can be turned into something like this:

txtReportDate.Text = String.Format("{0:MM/dd/yy}",StartReport.ReportDate);

You can check out the other date formatting options here:

http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx
Avatar of tis9700

ASKER

Hi tried to format it as suggested and no luck

So I tried ..
txtReportDate.text = StartReport.ReportDate.ToString("MM/dd/yyyy")

and returned 01/01/0001

Ugh!!! I think I need to step away for an hour.
Avatar of tis9700

ASKER

Just added ...

Dim d as Date = cmd.Parameters("@reportDate").Value
a.ReportDate = d

This seems to work but I'm not sure if it's the correct way of doing things.

I entered 10/3/2005
and the value remained in the textbox instead of changeing.

Since @reportDate is an input parameter does this mean I'm merely taking the value stored in the parameter and passing it back through a.ReportDate of my object?
ASKER CERTIFIED SOLUTION
Avatar of DeviantSeev
DeviantSeev
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tis9700

ASKER

I'm such a boob! To much coffee.

I was trying to return reportDate from my SP and it's totally unneccessary.

I removed
txtReportDate.text = StartReprot.ReportDate

Thanks Deviant
lol you're welcome. I'm glad I could help.