CODEWITHSUNDEEP
c#asp.netsqlsql-server-2008-r2
Tech Lover
Tech Lover

Reputation: 79

How to display the search results based on two dates in the ListView?

I am trying to use AjaxToolKit CalendarExtender control with two textboxes in order to develop search filter. The first TextBox is for the (startdate) and the second one will be for the finish date, and after clicking on the (Search) button, the results should be shown in a ListView with adding a checkbox at the first column of the ListView.

FYI, the database design is as following:

Employee Table: Username, Name...
SuggestionsLog: ID, Title, Description, DateSubmitted, StatusID
SuggestionsStatus: ID, Status

(DateSubmitted is a datetime datatype field)

The StartDate and EndDate will be based on the DateSubmitted column in SuggestionsLog table in the database. The problem is the format of the date selected from the Ajax CalendarExtender is as (July 2, 2012). Also, I wrote the query which is:

SELECT     dbo.SafetySuggestionsLog.Title, dbo.SafetySuggestionsLog.Description, dbo.SafetySuggestionsLog.Username, dbo.SafetySuggestionsLog.DateSubmitted, 
                      dbo.SafetySuggestionsStatus.Status
FROM         dbo.SafetySuggestionsLog INNER JOIN
                      dbo.SafetySuggestionsStatus ON dbo.SafetySuggestionsLog.StatusID = dbo.SafetySuggestionsStatus.ID
WHERE     (dbo.SafetySuggestionsLog.DateSubmitted = @startDate) AND (dbo.SafetySuggestionsLog.DateSubmitted = @finishDate)

I want to show the results in the ListView, however, nothing is displayed on the ListView and I don't know why. Could you please help me with this?

ASP.NET:

<div>
                    <asp:Label ID="Label2" runat="server" Text="From: " />
                    <asp:TextBox ID="txtStartDate" runat="server" />
                    <asp:CalendarExtender ID="CalendarExtender1" runat="server" Enabled="True" 
                        Format="MMMM d, yyyy" TargetControlID="txtStartDate">
                    </asp:CalendarExtender>

                    <asp:Label ID="Label3" runat="server" Text="To: " />
                    <asp:TextBox ID="txtEndDate" runat="server" /> 
                    <asp:CalendarExtender ID="CalendarExtender2" runat="server" Enabled="True" 
                        Format="MMMM d, yyyy" TargetControlID="txtEndDate">
                    </asp:CalendarExtender>
                    <asp:Button ID="searchButton" runat="server" Text="Search" 
                        onclick="searchButton_Click" />

                    <asp:ListView ID="FilteredSuggestions" runat="server"></asp:ListView>
                </div>

Code-behind:

protected void searchButton_Click(object sender, EventArgs e)
    {
        string connString = ConfigurationManager.ConnectionStrings["testConnectionString"].ConnectionString;
        SqlConnection conn = new SqlConnection(connString);
        string cmd = @"SELECT     dbo.SafetySuggestionsLog.Title, dbo.SafetySuggestionsLog.Description, dbo.SafetySuggestionsLog.Username, dbo.SafetySuggestionsLog.DateSubmitted, 
                                 dbo.SafetySuggestionsStatus.Status
                        FROM         dbo.SafetySuggestionsLog INNER JOIN
                                    dbo.SafetySuggestionsStatus ON dbo.SafetySuggestionsLog.StatusID = dbo.SafetySuggestionsStatus.ID
                        WHERE     (dbo.SafetySuggestionsLog.DateSubmitted = @startDate) AND (dbo.SafetySuggestionsLog.DateSubmitted = @finishDate)";

        SqlDataAdapter sda = new SqlDataAdapter(cmd,conn);
        sda.SelectCommand.Parameters.AddWithValue("@startDate", txtStartDate.Text);
        sda.SelectCommand.Parameters.AddWithValue("@finishDate", txtEndDate.Text);
        DataSet ds = new DataSet();
        sda.Fill(ds, "table");

        FilteredSuggestions.DataSource = ds.Tables["table"];
        FilteredSuggestions.DataBind();
    }

Upvotes: 0

Views: 1690

Answers (2)

IrishChieftain
IrishChieftain

Reputation: 15253

BETWEEN assumes midnight when the day begins:

T-SQL Between Dates Confusion

So it will effectively exclude the @finishDate. Be aware of that if you are using the 'Between' operator. If this is not what you want use the '<', '>', '<=', '>=' operators instead when establishing your time window.

WHERE (NOT((finishDate <= @startDate) OR (startDate > @finishDate)))

Upvotes: 1

Kapil Khandelwal
Kapil Khandelwal

Reputation: 16144

Try this:

Use BETWEEN to specify the date range

SELECT     dbo.SafetySuggestionsLog.Title, dbo.SafetySuggestionsLog.Description, dbo.SafetySuggestionsLog.Username, dbo.SafetySuggestionsLog.DateSubmitted, 
                      dbo.SafetySuggestionsStatus.Status
FROM         dbo.SafetySuggestionsLog INNER JOIN
                      dbo.SafetySuggestionsStatus ON dbo.SafetySuggestionsLog.StatusID = dbo.SafetySuggestionsStatus.ID
WHERE     (dbo.SafetySuggestionsLog.DateSubmitted Between @startDate AND @finishDate)

Upvotes: 1

Related Questions