I am getting this error when executing a reader which is used on an asp page:
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
A date is selected from a calendar, and is set to a DateTime variable, which is then used as a parameter in an SQL command. It seems to randomly work, I select a date once and it works, I reload the page and select the same day and it might not work.
My relative code is below, I'm getting the error at the .ExecuteReader() line.
I've checked the confirmedDate variable right before the reader is executed, and it does have a valid DateTime.
DateTime confirmedDate = DateTime.Parse(dateSelected);
SqlCommand command4 = new SqlCommand();
command4.Connection = gconn;
String sql4 = "SELECT MAX([Day]) as TheDay FROM Days WHERE User_ID = @User_ID AND [Day] < @dateSelected AND NOT EXISTS (SELECT 1 FROM Days WHERE User_ID = @User_ID AND [DAY] >= @dateSelected)";
command4.CommandText = sql4;
command4.Parameters.Add(new SqlParameter("@User_ID", ""));
command4.Parameters.Add(new SqlParameter("@dateSelected", confirmedDate));
for (int i = 0; i < firstName.Count; i++ )
{
command4.Parameters["@User_ID"].Value = userID[i];
using (SqlDataReader reader = command4.ExecuteReader()) //error here
{
while (reader.Read())
{
if (reader.HasRows)
{
if (reader["TheDay"].ToString() == "")
{
dates.Add("NULL");
}
else
{
dates.Add(reader["TheDay"].ToString());
}
}
}
}
}
I've looked up other questions regarding this but can't find a solution that works. A valid DateTime value is always being passed in.
The possible range of a .net DateTime object is different from a Sql Server DateTime object. You should do some parameter validation before plugging it into your sql query.
Here is an example.
DateTime confirmedDate = DateTime.Parse(dateSelected);
if(confirmedDate > DateTime.Now) throw new ArgumentOutOfRangeException("Date time cannot be in the future");
if(confirmedDate.Year < 1990) throw new ArgumentOutOfRangeException("Sorry, we only have data starting from the year 1990");
Also specify your data types in your parameters.
command4.Parameters.Add(new SqlParameter("@dateSelected", SqlDbType.DateTime){Value = confirmedDate}));
The range of a .Net DateTime
The possible range of a Sql DateTime