0

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

3
Contributors
11
Replies
44
Views
1 Year
Discussion Span
Last Post by Reverend Jim
0

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.

0

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

0

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)
0

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.

0

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

Edited by mbowenitj

0

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 by Reverend Jim: Corrected code formatting

0

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 by Reverend Jim

0

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

0

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 topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.