Filter Datagridview by Interval Between Dates in VB.NET

Asked

Viewed 2,143 times

1

I’m new to programming and I’m finishing a job, but I have a problem that I’ve been facing for weeks and I haven’t been able to solve.

I created a simple system that registers the Oss (service orders), and in a form I put a datagrid that pulls all OS.

I made a filter that is to find the employees I type in the text box, but it searches all the services done by the name typed, so I need to know how to make a second filter, where I will inform the initial and final date.

Example: I need to know what the employee did, so I put the employee name in the box and filter, and the services appear on datagrid, however need to know how to make another filter to filter only the dates, then put the dates in another field with a range. ex: 10/10/2014 until 10/20/2014. I have tried some ways but I could not at all.

Could someone explain me, please? I am using VB.NET and Access 2003.

Code:

Dim cn As New OleDb.OleDbConnection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Firebird.mdb"
cn.Open()

Dim dt1 As DateTime = DateTimePicker1.Value.Date
Dim dt2 As DateTime = DateTimePicker2.Value.Date
'MsgBox(dt1)
Try

    MsgBox(dt1 + " " + dt2)

    Dim cmd As OleDbCommand
    Dim dt As DataTable
    Dim Da As OleDbDataAdapter
    With Cmd
        .CommandType = CommandType.Text
        .CommandText = "select * FROM OS where data between @datainicial AND @datafinal"
        .Parameters.AddWithValue("@datainicial", dt1)
        .Parameters.AddWithValue("@datafinal", dt2)
        .Connection = cn
    End With

    With OSTableAdapter.Adapter
        .SelectCommand = cmd
        dt = New DataTable
        .Fill(dt)
        OSDataGridView.DataSource = dt
    End With

    cn.Close()
Catch ex As Exception
    MsgBox(ex.Message)
End Try
winforms vb.net ms-access
  • 1

    Could you put in your question an example code?

  • I tried using this code here.. http://pastebin.com/65eS1m0L . however.. I don’t think I need to search the database.. where datagrid already has the results filtered by employee name... I don’t know if I could filter only the dg'data' field.

  • You want to filter the result on DataTable, that’s it?

  • I have a Datagridview.. on it is filtered the name of the employee with all the services performed by him ... I need now filter the range of dates, I need to see the services made by this employee during a certain period... then I tried to include two Datetimepicker one being the initial date, and another the Final Date... I used this code inside the boot filter. However... it didn’t work, I don’t know if I really need to search the bank.. or if you have to filter only the Datagrid column, without having to re-query the database.

2 answers

0


No need to pass dates as parameter. You can use the property RowFilter of his DataGridView as follows:

DirectCast(OSDataGridView.DataSource, DataTable).DefaultView.RowFilter = "data > #" & DateTimePicker1.Value & "# and data < #" & DateTimePicker2.Value & "#"

  • Rowfilter output did not appear, I typed all the code as above but after Osdatagridview. , the only options I have with Row .. not one is this.

  • @Vyniciushenrique You are right. I changed the answer.

  • gave a syntax problem with this "(" of the beginning...

  • I didn’t test this code, so. I updated it again.

  • an error occurred during the test ... It was not possible to convert an object of type "System.Windows.Forms.Bindingsource" in type "System.Data.Datatable", I will try to put the Datetimepicker.value in - Datetimepicker.value.Date

  • even so it did not work. I do not understand what the reason for the error,

  • You need to take the property that has your data DataGridView as a DataTable. In it has DefaultView.RowFilter, which is where the filter actually happens. I’m not being able to test this code for you now.

  • FirebirdDataSet.OS.DefaultView.RowFilter = "data > #" & DateTimePicker1.Value & "# and data < #" & DateTimePicker2.Value & "#" " Character string not recognized as valid datetime. "

  • @Vyniciushenrique Now it is easier. Only convert the values of the DateTimePicker: DateTimePicker1.Value.Date.

  • Gypsy, had tried too , however it did not work, think it can be in the bank, where I left as text the field ?

Show 5 more comments

0

You can also filter through the datatable, after the query without the date filters.

For that just create something like:

Dim filtro as String = "data >= '" & dt1 & "' and data <= '" & dt2 & "'"
Dim drFiltro() as Datarow = dt.Select(filtro)
OSDataGridView.DataSource = drFiltro

The solution that the colleague commented on using rowFilter should also work for Dataviews. The advantage of using Dataview is that you can have different filters in the data without changing the original data, that is, without having to make a new query every time you want to make a new filter.

Browser other questions tagged winforms vb.net ms-access

You are not signed in. Login or sign up in order to post.