如何找到201-may-2018到25-may-2018这两个日期？ [英] How to find between two dates like 01-may-2018 to 25-may-2018?
问题描述
当我过滤日期时，如2018年5月1日到2018年5月31日，那么结果显示可能会在一月完成，但其显示的其他月份的日期为2018年4月2日，所有日期都在01to31的数据网格中。
i只能找到一个月到一个月。
这样的节目
02-Apr-2018
03-Apr-2018
01-May-2018
08-May-2018
10-May-2018
12-May-2018
31-May-2018
15-Jun -2018
28-Jun-2018
?????
我有什么试过：
< pre> 私有 功能 GetRows（）作为 DataTable
Dim DateStr As DateTime = DateTimePicker1.Value。日期
Dim DateEnd As DateTime = DateTimePicker2.Value。日期
Dim dtable 作为 DataTable = 新 DataTable
Dim con1 As OleDbConnection
' 尝试
con1 = 新 OleDbConnection（ Provider = Microsoft.Jet.OLEDB.4 .0;数据来源=& Application.StartupPath& \ WB001.mdb; Jet OLEDB：数据库密码= jhk312;）
使用 cmd1 作为 OleDbCommand = 新 OleDbCommand（ SELECT * FROM DW1 WHERE Date BETWEEN＃& DateStr.ToString& ＃And＃& DateEnd.ToString& ＃，con1）
' 使用cmd1.Parameters
' 。添加（@ dt1，OleDbType.Date） .Value = DateTimePicker1.Value
' .Add（@ dt2，OleDbType.Date ）.Value = DateTimePicker2.Value
' 以结尾
Dim adp As 新 OleDbDataAdapter（cmd1）
' con1.Open（）
adp.Fill（dtable）
DataGridView1.DataSource = dtable
con1.Close（）
' 结束使用
结束 使用
' Catch ex As Exception
' MsgBox（错误）
' 结束尝试
< span class =code-keyword>结束 功能
最可能的原因是数据库中的设计不好。从您显示的值看，数据库中的日期列看起来像是VARCHAR或NVARCHAR而不是DATE。
当您这样做时，您对日期的比较将是字符串比较，这意味着整个比较基于两个字符串中的第一个不同字符。没有检查其他字符。
这意味着普通整数的排序顺序变为：
1
10
11
...
...
29
3
30
...日期会发生非常类似的排序顺序。
解决方案很简单：更改数据库设计，并始终存储值适当的数据类型。整数值int INT，FLOAT或DECIMAL中的浮点，DATE或DATETIME中的日期。如果你不这样做，每次尝试使用它们都会有麻烦...
正如理查德所说：永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。总是使用参数化查询。
连接字符串时会导致问题，因为SQL会收到如下命令：
SELECT * FROM MyTable WHERE StreetAddress = ' Baker' s Wood ' < span class =code-string>就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：
SELECT * FROM MyTable WHERE StreetAddress = ' x'; DROP 表 MyTable; - '哪个SQL看作三个单独的命令：
SELECT * FROM MyTable WHERE StreetAddress = ' x';完全有效的SELECT
DROP TABLE MyTable;完全有效的删除表格通讯和
- '其他一切都是评论。
所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。
所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。您是否定期进行备份，不是吗？
As OriginalGriff [ ^ ]提到，您必须将Date字段更改为适当的数据类型。注意：您还需要更改其名称，因为日期是保留字 [ ^ ]用于MS Access。使用保留字可能会导致一些问题...
如何更改数据类型？
1.在独家模式 [ ^ ]
2 。在设计模式下打开DW表格
3.添加新字段，例如：DateOfEvent并选择日期数据类型（允许空值）
4.保存更改并关闭DW表
5.创建新查询并执行：
更新 DW SET DateOfEvent = CDate（日期）
6.如果更新成功，您可以删除日期字段
好吧，分别为 Richard Deeming [ ^ ]关于 Sql Injection [ ^ ]，你必须使用参数化查询而不是连接字符串：
似乎，你非常接近正确的解决方案。以下是代码的改进版本：
Dim sCon 作为 字符串 = 字符串 .Format（ Provider = Microsoft.Jet.OLEDB.4.0; Data Source = {0} \WB001.mdb; Jet OLEDB：Database Password = jhk312;，Application.StartupPath）
Dim sSql As String = SELECT * FROM DW1 WHERE DateOfEvent BETWEEN @ dt1 and @ dt2;
使用 con1 作为 OleDbConnection = 新 OleDbConnection（sCon）
con1.Open（）
使用 cmd1 As OleDbCommand = 新 OleDbCommand（sSql，con1）
使用 cmd1.Parameters
.Add（ @ dt1，OleDbType。 Date ）。Value = DateTimePicker1.Value
.Add（ @ dt2，OleDbType。日期）。值= DateTimePicker2.Value
结束 使用
Dim adp As 新 OleDbDataAdapter（ cmd1）
adp.Fill（dtable）
DataGridView1.DataSource = dtable
结束 使用
con1.Close（）
结束 使用
注意：即使是官方文档说明OleDb提供程序不允许使用命名参数，您可以使用它们，但有一个要求：您必须按照与sql语句中的顺序相同的顺序添加参数。 ;）
When i Filter Date like 01-May-2018 To 31-May-2018 then result show may month complete but its show other months date to like 02-Apr-2018 and all date in 01to31 in datagridview.
i have to find only month To month.
its show like this
02-Apr-2018
03-Apr-2018
01-May-2018
08-May-2018
10-May-2018
12-May-2018
31-May-2018
15-Jun-2018
28-Jun-2018
?????
What I have tried:
<pre> Private Function GetRows() As DataTable
Dim DateStr As DateTime = DateTimePicker1.Value.Date
Dim DateEnd As DateTime = DateTimePicker2.Value.Date
Dim dtable As DataTable = New DataTable
Dim con1 As OleDbConnection
' Try
con1 = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\WB001.mdb;Jet OLEDB:Database Password=jhk312;")
Using cmd1 As OleDbCommand = New OleDbCommand("SELECT * FROM DW1 WHERE Date BETWEEN #" & DateStr.ToString & "# And #" & DateEnd.ToString & "#", con1)
' With cmd1.Parameters
'.Add("@dt1", OleDbType.Date).Value = DateTimePicker1.Value
' .Add("@dt2", OleDbType.Date).Value = DateTimePicker2.Value
' End With
Dim adp As New OleDbDataAdapter(cmd1)
' con1.Open()
adp.Fill(dtable)
DataGridView1.DataSource = dtable
con1.Close()
'End Using
End Using
' Catch ex As Exception
'MsgBox("Error")
' End Try
End FunctionThe most likely reason is bad design in your DB. From the values you show, it looks like your date column in the DB is VARCHAR or NVARCHAR rather than DATE.
When you do that, your comparisons for dates will be string comparisons, which means the entire comparison is based on the first different character in the two strings. No further characters are checked.
That means that the sort order for "normal intgers" becomes:
1 10 11 ... 19 2 20 21 ... 29 3 30 ...And a very similar sort order happens with dates.
The solution is simple: change your DB design, and always store values in appropriate datatypes. Integer values int INT, floating point in FLOAT or DECIMAL, dates in DATE or DATETIME. If you don't you get hassle every time you try to use them...
And as Richard said: Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.
When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';A perfectly valid SELECT
DROP TABLE MyTable;A perfectly valid "delete the table" command
--'And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.
So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
As OriginalGriff[^] mentioned, you have to changeDatefield to appropriate data type. Note: you also need to change its name, becauseDateis reserved word[^] for MS Access. Using reserved words may cause several problems...
How to change data type?
1. Open database in exclusive mode[^]
2. OpenDWtable in design mode
3. Add new field, for example:DateOfEventand selectDatedata type (allow nulls)
4. Save changes and closeDWtable
5. Create new query and execute:
UPDATE DW SET DateOfEvent = CDate(Date)
6. If update has been successful, you can removeDatefield
Well, respectivelly to Richard Deeming[^] 's comment about Sql Injection[^], you have to use parameterized queries instead of concatenated strings:
Seems, you were pretty close to proper solution. Here is "improved" version of your code:
Dim sCon As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\WB001.mdb;Jet OLEDB:Database Password=jhk312;", Application.StartupPath) Dim sSql As String = "SELECT * FROM DW1 WHERE DateOfEvent BETWEEN @dt1 And @dt2;" Using con1 As OleDbConnection = New OleDbConnection(sCon) con1.Open() Using cmd1 As OleDbCommand = New OleDbCommand(sSql, con1) With cmd1.Parameters .Add("@dt1", OleDbType.Date).Value = DateTimePicker1.Value .Add("@dt2", OleDbType.Date).Value = DateTimePicker2.Value End With Dim adp As New OleDbDataAdapter(cmd1) adp.Fill(dtable) DataGridView1.DataSource = dtable End Using con1.Close() End Using
Note: Even if official documentation states that OleDb provider does not allow to use named parameters, you can use them, but there's one requirement: you have to add parameters in the same order as their order in sql statement. ;)
这篇关于如何找到201-may-2018到25-may-2018这两个日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
