从Access数据库中按VB.NET窗体中的日期搜索条件选择行 [英] Selecting rows from Access database by date search criteria in VB.NET form

问题描述

我有一个非常简单的问题。我的VB.NET表单上有几个datepicker控件，用户选择startDate和endDate，并显示相关表中的所有行都有一个 orderDate 在用户选择的开始和结束日期之间。

以下是相关代码：

 code> Private Sub generate_report_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理generate_report.Click 
尝试
 Dim con As New OleDb.OleDbConnection 
 con.ConnectionString =Provider = Microsoft.ACE.OLEDB.12.0; Data Source = D：\KHMSDB.accdb
 con.Open（）
 Dim sql As String 
 Dim selected As String = 
 Dim ds As DataSet = New DataSet 
 
 Dim适配器作为新的OleDb.OleDbDataAdapter 
 sql =SELECT OrderDate AS订单日期和时间，项目AS有序项目 FROM Orders WHERE格式（Orders.OrderDate，'mm / dd / yyyy'）> =＃+ startDate.Value.Date +＃A ND格式（Orders.OrderDate，'mm / dd / yyyy'）< =＃+ endDate.Value.Date +＃
 adapter.SelectCommand = New OleDb.OleDbCommand（sql，con）
 adapter.Fill（ds）
 gridReport.DataSource = ds.Tables（0）
 
 Catch ex As Exception 
 
 MsgBox（Operation failed。 + ex.ToString）
 
结束尝试
  

如果我保存一个新的在日期之前的数据库中，我将开始和结束日期作为默认日期（即今天的日期），它不显示刚刚保存的新行。今天输入的新行只显示如果我将开始日期移动到11月30日，然后在12月12日之前添加一个新行，再次，当我选择结束日期为> = 12月12日，它不会显示，当我将开始日期提升到12月1日时，我才会显示，我决定在11月21日输入一行，并在11月21日开始和结束日期运行查询显示该行，然后输入一行1月8日..现在任何组合up upDate和或向下移动endDate只是不显示1月的订单。发生了什么事实我已经在11月之前尝试过这个代码，它的工作非常好！ / p>

解决方案

这是什么可能是字符串格式的问题。我不知道Access将会将字符串转换为Date，然后比较或将Date转换为字符串，然后进行比较。您可以尝试这样：

格式（Orders.OrderDate，'mm / dd / yyyy'）> =格式（＃+ startDate。 Value.Date +＃，'mm / dd / yyyy'）

或者你总是可以直接使用日期

OrderDate> =＃+ startDate.Value.Date +＃

修改 :,做我的尽职调查，你真的应该这样做这样的查询

 code> OrderDate> = @StartDate 
  

然后添加此代码

  adapter.Parameters.Add（@ StartDate，startDate.Value.Date）; 
  

使用参数对于强大的代码很重要，并避免可怕的SQL注入攻击。

I have a very simple problem. I have a couple of datepicker controls on my VB.NET form and users select "startDate" and "endDate", and all rows from the related table are displayed which have an orderDate between the user's selected start and end dates.

The following is the relevant code:

Private Sub generate_report_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles generate_report.Click
    Try
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\KHMSDB.accdb"
        con.Open()
        Dim sql As String
        Dim selected As String = ""
        Dim ds As DataSet = New DataSet

        Dim adapter As New OleDb.OleDbDataAdapter
        sql = "SELECT OrderDate AS `Order Date and Time`, Items AS `Ordered Items` FROM Orders WHERE Format(Orders.OrderDate,'mm/dd/yyyy') >= #" + startDate.Value.Date + "# AND Format(Orders.OrderDate,'mm/dd/yyyy') <= #" + endDate.Value.Date + "#"
        adapter.SelectCommand = New OleDb.OleDbCommand(sql, con)
        adapter.Fill(ds)
        gridReport.DataSource = ds.Tables(0)

    Catch ex As Exception

        MsgBox("Operation failed. " + ex.ToString)

    End Try

If I save a new row in the database under today's date, and I leave the "start" and "end" dates both as the default date (i.e. today's date), it doesn't show the new row I just saved. The new row entered today only shows up if I move the "start date" up to the 30th of November. Then I add a new row with date 12th December. Again, it won't show up when I select the end date to be >= 12 December, it'll only show up when I move the start date up to 1st December. I decided to enter a row dated 21st November, and running the query with start and end date both on 21st November shows up that row. I then entered a new row in January 8th.. and now any combination of moving up startDate and or moving down endDate just doesn't display the January order. What's going on?? I've actually already tried this code out before in November and it worked perfectly fine!

解决方案

What could be the issue with this is the string format. I'm not sure if Access will convert the string to a Date and then compare or convert the Date to a string and then compare. You can try this:

Format(Orders.OrderDate,'mm/dd/yyyy') >= Format(#" + startDate.Value.Date + "#,'mm/dd/yyyy')

Or you could always just use the date directly,

OrderDate >= #" + startDate.Value.Date + "#"

Edit:, to do my due diligence, you really should be doing the query like this

OrderDate >= @StartDate

Then add this code

adapter.Parameters.Add("@StartDate", startDate.Value.Date);

Using parameters is important for robust code and to avoid the dreaded SQL injection attack.

这篇关于从Access数据库中按VB.NET窗体中的日期搜索条件选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！