使用 DateTimePicker 从 Access 数据库检索记录时数据类型不匹配

发布于 2024-08-25 19:26:26 字数 367 浏览 11 评论 0原文

当我尝试使用 C# 中的 DateTimePicker 从两个日期之间的访问数据库中检索记录时，出现数据类型不匹配条件表达式错误。

这是选择语句

else if (dtpDOBFrom.Value < dtpDOBTo.Value)
{
    cmdSearch.CommandText = "SELECT [First Name], [Surname], [Contact Type], [Birthdate] FROM [Contacts] WHERE [Birthdate] >= '" + dtpDOBFrom.Value +"' AND [Birthdate] <= '" + dtpDOBTo.Value +"'";
}

原文

I get a Data type mismatch criteria expression error when i try to retrieve records from an access database between two dates using a DateTimePicker in C#.

This is the Select statement

else if (dtpDOBFrom.Value < dtpDOBTo.Value)
{
    cmdSearch.CommandText = "SELECT [First Name], [Surname], [Contact Type], [Birthdate] FROM [Contacts] WHERE [Birthdate] >= '" + dtpDOBFrom.Value +"' AND [Birthdate] <= '" + dtpDOBTo.Value +"'";
}

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

过期以后 2024-09-01 19:26:26

dtpDOBFrom.Value 是一个 DateTime，您正尝试将其粘贴到字符串中。因此，DateTime 被转换为字符串，但格式似乎与 Access 期望的格式不同。您可以使用 DateTime.ToString 的参数（并将日期括在 # 而不是 ' 中，因为这是 Access 想要的），但这不是正确的做法。

一般来说，避免此类类型转换问题（以及 SQL 注入等）的正确方法是使用参数：

在您的情况下，它看起来像这样（未经测试）：

cmdSearch.CommandText = "SELECT [First Name], [Surname], [Contact Type], [Birthdate] FROM [Contacts] WHERE [Birthdate] >= ? AND [Birthdate] <= ?";
cmdSearch.Parameters.AddWithValue("DOBFrom", dtpDOBFrom.Value);
cmdSearch.Parameters.AddWithValue("DOBTo", dtpDOBTo.Value);

使用 OLEDB（用于 MS Access 数据库访问），参数的名称（“DOBFrom”）并不重要，但添加它们的顺序必须与 SQL 中问号的顺序匹配。

dtpDOBFrom.Value is a DateTime, which you are trying to paste into a string. Thus, the DateTime is converted into a string, but the format seems to be different from the format that Access expects. You could play with the parameters of DateTime.ToString (and enclose the date in # instead of ', since this is what Access wants), but that would not be the right way to do it.

The right way, which avoids such type cast problems in general (and SQL injection, among other things), is to use parameters:

In your case, it would look something like this (untested):

cmdSearch.CommandText = "SELECT [First Name], [Surname], [Contact Type], [Birthdate] FROM [Contacts] WHERE [Birthdate] >= ? AND [Birthdate] <= ?";
cmdSearch.Parameters.AddWithValue("DOBFrom", dtpDOBFrom.Value);
cmdSearch.Parameters.AddWithValue("DOBTo", dtpDOBTo.Value);

With OLEDB (which is used for MS Access database access), the name of the parameters ("DOBFrom") is not important, but the order in which you add them must match the order of the question marks in the SQL.

回复收藏 0 原文

~没有更多了~