Can anyone see a problem with this SQL statement?

Question

0.00/5 (No votes)

See more:

Hey guys,
This statement is returning no result, no error is given however it always returns 0. It should return the total amount of sales that have been paid via EFTpos, given an id and a date range.

C#

string sqleft = "SELECT SUM(SalePrice) SalePrice from Sales " + "where Stall = '" + textBox1.Text + "' AND EFTPos = 1 AND date BETWEEN '" + dateTimePicker1.Value.ToString() + "' AND '" + dateTimePicker2.Value.ToString() + "'";
                SqlCommand execsqleft = new SqlCommand(sqleft, cn);

                execsqleft.ExecuteNonQuery();
                double resulteft = 0.0d;
                result = Convert.ToDouble(execsql.ExecuteScalar());

All my other statements like this one work, so I believe there is a problem with the EFTPos column. The column data type is BIT, and will store either 1 or 0.
Here is what the statement reads during runtime

SELECT SUM(SalePrice) SalePrice from Sales where Stall = 'A5' AND EFTPos = 1 AND date BETWEEN '11/4/2014 12:00:00 AM' AND '12/4/2014 12:00:00 AM'

Thank you!

Posted 3-Dec-14 14:35pm

Member 10490060

Add a Solution

Comments

PIEBALDconsult 3-Dec-14 20:53pm

0) I hope you are not storing dates as strings.
1) If you use a parameterized statement, you won't need to convert the incoming dates to strings. And things will likely work much much better.
2) No need to use Convert, just cast the result.
3) Try the query in SSMS.

6 solutions

Add a Solution

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

BacchusBeale · Answer 1 · 2014-12-03T14:52:00

Solution 1

For aggregate functions like SUM you need to 'GROUP BY' the SalePrice with one of the other fields and the filter with your where conditions.
e.g

SQL

select sum(price) from orders group by productid where orderdate=2014

Posted 3-Dec-14 14:52pm

BacchusBeale

Comments

Member 10490060 3-Dec-14 20:58pm

Thanks! I'll give it a go :)

Garth J Lancaster · Answer 2 · 2014-12-03T14:53:00

Solution 2

well, obviously you should check each clause of the where statement on its own before combining them - for example, do a

SQL

select discrete stall

for example to make sure there's an 'A5' value, remove the EFTPos phrase from the select to see if you get results, and if you do, that's where you work next

you may wish to think about this, for the EFTPos part

EFTPos = cast(1 as bit)

Posted 3-Dec-14 14:53pm

Garth J Lancaster

Comments

Member 10490060 3-Dec-14 20:57pm

Thanks for the suggestion! I've just tried this:

string sql = "SELECT SUM(SalePrice) SalePrice from Sales " + "where Stall = '" + textBox1.Text + "' AND date BETWEEN '" + dateTimePicker1.Value.ToString() + "' AND '" + dateTimePicker2.Value.ToString() + "'";

And I have recieved the correct results. But when I add in the eftpos part, I get nothing :( So it must be something wrong with the eftpos query, right? Thanks!

Garth J Lancaster 3-Dec-14 22:14pm

yup - so you have to look at the EFTPos column - start by doing a

select unique EFTPos, count(*)

from that table and see what sort of data you have there - did you try the query with the EFTPOS = cast(1 as bit) in it ?

Sinisa Hajnal 4-Dec-14 8:22am

If you don't have default value on EFTPOS it will be null, so not only 0 and 1.

You should do ISNULL(EFTPOST, your.default.value) = 1 ... your default obviously depends on whether you want newly inserted values as 0 or 1 :)

Also, DO NOT EVER store dates as strings...there are date time columns for those things, just like there is a bit column for true/false...otherwise, why not use CHAR(1) or VARCHAR(1)?

/\jmot · Answer 3 · 2014-12-21T22:41:00

try..

SQL

string strStall=textBox1.Text;

DateTime dt1=Convert.ToDatetime(dateTimePicker1.Value.ToString());
DateTime dt2=Convert.ToDatetime(dateTimePicker2.Value.ToString());

string sqleft = "SELECT SUM(Isnull(SalePrice,0)) SalePrice from Sales where Stall = @Stall AND EFTPos = 1 AND cast(date as date) BETWEEN cast(@date1 as date) and cast((@date2 as date)";
SqlCommand execsqleft = new SqlCommand(sqleft, cn);

execsqleft.Parameters.AddWithValue("@Stall ",strStall);
execsqleft.Parameters.AddWithValue("@date1 ",dt1);
execsqleft.Parameters.AddWithValue("@date2",dt2);

double resulteft = 0.0d;
result = Convert.ToDouble(execsql.ExecuteScalar().ToString());

deepankarbhatnagar · Answer 4 · 2014-12-21T23:11:00

Solution 5

Try to use EFTPos= true instead of 1

string sqleft = "SELECT SUM(SalePrice) SalePrice from Sales " + "where Stall = '" + textBox1.Text + "' AND EFTPos =" + true + " AND date BETWEEN '" + dateTimePicker1.Value.ToString() + "' AND '" + dateTimePicker2.Value.ToString() + "'";
                SqlCommand execsqleft = new SqlCommand(sqleft, cn);
 
                execsqleft.ExecuteNonQuery();
                double resulteft = 0.0d;
                result = Convert.ToDouble(execsql.ExecuteScalar());

Posted 21-Dec-14 23:11pm

deepankarbhatnagar

Updated 21-Dec-14 23:13pm

v2

Comments

deepankarbhatnagar 23-Dec-14 7:43am

Could anyone tell me whats wrong in my ans & i degraded. When we have to write inline queries for bit field we have to write true/false for 1/0 respectively. and this query will work fine. I just dont know why administrator degrade my solution.

deepankarbhatnagar 23-Dec-14 7:46am

In above answer in which administrator give 4 start will not match the above requirement of question. actually user have problem in EFTPos = 1 in the question, and I have give the write solution in which data will filter and comes only EFTPos = true as this field is bit according to required question.

Maciej Los · Answer 5 · 2014-12-22T00:19:00

SQL query looks good, but...
Have a look at your code:

C#

double resulteft = 0.0d;
result = Convert.ToDouble(execsql.ExecuteScalar());

You have declared: resulteft, but getting result of ExcecuteScalar method into result variable.

My suggestion:

C#

double resulteft = (double)execsql.ExecuteScalar();
Console.WriteLine("Result: {0}", resulteft);

For further information, please see: SqlCommand.ExecuteScalar Method[^]

Lucifier Rocks · Answer 6 · 2014-12-19T02:00:00

Solution 3

use group by as aggregate function is being used

Posted 19-Dec-14 2:00am

Lucifier Rocks

Can anyone see a problem with this SQL statement?

6 solutions

Solution 1

Solution 2

Solution 4

Solution 5

Solution 6

Solution 3

Add your solution here

Preview 0

Can anyone see a problem with this SQL statement?

6 solutions

Solution 1

Solution 2

Solution 4

Solution 5

Solution 6

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us