Selecting between dates sql

[mbowenitj]

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

[Santanu.Das]

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.

[mbowenitj]

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

[Santanu.Das]

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)

[mbowenitj]

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.

[Reverend Jim]

How is EmpJoinDate defined in the database?

[mbowenitj]

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

[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

[Reverend Jim]

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.

[mbowenitj]

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

[Reverend Jim]

Read my last post.

[Reverend Jim]

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.