如何使用ms访问在C＃中的两个日期之间选择数据 [英] How to select data between two dates in C# using ms access

问题描述

大家好！我想知道如何在我使用查询的两个日期之间选择数据，但我得到空数据请帮助我



我尝试过：



DataSet dsa = new DataSet（）;

DataTable dt1 = new DataTable（）;

dsa .Tables.Add（dt1）;

OleDbDataAdapter da = new OleDbDataAdapter（）;

da = new OleDbDataAdapter（SELECT [Column1]，[Column2]，[Column3] ，[日期]，[收货号码]，[交货人]来自[总计]其中[日期]之间的+ dateTimePicker2.Value.ToShortDateString（）+AND＃+ dateTimePicker3.Value.ToShortDateString（）+＃， VCON）;

da.Fill（dt1）;

Hello Guys! i want to know how to select data between two dates i am using query but i get null data please help me

What I have tried:

DataSet dsa = new DataSet();
DataTable dt1 = new DataTable();
dsa.Tables.Add(dt1);
OleDbDataAdapter da = new OleDbDataAdapter();
da = new OleDbDataAdapter("SELECT [Column1],[Column2],[Column3],[Date],[Receipt No],[Delivery Person] from [Total] Where [Date] between " + dateTimePicker2.Value.ToShortDateString() + " AND #" + dateTimePicker3.Value.ToShortDateString() + "#", VCON);
da.Fill(dt1);

推荐答案

您应该学习尽快使用调试器。而不是猜测你的代码在做什么，现在是时候看到你的代码执行并确保它完成你期望的。



调试器允许你跟踪执行逐行检查变量，你会看到它有一个停止做你期望的点。

调试器 - 维基百科，免费的百科全书 [ ^ ]

掌握Visual Studio 2010中的调试 - A初学者指南 [ ^ ]



调试器在这里向您展示您的代码正在做什么，您的任务是与它应该做什么进行比较。 />
当代码不做ex的时候你接近一个bug。



使用调试器查看你真正拥有的查询。

你会看到第二个日期嵌入在＃中，第一个日期不是，这足以使查询失败。

还要确保2个日期为字符串的格式为预期格式。



否则，按照你的方式构建查询是一个坏主意，因为它打开了sql注入的大门。

SQL注入 [ ^ ]

You should learn to use the debugger as soon as possible. Rather than guessing what your code is doing, It is time to see your code executing and ensuring that it does what you expect.

The debugger allow you to follow the execution line by line, inspect variables and you will see that there is a point where it stop doing what you expect.
Debugger - Wikipedia, the free encyclopedia[^]
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]

The debugger is here to show you what your code is doing and your task is to compare with what it should do.
When the code don't do what is expected, you are close to a bug.

Use the debugger to see what is the query you really have.
You will see that the second date is embedded in "#" and the first is not, This is enough to make the query fail.
Also make sure the 2 dates as strings are in the expected format.

Otherwise, building the query the way you do is a bad idea because it open the door to sql injection.
SQL Injection[^]

试试这个

try this

da = new OleDbDataAdapter("SELECT [Column1],[Column2],[Column3],[Date],[Receipt No],[Delivery Person] from [Total] Where [Date] between #" + dateTimePicker2.Value.ToShortDateString() + "# AND #" + dateTimePicker3.Value.ToShortDateString() + "#", VCON);

如果要发布架构和一些示例数据这会有所帮助。如果没有看到我无法访问的数据库，我可能会猜测根本原因。



但是为了澄清我将分享一个例子，你可以在sql server中运行（我没有访问抱歉）。



这个例子更多的是在你的sql查询中显示时间戳的问题。

If you would post your schema and some sample data that would help. Without seeing your database, which i don't have access to, i am making some likely guesses as to the root cause.

But to clarify i'll share an example you can run in sql server (i don't have access sorry).

This example is more to show the issue of time stamps in your sql query.

DECLARE @Dates TABLE 
(
 Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 DateExample DATETIME NULL
);

INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 20:30:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 19:00:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 18:30:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 16:00:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 15:00:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 14:30:32.843')

--This returns nothing due to the time stamps being midnight and the time stamps in the data being
SELECT * FROM @Dates WHERE DateExample BETWEEN '2016-09-08 00:00:00.000' AND '2016-09-08 00:00:00.000'

--Returns results because it converts dateexample to a date, which makes the timestamps midnight.
SELECT * FROM @Dates WHERE CAST(DateExample AS DATE) BETWEEN '2016-09-08 00:00:00.000' AND '2016-09-08 00:00:00.000'

第一个选择语句考虑了时间戳，因为你正在剥离你的时间您的日期被传递，您将上午12:00的日期与其他日期进行比较，并带有时间戳，这可能是您没有返回任何数据的原因。


可能出现的另一个问题是您将日期转换为字符串，并且可能无法正常访问。



第二个查询将dateexample列转换为日期这使得时间戳上午12:00，并使用该查询。



鉴于T-SQL无法很好地转换为MS Access，您有2个选项



1）确保您在BETWEEN条款中的日期包括符合您所查找标准的时间戳。您可能需要将00:00:00和24：59：59.59附加到BETWEEN语句的开始和结束日期，以获取所有有效数据。



2）我认为MS Access可能具有将字符串转换为日期的功能，从而剥离它的时间戳。我没有安装MS访问权限，所以我无法测试这个，但我认为这可以通过快速谷歌搜索。



ms访问CAST - Google搜索 [ ^ ]





再次，我无法访问你的数据库所以这可能是关闭的。如果是，那么随时提供架构并澄清您的问题。

The first select statement takes into account time stamps, and since your are stripping your times off of your dates being passed in , your comparing a date at 12:00am to other dates with a time stamp which is likely the cause of you not having any data returned.

The other issue that might be arising is you are converting a date to a string and that may not jive well with access.

The second query converts the dateexample column to date which makes the timestamp 12:00am and works with that query.

Given that T-SQL doesn't convert well to MS Access you have 2 options

1) Make sure your dates in your BETWEEN clause include time stamps that meet the criteria of what you are looking for. You may need to append 00:00:00 and 24:59:59.59 to your start and end dates of your BETWEEN statement in order to get all valid data.

2) I think MS Access may have functions that convert strings to date and in turn strip the time stamp off of it. I don't have MS access installed so i can't test this but I think this is possible from a quick google search.

ms access CAST - Google Search[^]


Again, i don't have access to your DB so this may be off. If it is then feel free to provide schema and clarify your issue.

这篇关于如何使用ms访问在C＃中的两个日期之间选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！