Addion on the bases of start date and end date.

Question

0.00/5 (No votes)

See more:

i am trying to filter the the data on the bases of start date and end date.. and on the bases of that filter i am performing addition(Sum) of price..

i have written the test code.. but its not working with start date and end date..

ignore ~~concentination~~ concatenation (Sql Injection) i just wrote this before implementing it on my project..

here is the test code.. what should i amend to make that code work.

here is the Code..

C#

protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection MAconn = new SqlConnection();
        MAconn.ConnectionString = connectionString;
        MAconn.Open();
        
        SqlCommand MAcmd = MAconn.CreateCommand();

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

        
        MAconn.Close();

    }

Posted 3-Dec-11 3:19am

codegeekalpha

Updated 3-Dec-11 15:21pm

Eduard Lu

v2

Add a Solution

2 solutions

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)

Umairbaig · Answer 1 · 2011-12-03T03:56:00

Solution 1

No Doubt the code is perfect. but you are taking StartDate and EndDate as string. but if you see in sql Server you may have set its dataType to DateTime. so you have to convert this string to dateTime and then pass it in Query. instead of using textbox, i would prefer to use DateTimePicker.

Posted 3-Dec-11 3:56am

Umairbaig

Updated 3-Dec-11 4:18am

v3

Comments

codegeekalpha 3-Dec-11 10:30am

i am using string in sql server as well.. and i can't use date time picker in asp.net.. i am using ajax control tool kit..

Richard MacCutchan 3-Dec-11 10:49am

If you are using string in SQL server then the only date calculations or comparisons you can do are for string equality. This is bad design and should be rectified as soon as possible; see my comments above.

codegeekalpha 5-Dec-11 4:29am

5+

Richard MacCutchan · Answer 2 · 2011-12-03T03:58:00

Solution 2

C#

MAcmd.CommandText = "select Sum(price) from sale where startdate='"+TextBox1.Text+"' and startdate='"+TextBox2.Text+"'";

startdate cannot be equal to both values, unless they are the same string; is that what you expect?

I would also guard against using text strings for date comparisons, you should always use DateTime values as they cannot be misinterpreted. Strings of the form "10/12/2011" are ambiguous; is that 10th of December or October 12th?

Posted 3-Dec-11 3:58am

Richard MacCutchan

Comments

codegeekalpha 3-Dec-11 10:31am

no this is the format which is used in my country.. 10/december/2011

Richard MacCutchan 3-Dec-11 10:45am

And that is the whole point of my comment, strings vary from country to country; do not use strings for date values, always use DateTime objects.