vb.net 2010 sql 2005 search date error

[dakaboguy]

I am using VS2010 and SQL Server 2005. I have a query to search between dates. Depending on which date I select on the datetimepicker I get no results or limited results, but mostly not all the dates between the two dates I specify.

For example, if i select 10/9/2010 on the datetimepicker, the search results are blank.
If I select 10/13/2010, then all dates between that date and current date show up.
If I select 9/8/2010 or 9/13/2010 on the datetimepicker, I dont get any results.

I've tried multiple things and nothing seems to work. My code is below. Thanks for any help.

Dim CurrentDate = DateTime.Now.ToShortDateString()

Private Sub SearchDate()
        Dim conn As SqlConnection
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Dim dt As New DataTable

        conn = GetConnect()
        Try
            cmd = conn.CreateCommand
            cmd.CommandText = "SELECT * FROM NCR_LOG WHERE Date BETWEEN '" & Trim(dtp.Value) & "' and  '" & Trim(CurrentDate) & "' "
            da.SelectCommand = cmd
            da.Fill(ds, "NCR_LOG")
            dgNcr.DataSource = ds
            dgNcr.DataMember = "NCR_LOG"
            dgNcr.ReadOnly = True

        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
        End Try
        conn.Close()
    End Sub

I have also tried

Private Sub SearchBetweenDates()
        Dim conn As SqlConnection
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Dim dt As New DataTable

        conn = GetConnect()
        Try
            cmd = conn.CreateCommand
            cmd.CommandText = "SELECT * FROM NCR_LOG WHERE Date >= '" & Trim(txtAdate.Text) & "' and Date <= '" & Trim(txtBdate.Text) & "' And Facility = '" & Trim(cbFacility.Text) & "' ORDER BY Id DESC"
            da.SelectCommand = cmd
            da.Fill(ds, "NCR_LOG")
            dgNcr.DataSource = ds
            dgNcr.DataMember = "NCR_LOG"
            dgNcr.ReadOnly = True

        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
        End Try
        conn.Close()
    End Sub

None of the above codes seem to produce the required results.

[__avd]

Use Parameters.

..
  cmd = conn.CreateCommand
  cmd.CommandText = "SELECT * FROM NCR_LOG WHERE Date BETWEEN @p1 and @p2"
  cmd.Parameters.AddWithValue("@p1",dtp.Value)
  cmd.Parameters.AddWithValue("@p2",DateTime.Now)

  da=new SqlDataAdapter(cmd)

  da.Fill(ds, "NCR_LOG")
  dgNcr.DataSource = ds
  dgNcr.DataMember = "NCR_LOG"
  dgNcr.ReadOnly = True
..

[dakaboguy]

Thank you very much, adatapost. With the parameters it works perfect.