Click here to Skip to main content
12,501,337 members (37,526 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# SQL
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?
Posted 19-Dec-10 20:35pm
wolfsor564
Updated 20-Dec-10 10:18am
v8
Comments
Toli Cuturicu 20-Dec-10 16:19pm
   
Why not use a parameterized query, like everyone else does?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

No, not the best way. You should use parameterized query[^] with your command.
  Permalink  
Comments
MCY 20-Dec-10 3:39am
   
yeah
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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'.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Hi,


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.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

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.
  Permalink  
Comments
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160919.1 | Last Updated 20 Dec 2010
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100