SqlDateTime Overflow Error When Button is Clicked

Question

When I execute the application, the application runs... then when I click the button I'm getting the error

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

I'm not sure how to go about fixing this, as most of the solutions on the internet don't seem to work for me. Is this something that is on the SQL side?

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection sqlcn1 = new SqlConnection("My Connection String");
    sqlcn1.Open();

    //Stored Procedure
    SqlCommand sqlcmddel = new SqlCommand("My_Stored_Procedure", sqlcn1);
    sqlcmddel.CommandType = CommandType.StoredProcedure;
    sqlcmddel.ExecuteNonQuery();

    sqlcn1 = new SqlConnection("My Connection String");
    sqlcn1.Open();

    foreach (DataGridViewRow row in dataGridView1.Rows)
    {
        SqlCommand sqlcmdins = new SqlCommand("My_Stored_Procedure", sqlcn1);

        sqlcmdins.CommandType = CommandType.StoredProcedure;

        //Stored Procedure
        sqlcmdins.Parameters.Add("@sugnum", SqlDbType.Int).Value = Convert.ToInt64 (row.Cells[0].Value);
        sqlcmdins.Parameters.Add("@sugtype", SqlDbType.NVarChar, 50).Value = Convert.ToString(row.Cells[1].Value);
        sqlcmdins.Parameters.Add("@buyerid", SqlDbType.NVarChar, 50).Value = Convert.ToString (row.Cells[2].Value);
        sqlcmdins.Parameters.Add("@duedate", SqlDbType.DateTime).Value = Convert.ToDateTime (row.Cells[3].Value);
        sqlcmdins.Parameters.Add("@xrelqty", SqlDbType.Float).Value = Convert.ToDouble (row.Cells[4].Value);
        sqlcmdins.Parameters.Add("@purchasingfactor", SqlDbType.Float).Value = Convert.ToDouble (row.Cells[5].Value);
    }
}

What is the DateTime value that you are trying to enter into sql? — Sudsy1002, Jul 20 '17 at 14:37
I'm guessing that `row.Cells[3].Value` doesn't contain what you think it contains! — Chris Dunaway, Jul 20 '17 at 14:39
as @ChrisDunaway said, that error that you are getting typically indicates that the value is either not a correctly formatted DateTime or it isn't a DateTime at all. — Sudsy1002, Jul 20 '17 at 14:42
@Sudsy1002 Haha would you look at that...It's showing up as, "M/DD/YYY HH:MM:SS" in my Cells[3]... What is the solution to solving this problem now? — hrutk1ta, Jul 20 '17 at 14:45
If the value is already in the sql database you can open SSMS (SQL Server Management Studio) and right click on the table in question. Then click "Edit Top ### Rows". You can then manually change the value. If multiple values are incorrect then consider checking the design of the table to make sure the format is correct. You would right click the table and select "Design" to make sure that the column is using the correct data type. — Sudsy1002, Jul 20 '17 at 14:51
@sudsy1002 Okay so on the SQL side of things I looked and the dates are in the correct format in the table already "YYYY-MM-DD HH:MM:SS.00".... They're getting put into the datagridview in a different format "M/DD/YYY HH:MM:SS". Why is this? — hrutk1ta, Jul 20 '17 at 15:04
This will likely be what you need. [link]https://stackoverflow.com/questions/4033113/how-to-format-datetime-columns-in-datagridview — Sudsy1002, Jul 20 '17 at 15:08
It worked for changing the format, which is nice... but unfortunately I'm still getting the same error message. — hrutk1ta, Jul 20 '17 at 15:18

score 0 · Answer 1 · answered Jul 20 '17 at 14:42

0

change the data type to datetime2 at SQL side (sorry for that short answer, can't make comments)

answered Jul 20 '17 at 14:42

hopeless

96
10

Tried that and.... "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value" :/ – hrutk1ta Jul 20 '17 at 14:49
@hrutk1ta as was said previously, I guess the value from the column is not what you expect, try debugging and find out what the value type and value are – hopeless Jul 20 '17 at 14:57

score 0 · Answer 2 · answered Jul 20 '17 at 15:21

0

Try to pass string value instead, parameters will figure out the correct data type example qlcmdins.Parameters.Add("@duedate", row.Cells[3].Value.ToString())

answered Jul 20 '17 at 15:21

Henry

1,010
8
10

"NullReferenceException was unhandled" for that line.... "Object reference not set to an instance of an object" :( – hrutk1ta Jul 20 '17 at 15:26

SqlDateTime Overflow Error When Button is Clicked

2 Answers2