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
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
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.
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?
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)
Debug the application or change the field in SQL from datetime to datetime2 (which accommodates the DateTime.MinValue and see what you get)
You could use SqlDateTime.MinValue for checking.
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
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").ToStrin g("yyyy MM dd HH:mm")
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(HttpCon text.Curre nt.User.Id entity.Nam e)
Dim currentUserId As String = currentUser.ProviderUserKe y.ToString
Dim StartReport As New ActivityInfo.ActivityRepor t
StartReport = ReportBl.getReportId(curre ntUserId, txtReportDate.Text)
txtReportDate.Text = StartReport.ReportDate
txtReportId.Text = StartReport.ReportId
End Sub
Thanks
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(HttpCon
Dim currentUserId As String = currentUser.ProviderUserKe
Dim StartReport As New ActivityInfo.ActivityRepor
StartReport = ReportBl.getReportId(curre
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 }",StartRe port.Repor tDate);
You can check out the other date formatting options here:
http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx
txtReportDate.Text = StartReport.ReportDate
can be turned into something like this:
txtReportDate.Text = String.Format("{0:MM/dd/yy
You can check out the other date formatting options here:
http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx
ASKER
Hi tried to format it as suggested and no luck
So I tried ..
txtReportDate.text = StartReport.ReportDate.ToS tring("MM/ dd/yyyy")
and returned 01/01/0001
Ugh!!! I think I need to step away for an hour.
So I tried ..
txtReportDate.text = StartReport.ReportDate.ToS
and returned 01/01/0001
Ugh!!! I think I need to step away for an hour.
ASKER
Just added ...
Dim d as Date = cmd.Parameters("@reportDat e").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?
Dim d as Date = cmd.Parameters("@reportDat
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.