Filter Datagridview by: From Date to Date

[NigeH]

Hi all,

Having some trouble finding out where to start with this one. I have a datagridview displaying data from my database. I can search using the text box (and clear), but I also want to search by a date period if the text box is left empty. Here is what I have so far.

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        'Sample filter
        Dim dv As DataView

        If TextBox1.Text <> "" Then
            dv = New DataView(ds.Tables(0), "Sample like '" & TextBox1.Text & "'", "Sample Desc", DataViewRowState.CurrentRows)
            DataGridView1.DataSource = dv
        End If

        If TextBox1.Text = "" Then

            'Enter code here to filter dates
            ' "TestDate >= #" & DateTimePicker1.Text & "#" And "TestDate <= #" & DateTimePicker2.Text & "#")

        End If

    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        'clear Filter

        DataGridView1.DataSource = ds.Tables(0)
    End Sub

[jmcilhinney]

This is a perfect example of why you should avoid using concatenation for anything but the simplest scenarios. You have managed to confuse what goes inside the quotes and what goes outside and ended up creating a Boolean expression. If you were going to use concatenation, this:

"TestDate >= #" & DateTimePicker1.Text & "#" And "TestDate <= #" & DateTimePicker2.Text & "#"

should be this:

"TestDate >= #" & DateTimePicker1.Text & "# And TestDate <= #" & DateTimePicker2.Text & "#"

If you do it properly then it should be this:

String.Format("TestDate >= #{0}# And TestDate <= #{1}#", DateTimePicker1.Text, DateTimePicker2.Text)

or this:

$"TestDate >= #{DateTimePicker1.Text}# And TestDate <= #{DateTimePicker2.Text}#"

If you only have quotes at the beginning and the end then you can't put them in the wrong place by accident.

[NigeH]

Thanks makes sense with the quotes.

That part I did have as a comment, as I was having a play around. Still not sure how I use this to create the filter I'm after. Can I use the Dataview as I have above with the text box?

[jmcilhinney]

Don't create a new DataView. You already have a DataView. When you bind a DataTable, the data you see comes from that DataView, which is how you can sort a DataGridView bound to it. You can access it in code via the DefaultView property of the DataTable, then you just set its RowFilter property. The rest is automatic.

That said, I would suggest that you use a BindingSource. Don't create a DataSet in the first place. Just create the DataTable you need and populate it, then bind that to a BindingSource and the BindingSource to the grid. You then set the Filter property of the BindingSource.

[NigeH]

You've lost me - I'm very new to VB.net and trying to self teach. I'm not sure I have previously created a DataView, apart from in the first filter.

I have populated the DataGridview using a DataSet...

con.ConnectionString = dbProvider & dbSource
        con.Open()
        sql = "SELECT * FROM tblContacts"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Banjo")
        con.Close()

        DataGridView1.DataSource = ds.Tables(0)

So how do I go about using a Binding Source? Thanks.

[ChrisE]

this is a sample with parameters, I'm in Germany so you will have to adjust the Date Format
I used the old Northwind Database for sample Data

Imports System.Data.OleDb

Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        DateTimePicker1.Value = CDate("01.08.1995")
        DateTimePicker2.Value = CDate("05.08.1995 23:59:59")
    End Sub



    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        GetDatesBetween(CDate(DateTimePicker1.Value), CDate(DateTimePicker2.Value))
    End Sub

    Public Function GetDatesBetween(ByVal pStartDate As Date, ByVal pEndDate As Date) As DataTable
        Dim sDB As String = "E:\Northwind.mdb"
        Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=" & sDB & ";"

        Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
        Dim dt As New DataTable
        Dim err As String = Nothing
        Dim sSql As String = " SELECT OrderID, "
        sSql &= "CustomerId, "
        sSql &= "EmployeeID, "
        sSql &= "OrderDate, "
        sSql &= "RequiredDate, "
        sSql &= "ShippedDate, "
        sSql &= "ShipAddress, "
        sSql &= "ShipCity, "
        sSql &= "ShipCountry "
        sSql &= "FROM Orders "
        sSql &= "WHERE OrderDate BETWEEN @OrderDateStart AND @OrderDateEnd;"

        Using Cn
            Using cmd As New OleDbCommand With {.Connection = Cn}
                cmd.CommandText = sSql
                cmd.Parameters.AddWithValue("@OrderDateStart", pStartDate)
                cmd.Parameters.AddWithValue("@OrderDateEnd", pEndDate)
                MsgBox(pStartDate & " - " & pEndDate)
                Try
                    Cn.Open()
                    dt.Load(cmd.ExecuteReader)
                Catch ex As Exception
                    err = ex.Message
                End Try
            End Using
        End Using
        DataGridView1.DataSource = dt.DefaultView
        Return dt

    End Function
