在 VB.NET 表单中按日期搜索条件从 Access 数据库中选择行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13769768/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting rows from Access database by date search criteria in VB.NET form
提问by user961627
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 orderDatebetween the user's selected start and end dates.
我有一个非常简单的问题。我的 VB.NET 表单上有几个 datepicker 控件，用户选择“startDate”和“endDate”，然后显示相关表中的所有行，这些行在orderDate用户选择的开始日期和结束日期之间。
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!
如果我在今天的日期下在数据库中保存一个新行，并将“开始”和“结束”日期都保留为默认日期（即今天的日期），它不会显示我刚刚保存的新行。只有当我将“开始日期”移至 11 月 30 日时，今天输入的新行才会显示。然后我添加一个日期为 12 月 12 日的新行。同样，当我将结束日期选择为 >= 12 月 12 日时它不会显示，它只会在我将开始日期移至 12 月 1 日时显示。我决定输入日期为 11 月 21 日的一行，并在 11 月 21 日运行开始和结束日期的查询会显示该行。然后我在 1 月 8 日输入了一个新行。现在向上移动 startDate 和或向下移动 endDate 的任何组合都不会显示一月的顺序。这是怎么回事？？一世'
回答by Kratz
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:
这可能是字符串格式的问题。我不确定 Access 是否会将字符串转换为日期，然后比较或将日期转换为字符串，然后进行比较。你可以试试这个：
Format(Orders.OrderDate,'mm/dd/yyyy') >= Format(#" + startDate.Value.Date + "#,'mm/dd/yyyy')
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 + "#"
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.
使用参数对于健壮的代码和避免可怕的 SQL 注入攻击很重要。
