Exception when When using

Question

I have a winform application which contains a datetimepicker. When I select a date it gives that date sale details. I have added the code to form load event also, but I get a SqlDateTime overflow error. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

my code is

  DateTime fromDate, toDate;
            DateTime.TryParse(dateTimePicker_FromByDateSaleReport.Text, out fromDate);
            DateTime.TryParse(dateTimePicker_ToByDateSaleReport.Text, out toDate);


            SqlDataAdapter da3 = new SqlDataAdapter("SELECT Bill_Date,Name,Item,Item_Code,MRP, Quantity,Amount as Total, Amount_After_Discount as Grand_Total  From POS  LEFT JOIN Customers ON POS.Customer=Customers.Customer_Id WHERE Bill_Date Between @From AND @To", con);
            da3.SelectCommand.Parameters.AddWithValue("@From", SqlDbType.DateTime).Value = fromDate;
            da3.SelectCommand.Parameters.AddWithValue("@To", SqlDbType.DateTime).Value = toDate;
            DataTable dt3 = new DataTable();
            da3.Fill(dt);
            dgv_ByDateSaleReport.DataSource = dt3;
        }

.

Did you output dateTimePicker_FromByDateSaleReport.Text? What does it say? Which date format does it contain? — Tyron78
– Tyron78, Commented Jan 18, 2017 at 14:57
You don't need to parse out the value from the picker - you can just retrieve it with the Value property. Don't think that's the problem, but it might help. — RB.
– RB., Commented Jan 18, 2017 at 14:57
Why is this question upvoted? The issue could be easily solved, if the asker had debugged it just one time. The code is not properly formatted. We are left to guess which date caused the overflow. And this has nothing to do with Visual Studio. So why is it upvoted? This question is of low quality. — Rabban
– Rabban, Commented Jan 18, 2017 at 15:08

Siyual · Accepted Answer · 2017-01-18 14:58:03Z

3

This can happen for one of two reasons:

Firstly, either the fromDate or the toDate is out of that range, or (most likely):

One of your date variables (either fromDate or toDate) could not be parsed by the TryParse(). When this happens, the date is set to the default C# value of 0001-01-01.

In SQL Server a DATETIME datatype can only hold values from 1753-01-01 to 9999-12-31, and the 0001-01-01 that was being passed is out of range.

Check the value of the strings being parsed prior to execution.

answered Jan 18, 2017 at 14:58

Siyual

17k8 gold badges47 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RB. Over a year ago

Or, better, don't use the string values at all, and just get the DateTime that the picker will give you directly! Nice psychic debugging by the way ;)

Rion Williams · Accepted Answer · 2017-01-18 14:57:53Z

1

This could be caused by your fromDate and toDate values not properly being parsed, and thus likely containing their original default value of "1/1/0001 12:00:00 AM", which won't be a valid date :

DateTime fromDate, toDate;
DateTime.TryParse(dateTimePicker_FromByDateSaleReport.Text, out fromDate);
DateTime.TryParse(dateTimePicker_ToByDateSaleReport.Text, out toDate);

If you are using a DateTime picker control, you could likely access the date by using the DateTimePicker.Value property, which will return an actual DateTime object :

DateTime fromDate = dateTimePicker_FromBydateSaleReport.Value;
DateTime toDate = dateTimePicker_ToByDateSaleReport.Value;

answered Jan 18, 2017 at 14:57

Rion Williams

76.9k38 gold badges206 silver badges335 bronze badges

Comments

Zohar Peled · Accepted Answer · 2017-01-18 15:04:39Z

0

Well, SqlDbType.DateTime has a value ranging from January 1, 1753 to December 31, 9999, so you should try to use SqlDbType.DateTime2 instead.
Also, no point of parsing the DateTimePicker.Text property to DateTime, since it already has a DateTime property called Value.

Try this instead:

        SqlDataAdapter da3 = new SqlDataAdapter("SELECT Bill_Date,Name,Item,Item_Code,MRP, Quantity,Amount as Total, Amount_After_Discount as Grand_Total  From POS  LEFT JOIN Customers ON POS.Customer=Customers.Customer_Id WHERE Bill_Date Between @From AND @To", con);
        da3.SelectCommand.Parameters.Add("@From", SqlDbType.DateTime2).Value = dateTimePicker_FromByDateSaleReport.Value;
        da3.SelectCommand.Parameters.Add("@To", SqlDbType.DateTime2).Value = dateTimePicker_ToByDateSaleReport.Value;
        DataTable dt3 = new DataTable();
        da3.Fill(dt);
        dgv_ByDateSaleReport.DataSource = dt3;

edited Jan 18, 2017 at 15:04

answered Jan 18, 2017 at 15:00

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

2 Comments

Abhijith Over a year ago

can i insert into datetime typed column with parameters as cmd4.Parameters.AddWithValue("@BillDate", dateTimePicker_POS.Text); Iam getting error message as "Conversion failed when converting date and/or time from character string."

Zohar Peled Over a year ago

Didn't see that you where using AddWithValue, changed that to Add. Anyway, the more important thing is that you don't need to use the Text property at all since you have a Value property.

Collectives™ on Stack Overflow

Exception when When using

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related