End Class

hth

[jmcilhinney]

If you already have all the data up front and you want to filter later then it's a one-liner. Add a BindingSource to your form in the designer. Create and populate a DataTable (NOT a DataSet) and bind that to the grid through the BindingSource, e.g.

myDataAdapter.Fill(myDataTable)
myBindingSource.DataSource = myDataTable
myDataGridView.DataSource = myBindingSource

To filter the data, set the Filter property of the BindingSource, e.g.

myBindingSource.Filter = $"MyColumn >= #{DateTimePicker1.Value:MM/dd/yyyy}# AND MyColumn <= #{DateTimePicker2.Value:MM/dd/yyyy}#"

Note that I have used the Value property and formatted it rather than using the Text property. That way, it will work no matter what format the DateTimePickers are using. Here in Australia, we would display the dates using dd/MM/yyyy format but the filter requires MM/dd/yyyy format, so using the Text would not work.

Note that, if you wanted to do it without a BindingSource for some misguided reason, you would declare the DataTable variable at the class level and then bind the DataTable directly to the grid:

myDataAdapter.Fill(myDataTable)
myDataGridView.DataSource = myDataTable

You would then filter using the DataView that already exists:

myDataTable.DefaultView.RowFilter = $"MyColumn >= #{DateTimePicker1.Value:MM/dd/yyyy}# AND MyColumn <= #{DateTimePicker2.Value:MM/dd/yyyy}#"

The filter expression itself is exactly the same. Note that, if you do use a BindingSource, it ends up setting the RowFilter of the DataView anyway.

Also note that I have used string interpolation for the filters, which is basically a language-native version of String.Format (it actually does compile to using String.Format). If you're using a version of VB that doesn't support it (2017 and later definitely do, not sure about 2015, 2013 and earlier don't) then you can just use String.Format:

myBindingSource.Filter = String.Format("MyColumn >= #{0:MM/dd/yyyy}# AND MyColumn <= #{1:MM/dd/yyyy}#", DateTimePicker1.Value, DateTimePicker2.Value)

[NigeH]

More than happy to take your advice and steer clear of adding another data view.

I trying to use as suggested but I get an error at the start of the program, even before adding the filter.

I get this error
System.NullReferenceException: 'Object reference not set to an instance of an object.' da was Nothing.


I am assuming because my Data adapter is empty , or is it my Data Table (dt). I've commented out my original code.

dbSource = "Data Source = " & FullDatabasePath

        con.ConnectionString = dbProvider & dbSource
        con.Open()

        'My original code
        'da = New OleDb.OleDbDataAdapter(sql, con)
        'da.Fill(ds, "AddressBook")

        'myDataAdapter.Fill(myDataTable)
        'myBindingSource.DataSource = myDataTable
        'myDataGridView.DataSource = myBindingSource

        da.Fill(dt)
        TblContactsBindingSource.DataSource = dt
        DataGridView1.DataSource = TblContactsBindingSource


        'DataGridView1.DataSource = ds.Tables(0)

[techgnome]

You still need to create the initial SQL and pass it to the dataadaptor... which you stopped doing...

        sql = "SELECT * FROM tblContacts"
        da = New OleDb.OleDbDataAdapter(sql, con)

-tg

[NigeH]

Thanks!

I still get an error when I put that back in. I'm obviously doing something silly!

System.ArgumentNullException: 'Value cannot be null.
Parameter name: dataTable'

 dbSource = "Data Source = " & FullDatabasePath

        con.ConnectionString = dbProvider & dbSource
        con.Open()
        sql = "SELECT * FROM tblContacts"
        da = New OleDb.OleDbDataAdapter(sql, con)
        'da.Fill(ds, "AddressBook")
        da.Fill(dt)
        TblContactsBindingSource.DataSource = dt
        DataGridView1.DataSource = TblContactsBindingSource

        'DataGridView1.DataSource = ds.Tables(0)

[techgnome]

Instanciate dt.... you need an instance of the datatable before it can be filled.

        dt = new DataTable() <-- this is what you're missing ... or alternatively, you can instanciate it on the same line you declared it
        da.Fill(dt)

-tg

[jmcilhinney]

Like a lot of people, I think you are confused between declaring a variable and creating an object. Any variable whose type is a class (rather than a structure) will be Nothing by default. You can't do anything with no object, so you have to create an object and assign it to the variable. Because of VB's syntax, that's not necessarily completely obvious. Here's an example of doing that long-hand:

Dim myVariable As SomeType
 
myVariable = New SomeType

The first line declares the variable and the second line creates the object and assigns it to the variable. You can shorten that into a single line on most occasions:

Dim myVariable As SomeType = New SomeType

and VB also provides a further shorthand:

Dim myVariable As New SomeType

Note the difference between that last example and the first line of the first example. People get confused and think that the New keyword is part of the declaration but it's not. It's that keyword that creates a new object. If you don't have that keyword then you're not creating an object. If you don't need to create an object, i.e. you're getting one created elsewhere, then don't use that keyword.

[NigeH]

Thanks so much both. That was it - I hadn't Instanciated dt

It is all working now - including the filters - you are right a very quick and simple way to filter.

con.ConnectionString = dbProvider & dbSource
        con.Open()
        sql = "SELECT * FROM tblContacts"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "AddressBook")
        da.Fill(dt)
        TblContactsBindingSource.DataSource = dt
        DataGridView1.DataSource = TblContactsBindingSource

