System.data.sqltypes.sqltypeexception: 'sqldatetime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.'

Question

0.00/5 (No votes)

See more:

I am getting an error that this date is overflowing. It must be between the date specified in the question. Please help me. I can't understand how to solve this error.

What I have tried:

C#

private void bindSearchDetails()
        {
            DataSet dsGetData = new DataSet();
            SqlCommand sqlCmd = new SqlCommand();                                     
            if (connString.State == ConnectionState.Closed)
            {
                connString.Open();
            }
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.AddWithValue("@Origin",Convert.ToString(Request.QueryString["Origin"]));
            sqlCmd.Parameters.AddWithValue("@Destination", Convert.ToString(Request.QueryString["Destination"]));
            sqlCmd.Parameters.AddWithValue("@TravelDate", Convert.ToDateTime(Request.QueryString["TravelDate"]));
            sqlCmd.CommandText = "ispGetAvailableBusDetails";
            sqlCmd.Connection = connString;
            SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
            sda.Fill(dsGetData);
            if (dsGetData.Tables[0].Rows.Count > 0)
            {
                hlinkSearch.Visible = false;
                gvBusDetails.DataSource = dsGetData.Tables[0];
                gvBusDetails.DataBind();
            }
            else
            {
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Trip not available, Please search again with different date')", true);
                hlinkSearch.Visible = true;
            }
        }

Posted 26-Jan-22 2:14am

Ashantia Clarke

Updated 26-Jan-22 4:56am

Sean Ewington

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2022-01-26T02:29:00

Solution 1

Parse the date and check whether it is within the acceptable range:

C#

DateTime travelDate;
if (DateTime.TryParse(Request.QueryString["TravelDate"], out travelDate))
{
    sqlCmd.Parameters.AddWithValue("@TravelDate", travelDate);
}
else
{
    sqlCmd.Parameters.AddWithValue("@TravelDate", DBNull.Value);
}

Also, the QueryString indexer already returns a string; there's no need to call Convert.ToString on those values:

C#

sqlCmd.Parameters.AddWithValue("@Origin", Request.QueryString["Origin"]);
sqlCmd.Parameters.AddWithValue("@Destination", Request.QueryString["Destination"]);

NB: If you want to allow dates outside of that range, change your SQL datatype from datetime to either datetime2 or date, both of which allow the same range of values as the .NET DateTime type:
Date and Time Data Types and Functions - SQL Server (Transact-SQL) | Microsoft Docs[^]

Posted 26-Jan-22 2:29am

Richard Deeming

Comments

Ashantia Clarke 26-Jan-22 9:06am

@RichardDeeming your solution fixed the error but why is it still rejecting the user input and giving the alert message ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Trip not available, Please search again with different date')", true);

Richard Deeming 26-Jan-22 9:07am

Either your input isn't a valid date, or there are no matching records. Since I can't see your input, and I have no access to your database, I can't tell you which.

Ashantia Clarke 26-Jan-22 9:20am

can i have your email address so I can share the sql and code with you please. It should be submitted today on the school portal before 5pm. I need help with this please.

Richard Deeming 26-Jan-22 9:25am

No, that's not how this site works. You ask your questions publicly, and wait to see if anyone can answer. You don't get to have private email help / tuition for your homework assignments.

Ashantia Clarke 26-Jan-22 9:48am

okay thanks, it's not like I'm asking you to do the whole website, I just want this error out the way. Thanks for your solution though, I appreciated it.