Member Avatar for CodingSource

Currently I have a button, 2 datetimepickers, a datagridview, and a combobox. What I like to do is the user will select the FromDate and ToDate and then with a keyword from a combobox it will search in a datagridivew according to the date range and the combobox's value.

I already implemented a search function for the combobox in the button click event:

 Dim con As New OleDbConnection(Get_Constring)
 Dim dt As New DataTable
 con.Open()
 Dim da As New OleDbDataAdapter("SELECT * from EncodingComplete where Client like '" & ComboBox1.Text & "%'", con)
 da.Fill(dt)
 DataGridView1.DataSource = dt
 con.Close()

How would I code the date range?

  • 2 Contributors
  • 2 Replies
  • 793 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by CodingSource

Dani AI

Generated

— is right that the date range belongs in the SQL. The safest, most reliable way is to use a parameterized query (no string concatenation) and handle the end-of-day correctly if your date column stores times. Replace [DateColumn] with your actual column name and test the wildcard (Access can use * vs % depending on ANSI mode).

Dim dt As New DataTable()

Using con As New OleDbConnection(Get_Constring)
    Dim sql As String =
        "SELECT * FROM EncodingComplete " &
        "WHERE Client LIKE ? " &
        "AND [DateColumn] >= ? AND [DateColumn] < ?"

    Using da As New OleDbDataAdapter(sql, con)
        da.SelectCommand.Parameters.Add("p1", OleDbType.VarChar).Value = If(String.IsNullOrWhiteSpace(ComboBox1.Text), "%", ComboBox1.Text & "%")
        da.SelectCommand.Parameters.Add("p2", OleDbType.Date).Value = DateTimePickerFrom.Value.Date
        ' use exclusive upper bound: < (ToDate + 1 day) so full ToDate is included even if time part exists
        da.SelectCommand.Parameters.Add("p3", OleDbType.Date).Value = DateTimePickerTo.Value.Date.AddDays(1)

        da.Fill(dt)
    End Using
End Using

DataGridView1.DataSource = dt

Notes and troubleshooting:

  • OleDb uses positional parameters (the ? placeholders), so add parameters in the same order they appear in SQL.
  • If your stored dates never include time, you can use <= ToDate instead. If they may include time, the < ToDate.AddDays(1) pattern avoids off-by-one errors.
  • If no rows appear, try switching the wildcard from % to * (Access legacy vs ANSI-92 mode).
  • Prefer explicit parameter types over string concatenation to prevent SQL injection and formatting problems.

Recommended Answers

All 2 Replies

Member Avatar for djjeavons

Hi

Do you want to include the DateRange within your SQL statement? If so, you could use the BETWEEN operator, for example:

SELECT * FROM EncodingComplete WHERE YourDateField BETWEEN #01/01/2010# AND #01/01/2011#

Note, depending on the database you are using you may need to substitue the # character with a ' character. I believe Access uses # and SQL Server uses ' but may be wrong.

HTH

Member Avatar for CodingSource

I don't include that in my SQL statement. Let me try then I will inform you.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.