Click here to Skip to main content
15,303,572 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
cmd.CommandText = "Select * from Orders2 where ((month([OrderDate])>= '" + textBox1.Text + "') and (year([OrderDate])>= '" + txtyear1.Text + "')) AND ((month([OrderDate])<= '" + textBox2.Text + "') and (year([OrderDate])<= '" + txtyear2.Text + "'));";

I used the command above to show sales in crystal reports according to date, but when I input these:

textBox1 as "12",
txtyear1 as "2010",

textBox2 as "01",
and txtyear2 as "2011",
the crystal report didn't show anything.
But, it works when I input:

textBox1 as "11",
txtyear1 as "2010",

textBox2 as "11" or "12",
and txtyear2 as "2011"

what could I do to improve the command without using parameterized query?
Updated 20-Dec-10 10:18am
Toli Cuturicu 20-Dec-10 16:19pm
Why not use a parameterized query, like everyone else does?

No, not the best way. You should use parameterized query[^] with your command.
MCY 20-Dec-10 3:39am
Sandeep Mewara 20-Dec-10 4:54am
Ok. Answer to your changed question:
So without using parameterized query, to start with you can see what should be the query at runtime to get back the result. Run the query string formed in the comman in your SQL and see if it works. Make the changes to the query such that command is formed correctly and you get back expected result.
If your different Textbox contains only value of Month and Year only then It is feasible. If you Textbox contains full Date then you also need to convert then Either that in a month or Year accordingly, Otherwise comparison will not be done.

Or as an alternate you could use DATEDIFF[^] function which will give timespan in days,month or year between two dates and furthermore you could compare whether it is between valid range or not.

Please vote and Accept Answer if it Helped.
I think taht better way is:
1. Create custom calendar control, with have property like "DateTime? Value {get; set;}" and no more use two textbox controls.
2. Create two DateTime objects: 1) startDate, 2) endDate = endDate.AddDay(1).AddSeconds(-1).
3. Use sql with parameterized query like 'select * from table where date between @fromDate and @endDate'.

my recommendation is try to use the parameterized query and try to avoid sql functions to find the month and year. Try to give your input as single value.
Create from txtBox1 and txtYear1 the beginning date, from txtBox2 and txtYear2 the ending date (you have to use add 1 month to the second date and then subtract 1 - this is eom of the month) and then you can simplify the select statement to use it with the between clause.
wolfsor 20-Dec-10 5:46am
can you cite an example?

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900