如何根据我的datetimepicker值过滤数据库的日期 [英] How to filter database with date based on my datetimepicker value

问题描述

在我的表格中我有一个名字，年龄，日期。 Date列数据类型是date。

所以我希望有一个按钮，每当我点击它时，显示所有具有相同Date值的行，基于我选择过滤datetimepicker的值。

它将显示在datagridview上。



我尝试过：

：



con.open（）



SqlDataAdapter sda = new SqlDataAdapter（select * from mydb where Date ='+ datetimepicker1.value +'，con）;

DataTable tb = new DataTable（）;

sda.fill（dt）;

mydatagridview.DataSource = dt;

con.Close（）;

解决方案

< blockquote>这会有效，但这是一个坏主意。永远不要连接字符串来构建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; 

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

   -   ' 

其他一切都是评论。

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



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



所以即使对于不容易出现SQL Inject的值，也应该使用参数 - 因为在这种情况下它防止混淆，因为数据库服务器和应用程序可能在具有非常不同的区域性设置的计算机上运行，​​因此代码生成的默认字符串可能与数据库服务器计算机设置的日期格式不匹配：如果您使用MM- dd-yy和服务器使用dd-MM-yy然后你将检索错误的信息。



如果你修复后根本没有得到任何数据这整个应用程序中的数据，然后仔细检查数据库中的数据，并检查日期列不包含任何时间信息（或它设置为午夜） - 等于比较需要值相同，直到勾选，而不仅仅是当天。

in my tables I a have columns Name, Age, Date. The Date column datatype is date.
So I want to have a button that whenever I click it displays all the rows with the same Date value base on the value I choose to filter my datetimepicker.
It will display on the datagridview.

What I have tried:

in my event handler button :

con.open()

SqlDataAdapter sda = new SqlDataAdapter ("select * from mydb where Date = '"+datetimepicker1.value+"'", con);
DataTable tb = new DataTable();
sda.fill(dt);
mydatagridview.DataSource = dt;
con.Close();

解决方案

That will work, but it's a bad idea. 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?

So even for values that can't be SQL Inject prone, you should use parameters - because in this case it prevents confusion, given that the database server and the app may be running on machines with very different culture settings, so the default string generated by your code may not match the date format that the database server machine is set for: if yours uses MM-dd-yy and the server uses dd-MM-yy then you will retrieve the wrong info.

If you aren't getting any data at all after you have fixed this throughout your whole app, then double check the data in your database and check that the Date column does not contain any time info (or that it's set to midnight) - an "equals comparison" needs the values to be identical down to the tick, not just to the day.

这篇关于如何根据我的datetimepicker值过滤数据库的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！