[RESOLVED] Change a sql query to a datagridview filter to search between dates

[Clutch001]

Hi - I'm having nothing but problems using databases so I'm trying to use xml to populate a datagridview- which works fine, but now I need to change my sql query to filter the datagrid if that's possible.
The datagrid just has 2 columns - a datetime and a text. I need to search between 2 dates. All my google searches gives me projects with 2 datetimepickers.

  Dim startDate As DateTime = New Date(currentDate.Year, currentDate.Month, 1)
        Dim endDate As DateTime = startDate.AddMonths(1).AddDays(-1)

        Dim sql As String = $"select * from calendar where CalDate between #{startDate.ToShortDateString()}# and #{endDate.ToShortDateString()}#"

Is it possible to change that sql query to a datagrid filter? Thanks - If all else fails I'll add a second datetimepicker and make it invisible :/

[jmcilhinney]

There is no such thing as a "datagridview filter" or "datagrid filter". Whether you're using a database (if you're having issues then you should try fixing them, not changing to an inferior solution) or an XML file, you can populate a DataTable with the data, bind that to a BindingSource and then bind that to the DataGridView. You can then set the Filter property of the BindingSource to filter the data it exposes. If it's bound to a grid then that grid will only see the exposed data.

Whether or not you're using zero, one or two DateTimePickers is completely irrelevant to the filtering part. You filter using two Date values and where those Date values come from is completely irrelevant. They might come from the Value properties of DateTimePicker controls or you might create/calculate them some other way but that has exactly zero bearing on the filtering. For instance, you might write this method to do the filtering:

Private Sub FilterByDateRange(startDate As Date, endDate As Date)
    myBindingSource.Filter = $"CalDate BETWEEN #{startDate:MM/dd/yyyy}# AND #{endDate:MM/dd/yyyy}#"
End Sub

and then call it in multiple ways:

Private Sub FilterBySelection()
    FilterByDateRange(startDatePicker.Value, endDatePicker.Value)
End Sub
 
Private Sub FilterByCurrentMonth()
    Dim currentDate = Date.ToDay
    Dim startDate As New Date(currentDate.Year, currentDate.Month, 1)
    Dim endDate = startDate.AddMonths(1).AddDays(-1)
 
    FilterByDateRange(startDate, endDate)
End Sub

As you can see, the code to get the date range and the code to filter based on that date range are completely independent. Being able to break down your problem into these independent parts and address each one separately is one of the most important skills you can develop as a new programmer, so you should concentrate on that. Doing so means that you are solving smaller and simpler problems so you can solve more of them yourself because they are easier to research and you can ask more focused questions about the ones you can't solve.

As a final note, it is wrong to call ToShortDateString to format the dates in that situation. Date literals are required to be in MM/dd/yyyy format (slight variations like M/dd/yyyy may be OK) and so ToShortDateString will fail on any system that doesn't use that as its standard short date format. That means almost every machine outside the USA and some within as well.

[Clutch001]

Thanks for your response. I will try to use that with my current code. I really haven't done much programming since the vb6 days using access mdb files. Most of my database problems seem to stem from using Windows 7 64bit.
I may have worded my question a bit wrong. I'm just making a simple office program for business (to print envelopes, keep track of workdays, etc) so it only needs to work on my machine. I'm mostly doing it for fun to pass the time during the plandemic, and so I don't have to open Quickbooks so often. This part is a simple calendar based off someone else's code, and i was trying to find a way to parse the dates in the datagrid to add labels to those days on the calendar. I don't have to use xml, but I just want a stand alone file for data. At the most there might be about 12 records a month - just a date and a text. I do have a dataset datatable and binding source connected to the datagrid. That info is in an xml file, and I was hoping to pull the data from bindingsource instead of reading the xml file directly into the calendar. Now that I wrote that I'm not sure if it makes sense. Thanks again

[.paul.]

Can you show us the xml (or a good example of the structure)? It's possible to read and write data between xml and a datatable. Also, which version of VB are you using? jmcilhinney's example won't work with all versions...

EDIT: I just noticed VB2017. jm's code should work fine...