How to show all Records between two dates (VB.Net)

I have a Form and a Local database and Table1 in DataGridView. Also I have 2 DatetimePickers and a button.

The Date column is defined as DateTime datatype.

How to show the records between two dates in DataGridView by pressing button1?

I would like to do this with DataGridView Task --> Add Query... --> Query Builder...

If this is not simple, any 开发者_高级运维solution is welcome.

Here is a solution (by me and Google searching).

This is simple way.

In DataGridView --> Add Query... --> Query Builder... In Date column (or whatever you have) in Filter cell write: BETWEEN @Date1 AND @Date2. Click OK and again click OK.

In Form area you see now a new line under Form title area with 2 text boxes. First is Date1 and second is Date2. After text boxes area is a button named Fillby. Change the name of Date1, Date2 and Fillby from properties to whatever you like e.g. From:- To: - Search.

An other way to do this is more difficult.

First put 2 buttons and 2 Timepickers in Form area.

Next in DataGridView --> Add Query... --> Query Builder... In Date column (or whatever you have) in Filter cell write: BETWEEN @Date1 AND @Date2. Click OK and again click OK.

In Code View area of Form paste this code for first Button (Button1):

Public Function GetFromDate(ByVal value As DateTime) As DateTime
        Return New DateTime(value.Year, value.Month, value.Day, 0, 0, 0, 0)
    End Function

    Public Function GetToDate(ByVal value As DateTime) As DateTime
        Return New DateTime(value.Year, value.Month, value.Day, 23, 59, 59, 999)
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim BeginningDateTime As Date = GetFromDate(DateTimePicker1.Value)
        Dim EndingDateTime As Date = GetToDate(DateTimePicker2.Value)
        Try
            Me.Table2TableAdapter.FillBy(Me.Database1DataSet1.Table2, New System.Nullable(Of Date)(CType(BeginningDateTime, Date)), New System.Nullable(Of Date)(CType(EndingDateTime, Date)))
     Catch ex As System.Exception
         System.Windows.Forms.MessageBox.Show(ex.Message)
     End Try
    End Sub

And next this code for second Button (Button2):

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Table2TableAdapter.Fill(Me.Database1DataSet1.Table2)
    End Sub

You have to Change:

1) Table2TableAdapter in what is your Table Adapter.

2) Database1DataSet1 in what is your Database DataSet.

3) Table2 in what is your Table.

The code FillBy , DateTimePicker1 , DateTimePicker2 must change if you have difference in your database.

For more formations of your code you can see the code from your Query Builder. It looks like:

Private Sub FillByToolStripButton_Click...

There you can find all info's to change the code above.

After all you can delete this code (Private Sub FillByToolStripButton_Click...).

That's all i have found and i solve my problem for searching between two dates.

The simplest and easiest method...

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    'Filter the Connections Date wise
    Me.TblUserBindingSource.Filter = "doj >= '" & DateTimePicker1.Value & "' and doj <= '" & DateTimePicker2.Value & "'"

end sub