C# sql command can not search the Total Cost

▲
0
▼

I am designing a form to show the total cost on the listbox. I find that there is a error when I try to run this command

my purpose is show the courseID , numberOfStudent and the Total cost(the sum of column "operatingCost" and column"materialFee")

I want to search the TotalCost between two value.(the value of "price1" textbox and "price2" textbox) for example search all the totalCost between 700 and 1000

but after running the command there is an error shown on the message box

the error message is about ErrorSystem.Date.OleDB.OleDbExceptionError(0x80040E14) select query Operator sum(operatingCost + materialFee) >='700' andd <='1000' missing Operator

 OleDbCommand cmd = new OleDbCommand("select courseId ,numOfStudent,sum( operatingCost + materialFee) AS Total_Cost from course group by courseId , numOfStudent having sum(operatingCost + materialFee) >= '" + price1.Text + "' and =<'" + price2.Text + "'", connection);

Where is my problem?Can somebody tell me please?

c#
mysql
exception
sum
between
asked on Stack Overflow Oct 15, 2015 by Adonis.C

3 Answers

▲
2
▼

There is an error on your SQL. The correct use:

having sum(operatingCost + materialFee) between x and y

or

having sum(operatingCost + materialFee) >= x and sum(operatingCost + materialFee) <= y

Sources:

http://www.w3schools.com/sql/sql_and_or.asp

http://www.w3schools.com/sql/sql_where.asp

http://www.w3schools.com/sql/sql_between.asp

answered on Stack Overflow Oct 15, 2015 by Zack Stone • edited Oct 15, 2015 by Zack Stone
▲
1
▼
  "... having sum(operatingCost + materialFee) between " + price1.Text + " and " + price2.Text, connection);

unquoted

answered on Stack Overflow Oct 15, 2015 by juankirr
▲
1
▼

Remember for every And condition, it's a new condition. therefore, you will need to define the column again with the your condition.

Example: SELECT * FROM [Products] WHERE Price >='2'and Price <='10'

you should use Total_Cost instead of

sum(operatingCost + materialFee)

answered on Stack Overflow Oct 15, 2015 by Steven Meow De Claude • edited Oct 15, 2015 by Steven Meow De Claude

User contributions licensed under CC BY-SA 3.0