如何在 Visual Basic 2012 中为日期使用 BindingSource.Filter? [英] How to use BindingSource.Filter for a Date in visual basic 2012?

问题描述

我正在尝试过滤我的数据库以显示从我的表单上的日历中选择的日期的所有预订.这是我写的代码...

I am trying to filter my database to show all bookings for a date thats selected from a calender I have on my form. This is the code I have written...

    Public selDate As DateTime
Dim response As Integer

Public Sub FilterBooking(selDate)
    '// Here I will create a filter to for boookings on selected date from calender

    Dim dateFrom As DateTime
    Dim dateTo As DateTime

    dateFrom = selDate & " 00:00:01"
    dateTo = selDate & " 23:59:59"
    MsgBox(dateFrom)
    MsgBox(dateTo)

    Me.QueryBookingInfoBindingSource.Filter = "BookingDate >= #" & dateFrom & "# AND BookingDate <= #" & dateTo & "#"
End Sub

Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'TODO: This line of code loads data into the 'GarageDataSet.queryBookingInfo' table. You can move, or remove it, as needed.
    Me.QueryBookingInfoTableAdapter.Fill(Me.GarageDataSet.queryBookingInfo)
    'set currently selected date in the main calender to selDate variable
    selDate = mainCalender.SelectionStart.Date
    'run the following sub
    FilterBooking(selDate)
End Sub

我在调试时创建的过滤器给出了这个错误信息...

The filter i have created when debugged gives this error message...

mscorlib.dll 中发生类型为System.FormatException"的未处理异常

An unhandled exception of type 'System.FormatException' occurred in mscorlib.dll

附加信息:字符串未被识别为有效的日期时间.

Additional information: String was not recognized as a valid DateTime.

谁能告诉我我哪里出错了.

PS我也试过这个过滤器=

Can someone show me where im making a mistake.

PS I have also tried this filter =

Me.QueryBookingInfoBindingSource.Filter = "BookingDate >= #" & dateFrom.ToString("dd/MM/yyyy hh:mm:ss") & "# AND BookingDate <= #" & dateTo.ToString("dd/MM/yyyy hh:mm:ss") & "#"

推荐答案

您是否尝试过格式化日期?

Have you tried formatting your dates?

Me.QueryBookingInfoBindingSource.Filter = "BookingDate >= " & String.Format("#{0:yyyy/MM/dd HH:mm:ss}#", dateFrom) & " AND BookingDate <= " & String.Format("#{0:yyyy/MM/dd HH:mm:ss}#", dateTo)

这个恕我直言更干净，更容易阅读:

This IMHO is cleaner and easier to read:

Public Sub FilterBooking(selDate)
    Dim dateFrom As DateTime = selDate.Date
    Dim dateTo As DateTime = dateFrom.AddDays(1).Subtract(New TimeSpan(1))

    Dim filterBuilder As New StringBuilder()
    Dim filterFormat As String = "BookingDate {0} #{1:yyyy/MM/dd HH:mm:ss}#"

    With filterBuilder
        .AppendFormat(filterFormat, ">=", dateFrom)
        .Append(" AND ")
        .AppendFormat(filterFormat, "<=", dateTo)
    End With

    Me.QueryBookingInfoBindingSource.Filter = filterBuilder.ToString()
End Sub

这也将能够在不崩溃的情况下接收带有时间值的日期，而您以前的代码则不会.;) 话虽如此，由于 selDate 是在方法之外声明的，您可能不希望以参数化方法开头.

This will also be able to receive dates with time values without crashing, which your former code wouldn't. ;) That being said, since selDate is declared outside the method, you probably don't want a parameterized method to begin with.

这篇关于如何在 Visual Basic 2012 中为日期使用 BindingSource.Filter?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！