dear friends i want to filter data by date ... see the attach pictures, after using where clause i receicve 0 rows :(

Attachments after.jpg 25.05 KB before.jpg 59.28 KB

Use DataView class and RowFilter property of it, to specify the filter.
But before that fill the dataTable and use databinding (datatable has to be a binding source of datagridview control).

You can find an example here.
bye

Since your check in date is actually a date+time you can't just go ahead and say check_in = '3/11/2012' because this translates to '3/11/2012 00:00:000'
You should make sure that either your filter gets into account only the date part or filter for check_in between '3/11/2012' and '3/12/2012'

Please share the code you've used in this form to see how you are filtering your data and offer more advice.

yes i m using date and time picker .. and i need only date filter .. i means all the records which are coming in a date ..
here is the code

On load event ....
.
Imports System.Data.SqlClient
Imports System.IO
Public Class frmCheckInChecout
Private Sub frmCheckInChecout_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim sql As String
Dim obj As New dbclass
Dim tbl As New DataTable
sql = "select * from tblSchdule "
tbl = obj.GetRecords(sql)
With DataGridView1
.DataSource = tbl

On filter event
.
Private Sub btnGenrate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenrate.Click
Dim sql As String
Dim obj As New dbclass
Dim tbl As New DataTable
If CboSearch.Text = "Check In" Then
sql = "select * from tblSchdule where CheckIn like '" & DateTimePicker2.Value.ToShortDateString() & "'"
tbl = obj.GetRecords(sql)
With DataGridView1
.DataSource = tbl
.Columns(0).Width = 80
.Columns(0).HeaderText = "File No"
.Columns(1).Width = 130
.Columns(1).HeaderText = "Hotel Name"
.Columns(2).Width = 90
.Columns(2).HeaderText = "Room Type"
.Columns(3).Width = 90
.Columns(3).HeaderText = "Room Size"
.Columns(4).Width = 90
.Columns(4).HeaderText = "Check In"
.Columns(5).Width = 90
.Columns(5).HeaderText = "Check Out"
.Columns(6).Width = 90
.Columns(6).HeaderText = "Total Nights"

End With

...


thanks for your time dear

Like won't do the trick by itself. In order for like to work you need a wildcard character in your string ('%' or '_').
Since you are not using any your like is the same as =

I am not sure what db you are using, so I'll use VB to set the criteria although it can be done via SQL.

Change

sql = "select * from tblSchdule where CheckIn like '" & DateTimePicker2.Value.ToShortDateString() & "'"

to

sql = "select * from tblSchdule where CheckIn >'" & DateTimePicker2.Value.ToShortDateString() & "' and CheckIn < '" & DateTimePicker2.Value.AddDays(1).ToShortDateString()&"'"

just change your query with this.

sql = "select * from tblSchdule where CheckIn between '" & DateTimePicker2.Value.ToShortDateString() & " and '" & DateTimePicker2.Value.ToShortDateString() & "'"

Regards

no result waqas dear ... empty tale ... is its better to use mankes text box instead of DateAndTimePicker ???

try this

sql = "select * from tblSchdule where CheckIn between '" & DateTimePicker2.Value.date.ToString() & " and '" & DateTimePicker2.Value.date.ToString() & "'"

Hope this will work fine.

Regards

Dear friends this one worked perfectly .....
.
VB.NET Syntax (Toggle Plain Text)
sql = "select * from tblSchdule where CheckIn >'" & DateTimePicker2.Value.ToShortDateString() & "' and CheckIn < '" & DateTimePicker2.Value.AddDays(1).ToShortDateString()&"'"sql = "select * from tblSchdule where CheckIn >'" & DateTimePicker2.Value.ToShortDateString() & "' and CheckIn < '" & DateTimePicker2.Value.AddDays(1).ToShortDateString()&"'"


thans alot friends :-)

You are welcome.
For future readers:
The above post shows the statement twice. You only need it once (stop before sql = in the second line)

Edited 4 Years Ago by adam_k: n/a

This question has already been answered. Start a new discussion instead.