Hi everyone,

I need help in selecting between date ranges. I have two dateTimepicker and want to retrieve only records selected between selected dateTimepicker.I tried but im getting all records. I'm using SQL server database. Here is my code:

selectQuery = "SELECT * FROM EmployeeRegistration WHERE EmpJoinDate BETWEEN '" & DateTimeFrom.Value.Date & "' And '" & DateTimeTo.Value.Date & "' ORDER BY EmpJoinDate"
cmd = New SqlCommand(selectQuery, conn)
SqlDataAdapter1.Fill(DataSetViewEmp, "EmployeeRegistration")
DataGridView2.DataSource = DataSetViewEmp.Tables("EmployeeRegistration").DefaultView

NO need to wrrap datatimepicker value by single quotation mark.
For string type variable you can use it. To use date you must use #.
Use parameterised query to avoid this type of hazards.

selectQuery = "SELECT * FROM EmployeeRegistration WHERE EmpJoinDate BETWEEN @fromDate And @toDate ORDER BY EmpJoinDate"
cmd.Parameters.AddWithValue("@fromDate", DateTimeFrom.Value.Date)
cmd.Parameters.AddWithValue("@toDate", DateTimeTo.Value.Date)

Hope it can help you.

I tried the above code and I'm getting "object reference not set to an instance of an object "error.I'm not sure what I'm doing wrong

The codes should be

selectQuery = "SELECT * FROM EmployeeRegistration WHERE EmpJoinDate BETWEEN @fromDate And @toDate ORDER BY EmpJoinDate"
cmd = New SqlCommand(selectQuery, conn)
cmd.Parameters.AddWithValue("@fromDate", DateTimeFrom.Value.Date)
cmd.Parameters.AddWithValue("@toDate", DateTimeTo.Value.Date)

This time i'm not getting any errors, except the datagridview displays all records, of which i only need selected datetimepicker records to be displayed.

It's datatype is (datetime) and I also tried with the (date datatype)

Edited 1 Year Ago by mbowenitj

I have modified my code and now i'm getting "Invalid column name ToDate" Error. I think im getting closer but not sure how to fix this error
Following is my code:

selectQuery = "SELECT * FROM EmployeeRegistration WHERE EmpJoinDate BETWEEN @FromDate And ToDate ORDER BY EmpJoinDate"
cmd = New SqlCommand(selectQuery, conn)
cmd.Parameters.AddWithValue("@FromDate", DateTimeFrom.Value.Date)
cmd.Parameters.AddWithValue("@ToDate", DateTimeTo.Value.Date)
Dim SqlDataAdapter1 As New SqlDataAdapter(cmd)
SqlDataAdapter1.Fill(DataSetViewEmp, "EmployeeRegistration")
DataGridView2.DataSource = DataSetViewEmp.Tables("EmployeeRegistration").DefaultView

Edited 1 Year Ago by Reverend Jim: Corrected code formatting

Set a breakpoint at

SqlDataAdapter1.Fill(DataSetViewEmp, "EmployeeRegistration")

then in the immediate window type

?cmd.CommandText
?cmd.Parameters(0).Value
?cmd.Parameters(1).Value

and post the output here.

Edited 1 Year Ago by Reverend Jim

The "@" symbol was missing on my code but now i the datagrid still shows all columns.

If you install SQL Management Studio (free from Microsoft) you can debug your queries in an interactive window and then code up the same query in your app.

This article has been dead for over six months. Start a new discussion instead.