commandtext query in asp.net

Question

I have a month and year variables and I am getting datas with this query.

EntityDataSourcePersonel.CommandText = 
"SELECT COUNT(TeklifTable.TeklifHazirlayan) AS Basari, EmployeeTable.Name, EmployeeTable.Surname, SUM(TeklifTable.TeklifTutar) AS ToplamSatis FROM EmployeeTable JOIN TeklifTable ON TeklifTable.TeklifHazirlayan = EmployeeTable.EmployeeId WHERE MONTH(TeklifTable.TeklifTarih) = "+dtM+" AND YEAR(TeklifTable.TeklifTarih) = " + dtY + " AND TeklifTable.Approved = true GROUP BY EmployeeTable.Name,EmployeeTable.Surname";

This query works fine but I need something more. As you can see I have a where condition and I take datas which have approved as true. I am wondering if there is a way too get all datas without checking it's approved or not. I mean I need both all datas and datas which has approved as true.

I think I couldn't explained my problem. I need something like approved = true datas in a column and all datas include both approved = true and approved = false in another column. @NSNoob — Koray Durudogan
– Koray Durudogan, Commented Dec 17, 2015 at 7:47

Mr Moose · Accepted Answer · 2015-12-17 12:56:30Z

1

If you mean you want a tally of Approved and Not Approved, then you could add columns to your SELECT clause like the following;

EntityDataSourcePersonel.CommandText = 
"SELECT COUNT(TeklifTable.TeklifHazirlayan) AS Basari, 
        EmployeeTable.Name, 
        EmployeeTable.Surname, 
        SUM(TeklifTable.TeklifTutar) AS ToplamSatis,
        SUM(CASE WHEN TeklifTable.Approved = true THEN 1 ELSE 0 END) as [TotalApproved], 
        SUM(CASE WHEN TeklifTable.Approved = false THEN 1 ELSE 0 END) AS [TotalUnapproved] 
   FROM EmployeeTable 
   JOIN TeklifTable ON TeklifTable.TeklifHazirlayan = EmployeeTable.EmployeeId
  WHERE MONTH(TeklifTable.TeklifTarih) = "+dtM+" 
    AND YEAR(TeklifTable.TeklifTarih) = " + dtY + " 
GROUP BY EmployeeTable.Name,EmployeeTable.Surname";

Please look at using SqlParameters though as your current command is vunerable to SQL injection (by constructing your statement through appending dtM and dtY values).

edited Dec 17, 2015 at 12:56

answered Dec 17, 2015 at 9:10

Mr Moose

6,4447 gold badges40 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Koray Durudogan Over a year ago

I removed 'AND TeklifTable.Approved = true' from your code and it becomes exactly what I need. Thank you very much :)

Mr Moose Over a year ago

No problems. I meant to remove that line too...I just forgot. Whoops :)

Collectives™ on Stack Overflow

commandtext query in asp.net

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related