Ajax Calendar extende - FIltering SQL Database results between 2 dates

Question

with the below code I am trying to select the number of unique visits by IP address between 2 dates and showing the result in a label. I m using Ajax Calendar extender to filter between dates but it doesn't work and I am been trying to solve this headache since 2 days with no chance of finding something complete in the Internet (P.S. the query does work if I use it in the query functionality of SSMS 2014):

This is my .aspx page code:

<asp:Label ID="Label1" runat="server" Text="Start Date"></asp:Label> <asp:TextBox ID="StartDateTextBox" runat="server"></asp:TextBox> <cc1:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="StartDateTextBox" Format="dd/MM/yyyy" Enabled="True"></cc1:CalendarExtender> <asp:Label ID="Label2" runat="server" Text="End Date"></asp:Label> <asp:TextBox ID="EndDateTextBox" runat="server"></asp:TextBox> <cc1:CalendarExtender ID="CalendarExtender2" runat="server" TargetControlID="EndDateTextBox" Format="dd/MM/yyyy" Enabled="True"></cc1:CalendarExtender> <asp:Button ID="showbtn" runat="server" Text="Button" onclick="showvisits"/> <asp:Label ID="uniquevisits" runat="server" Text=""></asp:Label> <asp:Label ID="failure" runat="server" Text=""></asp:Label>

And in code behind:

 Protected Sub showvisits(sender As Object, e As EventArgs) Handles showbtn.Click
    Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString)
    Try
        conn.Open()
        Dim cmd As New SqlCommand("SELECT COUNT (DISTINCT VisitIp) FROM IpStorage WHERE VisitDate BETWEEN '@StartDate' AND '@EndDate')", conn)
        cmd.Parameters.AddWithValue("@StartDate", DateTime.Parse(StartDateTextBox.Text))
        cmd.Parameters.AddWithValue("@EndDate", DateTime.Parse(EndDateTextBox.Text))
        Dim myReader As SqlDataReader = cmd.ExecuteReader()
        While myReader.Read()
            uniquevisits.Text = myReader("VisitIp").ToString()
        End While
        myReader.Close()
    Catch ex As SqlException
        failure.Text = "ERROR"
    Finally
        conn.Close()
    End Try
End Sub

Do you know what's wrong with the above code?

No particular error is thrown but the error message that I implemented above in the failure.Text label

Thanks a lot for your help

Answer 1

You just need to remove the quotes around the parameter placeholders. With quotes the placeholders become literal strings and, of course, the query fails to retrieve any record BETWEEN the strings "@StartDate" and "@EndDate"

Dim cmd As New SqlCommand("SELECT COUNT (DISTINCT VisitIp) " & _ 
                          "FROM IpStorage " & _ 
                          "WHERE VisitDate " & _ 
                          "BETWEEN @StartDate AND @EndDate", conn)

There is also an unneeded close parenthesys after the @EndData parameter that is not required and causes a syntax error when the T-SQL engine parses this query.

Another point to keep under strict control is the usage of AddWithValue. While it is an handy shortcut, it has serious drawbacks as explained in this article

Can we stop using AddWithValue already?

So

cmd.Parameters.Add("@StartDate", SqlDbType.Date).Value = DateTime.Parse(StartDateTextBox.Text)
cmd.Parameters.Add("@EndDate", SqlDbType.Date).Value = DateTime.Parse(EndDateTextBox.Text)

Pay particular attention to the SqlDbType enum to use against your table column type

Finally keep in mind that if your datebase field contains a time part the BETWEEN expression could yield incomplete results.

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

@EndDate = 2015/03/26 14:37:00 is bigger than VisitDate = 2015/03/26 14:36:59 albeit they are in the same day.