getting exception for dates not in range

Question

0.00/5 (No votes)

See more:

i am adding the price for the products between the two dates..

i have two textboxes to select the the dates which are integrated with ajax tool kit calender control..

but in case if user enter the date. which is not between the dates present in db. i get an exception.. even if one date it db and the other one is not i am getting exception..

how i sort out this problem....
here is the code.

C#

try
       {
           SqlConnection MAconn = new SqlConnection();
           MAconn.ConnectionString = connectionString;
           MAconn.Open();
           //int c = System.Convert.ToInt32(txtamount.Text);

           SqlCommand MAcmd = MAconn.CreateCommand();

           MAcmd.CommandText = "select Sum(price) from sale where (startdate between '" + TextBox1.Text + "' and '" + TextBox2.Text + "')";
           lblsale.Text = System.Convert.ToString(MAcmd.ExecuteScalar());


           MAconn.Close();
           //Response.Redirect("profit.aspx");
       }
       catch (Exception ex)
       {
           lblres.Text = "oh date entered is not in range" + ex;
       }

   }

eand the exception is as fallows.

HTML

System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.HasMoreRows() at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) at System.Data.SqlClient.SqlCommand.ExecuteScalar() at profit.Button1_Click(Object sender, EventArgs e)

Posted 3-Dec-11 8:20am

codegeekalpha

Add a Solution

4 solutions

Solution 2

Never use string concatenation to add conditions to an SQL statement. Instead use SqlParameter[^] class.

Using concatenation may cause problems with datatype conversions as you have experienced but also leaves you wide open to SQL injections.

Posted 3-Dec-11 8:32am

Wendelius

Comments

thatraja 3-Dec-11 21:50pm

5!

Wendelius 4-Dec-11 3:37am

Thank you :)

Solution 3

As I said a number of times in your previous questions you are never going to solve this until you stop using strings for dates and use proper DateTime variables. A small amount of pain now will save you from lots of anguish in the future.

Posted 3-Dec-11 9:21am

Richard MacCutchan

Comments

codegeekalpha 3-Dec-11 15:50pm

i am not getting u.. if u give me code example than maybe i get u...

Richard MacCutchan 4-Dec-11 3:13am

Use DateTime.Parse() method to validate your text fields before sending to SQL. Alternatively, replace the TextBoxes by DateTimePicker objects, so your dates are automatically validated.

thatraja 3-Dec-11 21:50pm

5!

Solution 4

just to leave this error, try the following:

C#

try
        {
            SqlConnection MAconn = new SqlConnection();
            MAconn.ConnectionString = connectionString;
            MAconn.Open();
            //int c = System.Convert.ToInt32(txtamount.Text);

            SqlCommand MAcmd = MAconn.CreateCommand();
 
            MAcmd.CommandText = "select Sum(price) from sale where (startdate between '" + TextBox1.Text + "' and '" + TextBox2.Text + "')";
            lblsale.Text = System.Convert.ToString(MAcmd.ExecuteScalar());
 

            MAconn.Close();
            //Response.Redirect("profit.aspx");
        }
        catch (System.Data.SqlClient.SqlException sqlEx)
        {
            lblres.Text = "oh date entered is not in range: " + sqlEx.ToString();
        }
        catch (Exception ex)
        {
            lblres.Text = "GENERAL ERROR: " + ex.ToString();
        }
 
    }

but also, as solution1 said, do not use concatenation for database manipulation. Your codes are prone to SQL injection.

Regards,
Eduard

Posted 3-Dec-11 13:08pm

Eduard Lu

Comments

thatraja 3-Dec-11 21:50pm

5!

[no name] 3-Dec-11 21:58pm

thanks thatraja!

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)

Wonde Tadesse · Accepted Answer · 2011-12-03T08:29:00

It seems like you didn't provided the right datetime range to the query. Just validate the datetime input textboxs and you should be fine.

[EDITED]
Perhaps this can be helpful.

C#

try
    {
        SqlConnection MAconn = new SqlConnection();
        MAconn.ConnectionString = connectionString;
        MAconn.Open();
        //int c = System.Convert.ToInt32(txtamount.Text);

        SqlCommand MAcmd = MAconn.CreateCommand();

        DateTime startDate, endDate;
        DateTime.TryParse(TextBox1.Text, out startDate);
        DateTime.TryParse(TextBox2.Text, out endDate);
        if (startDate != null && endDate != null && !string.IsNullOrEmpty(startDate.ToString()) && !string.IsNullOrEmpty(endDate.ToString()) && endDate.CompareTo(startDate) >= 0)
        {
            MAcmd.CommandText = "select Sum(price) from sale where startdate between @startDate and @endDate";
            MAcmd.Parameters.AddWithValue("@startDate", startDate.ToString());
            MAcmd.Parameters.AddWithValue("@endDate", endDate.ToString());
            lblsale.Text = System.Convert.ToString(MAcmd.ExecuteScalar());
        }
        else
            lblres.Text = "oh date entered is not in range";


        MAconn.Close();
        //Response.Redirect("profit.aspx");
    }
    catch (Exception ex)
    {
        lblres.Text = "oh date entered is not in range" + ex;
    }

getting exception for dates not in range

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us