我想从datetime字段中找出给定时间之间的月度销售 [英] I want to find out monthly sale for between given times from datetime field

问题描述

我正在使用vs2008和后端ms-access

表bdate中有两个字段（仅限商店日期），结束时间（商店日期和时间）



我想在给定时间内显示两个日期之间的表格记录。

我使用datetimepicker作为条件。没有找到记录......



Pl给出解决方案



我尝试过：

I am using vs2008 and backend ms-access
There are two fields in a table bdate (store date only), endtime (store date and time)

I want to display records from table between two dates for given time only.
I am using datetimepicker for condition. No records found...

Pl give solution

What I have tried:

SELECT * FROM sale where bdate between #" & Format(fromtime.Value, "dd/MMM/yyyy") & "# and #" & Format(totime.Value, "dd/MMM/yyyy") & "# and (endtime between #" & fromtime.Value.ToLongTimeString & "# and " & totime.Value.ToShortTimeString & "#)"

推荐答案

首先，停止这样做！永远不要连接字符串来构建一个SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏整个数据库。请改用参数化查询。



当你连接字符串时，你会导致问题，因为SQL收到如下命令：

For starters, stop doing that! 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. Use Parametrized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

就SQL而言，用户添加的引用会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

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;--'

哪个SQL看作三个单独的命令：

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

完全有效的SELECT

A perfectly valid SELECT

DROP TABLE MyTable;

完全有效的删除表格通讯和

A perfectly valid "delete the table" command

--'

其他一切都是评论。

所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。



所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你定期进行备份，不是吗？



在这种情况下，你可以使用它，因为DateTimePicker只会返回有效日期，但即使这样，它也是一个穷人想法，因为如果你扩展这个应用程序以使用基于服务器的数据库，那么你传递的实际日期很容易被错误地解释。养成为每个数据库访问使用参数的习惯，很多未来的问题都会消失。



如果还意味着你没有得到＃字符的数量你需要错...它应该是偶数！使用参数，您不需要它们，并且阅读起来也更容易。

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?

In this case you get away with it because a DateTimePicker only ever returns valid dates, but even then it's a poor idea because if you leter expand this app to use a server based database then the actual date you pass over can very easily be interpreted wrongly. get into the habit of using parameters for every DB access and a lot of future problems disappear.

If also means you don't get the number of "#" characters you need wrong ... it should be an even number! With parameters, you don't need them and it's all a lot easier to read as well.

这篇关于我想从datetime字段中找出给定时间之间的月度销售的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！