当我在两个日期之间获取数据时，我收到错误 [英] I am getting error when I get data between two dates

问题描述

Hello Guys，



我在这样的两个日期之间获取数据时收到错误



附加信息：查询表达式中的日期语法错误'[员工姓名] ='sajid lakha'和[日期]> =＃22/12/2016 05：14：30：PM＃AND [日期]< ＃22/12/2016 05：14：30：PM'。



我尝试了什么：

Hello Guys,

I am getting error when i get data between two dates like this

Additional information: Syntax error in date in query expression '[Employee Name] = 'sajid lakha' AND [Date] >= #22/12/2016 05:14:30:PM# AND [Date] < #22/12/2016 05:14:30:PM'.

What I have tried:

DataSet dsa = new DataSet();
    DataTable dt = new DataTable();
    dsa.Tables.Add(dt);
    OleDbDataAdapter da = new OleDbDataAdapter();
    da = new OleDbDataAdapter("SELECT [Employee Name],[Column1],[column2],[column3],[Flavours],[Date] from [Total] where [Employee Name] = '" + employeedata + "' AND [Date] >= #" + dateTimePicker1.Text+ "# AND [Date] < #" + dateTimePicker1.Text + "#", connection);
    da.Fill(dt);
    dataGridView1.DataSource = dt;
    connection.Close();

推荐答案

两个字：参数化查询。



谷歌针对SQL注入攻击找出为什么使用字符串连接会使你的数据库面临风险。



然后谷歌为C＃参数化查询有关如何解决该问题并使用日期时间字符串修复问题的示例。

Two words: "parameterized queries".

Google for "SQL Injection attack" to find out why using string concatenation is putting your database at risk.

Then Google for "C# Parameterized queries" for examples on how to fix that problem and fix your problem with the date time strings.

Quote：

查询表达式中的日期语法错误'[员工姓名] ='sajid lakha'和[日期]> =＃22/12/2016 05：14：30：PM＃AND [日期]< ＃22 / 12/2016 05：14：30：PM'。

Syntax error in date in query expression '[Employee Name] = 'sajid lakha' AND [Date] >= #22/12/2016 05:14:30:PM# AND [Date] < #22/12/2016 05:14:30:PM'.

在错误消息中，第二个日期缺少＃最后。由于某些原因，最后的＃不是应该的位置。

否则，您的查询是完全错误的，因为2个日期是相同的，不包括任何回答。



永远不要建立一个SQL查询

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

In error message, the second date is missing the # at the end. For some reasons the last # is not where it should.
Otherwise, your query is plain wrong because the 2 dates are the same, excluding any answer.

Never build a SQL query that way
SQL injection - Wikipedia[^]
SQL Injection[^]

正如已经指出的那样，参数化是查询工作的基本键正常。这不仅有助于防止SQL注入，还有助于解决可能的转换问题。例如，考虑日期的本地化是否不同。



另一个问题是缺少使用块。它们应该用于确保正确放置像OleDbConnection和OleDbDataAdapter这样的对象。请注意，您已经初始化了两次数据适配器



如果日期范围的两端都包含在条件中也会更容易。



因此代码应该类似于

As already pointed out, parameterization is elemental key for the query to work properly. This does not only help to prevent SQL injections but also helps with possible conversion issues. For example consider if the localization of the dates is different.

Another problem is the lack of using blocks. They should be used to ensure that the objects like OleDbConnection and OleDbDataAdapter are disposed correctly. Note that you have initialized the data adapter twice

Also it would be easier if both ends of the date range would be included in the condition.

So the code should look something like

DataSet dsa = new DataSet();
DataTable dt = new DataTable();
dsa.Tables.Add(dt);
string sqlQuery = 
using OleDbconnection connection = new OleDbConnection(...)) 
   using (OleDbCommand command = new OleDbCommand ()) {
      command.Connection = connection;
      command.CommandText = @"SELECT [Employee Name],[Column1],[column2],[column3],[Flavours],[Date] 
FROM [Total] 
WHERE [Employee Name] = @name AND [Date] BETWEEN @start AND @end";
      command.Parameter.Add("@name", OleDbType.VarChar, 100).Value = employeedata;
      command.Parameter.Add("@start", OleDbType.Date).Value = DateTime.Parse(dateTimePicker1.Text).Date;
      command.Parameter.Add("@end", OleDbType.Date).Value = DateTime.Parse(dateTimePicker2.Text).Date;
   
      using (OleDbDataAdapter da = new OleDbDataAdapter(sqlQuery, connection)) {
         da.Fill(dt);
      }
   }
   connection.Close();
}
dataGridView1.DataSource = dt;

代码可能包含错误，这只是一个例子。



其他说明：

- 在原始代码中，您似乎使用dateTimePicker1中的值来查询查询中的起始和结束限制。我在示例中使用了dateTimePicker1和dateTimePicker2

- 为变量和对象使用有意义的名称。这使得调试和维护代码变得更加容易。例如，使用StartDatePicker或类似的东西代替dateTimePicker1

- 特别是在数据库对象中使用有意义的名称。拥有像Column1或Column2这样的列名使得查询很难理解并确保正确使用正确的字段。

The code may contain errors, it's just an example.

Other notes:
- In your original code you seem to use value from dateTimePicker1 for both start and end limits in your query. I've used dateTimePicker1 and dateTimePicker2 in the example
- Use meaningful name for the variables and the objects. This makes debugging and maintaining the code much easier. For example instead of dateTimePicker1 use StartDatePicker or something similar
- especially use meaningful names in the database objects. Having column names like Column1 or Column2 makes it really hard to understand the queries and to ensure that correct fields are used properly.

这篇关于当我在两个日期之间获取数据时，我收到错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！