One further question if you don't mind.

I was previously writing / adding data to the database by using...

dsNewRow = ds.Tables("AddressBook").NewRow()

                            dsNewRow.Item(1) = SampleSplit(0)
                            dsNewRow.Item(2) = Test
                            dsNewRow.Item(3) = WellName
                            dsNewRow.Item(4) = SplitCardInfo(4)
                            dsNewRow.Item(5) = OrgRes
                            dsNewRow.Item(6) = OrgUser
                            dsNewRow.Item(7) = Serial
                            dsNewRow.Item(8) = TestDate
                            dsNewRow.Item(9) = TestTime
                            dsNewRow.Item(10) = NewResult
                            dsNewRow.Item(11) = SplitCardInfo(1)
                            dsNewRow.Item(12) = "C:\DemoData"

                            ds.Tables("AddressBook").Rows.Add(dsNewRow)
                            da.Update(ds, "AddressBook")

Obviously now this doesn't update the DataGridView. Do I continue with this and refresh the data in the grid - or should I now write this to something else? What is best practice?

[jmcilhinney]

We just got finished talking about your creating a DataTable and binding it to the grid. Why would you then introduce code that refers to a DataSet? Have we not established that there should be no DataSet? You have a DataTable. If you want to add a row, add it to that DataTable. It's bound to the grid so the grid will reflect the change. When it's time to save the changes, save that DataTable.

Also, I can't tell you how much it annoys me when people cut the leading whitespace off the first line of a block of code and leave it on the rest. If you're going to leave all the rest then leave it on the first line too, so at least it's consistent. Better yet, don't keep any leading whitespace at all (other than indenting). When you select code in VS you can hold down the Alt key to select an arbitrary block. Make the left edge of that block line up with the first character of the left-most line of code. Boom! No fat wad of whitespace that does nothing but make the code harder to read.

[NigeH]

The code was already there from before.

I've changed it now so it writes to the DataTable, and it updates the grid. But currently it is not entering that into the database. You say save the Datatable, how do I do that, it is not

dt.Rows.Add(dsNewRow)
da.Update(dt)

[jmcilhinney]

Yes it is that, assuming you've configured the data adapter correctly. Have you done that? If you don't know how, follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data.

[NigeH]

Yes it is that, assuming you've configured the data adapter correctly. Have you done that? If you don't know how, follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data.

Thanks. I was missing

Dim builder As New OleDb.OleDbCommandBuilder(da)
builder.GetInsertCommand()

Now working. I've learnt lots - Thanks!!

[jmcilhinney]

Thanks. I was missing

Dim builder As New OleDb.OleDbCommandBuilder(da)
builder.GetInsertCommand()

Now working. I've learnt lots - Thanks!!

That second line is useless. Get rid of it.