CODEWITHSUNDEEP
c#sqlasp.netsql-serverdate
Koray Durudogan
Koray Durudogan

Reputation: 634

Display dates between two dates in asp.net

I have two calendars in my aspx and I want to display records between selected dates of these calendars. My 'TeklifTarih' database attribute is a date type attribute. Here is my aspx:

<asp:Calendar ID="Calendar1" runat="server"></asp:Calendar><br />
<asp:Calendar ID="Calendar2" runat="server"></asp:Calendar><br/>
<asp:Button ID="btnClendar" runat="server" Text="İstatistikleri Filtrele" OnClick="btnClendar_Click"/>

And my onclick method:

protected void btnClendar_Click(object sender, EventArgs e)
    {
        string baslangicTarihi = Calendar1.SelectedDate.ToString();
        string bitisTarihi = Calendar2.SelectedDate.ToString();
        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 TeklifTable.TeklifTarih >= " + baslangicTarihi + " AND TeklifTable.TeklifTarih <= " + bitisTarihi + " GROUP BY EmployeeTable.Name,EmployeeTable.Surname";
    }

I need to display datas with this commandtext and it works when I remove where command of query but I need to filter with these two dates.

Upvotes: 0

Views: 838

Answers (3)

Suprabhat Biswal
Suprabhat Biswal

Reputation: 3216

You have missed single quotes (') while creating a sql statement your query should be like following:-

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 TeklifTable.TeklifTarih >= '" + baslangicTarihi + "' 
 AND TeklifTable.TeklifTarih <= '" + bitisTarihi + "' 
 GROUP BY EmployeeTable.Name,EmployeeTable.Surname";

Upvotes: 1

Sami
Sami

Reputation: 2110

Combined with the other answer pointing out you're missing the quotes, and the datetime formatting, the following should work: (not tested though)

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 TeklifTable.TeklifTarih >= '" + baslangicTarihi.ToString("yyyy-MM-dd") + "' 
AND TeklifTable.TeklifTarih <= '" + bitisTarihi.ToString("yyyy-MM-dd") + "' 
GROUP BY EmployeeTable.Name,EmployeeTable.Surname";

Also consider Tim's point about parameterized queries, you should use them whenever possible.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460138

I must admit that i'm not familiar with the EntityDataSource control. But according to what i've read you can also use parametrized queries. So do it:

String sql = @"SELECT Count(TeklifTable.TeklifHazirlayan) AS Basari, 
       EmployeeTable.NAME, 
       EmployeeTable.Surname, 
       Sum(TeklifTable.TeklifTutar)        AS ToplamSatis 
FROM   EmployeeTable 
       JOIN TeklifTable 
         ON TeklifTable.TeklifHazirlayan = EmployeeTable.EmployeeId 
GROUP  BY EmployeeTable.NAME, 
          EmployeeTable.Surname";
EntityDataSourcePersonel.CommandText = sql;
String whereClause = @"TeklifTable.TeklifTarih >= @baslangicTarihi 
                   AND TeklifTable.TeklifTarih <= @bitisTarihi";
EntityDataSourcePersonel.Where = whereClause;
EntityDataSourcePersonel.WhereParameters.Add(new Parameter("baslangicTarihi", TypeCode.DateTime,  Calendar1.SelectedDate));
EntityDataSourcePersonel.WhereParameters.Add(new Parameter("bitisTarihi", TypeCode.DateTime,  Calendar2.SelectedDate));

Upvotes: 0

Related Questions