如何从日期中选择数据 [英] How to select data from date

问题描述

Hello Guys，我从数据库中选择数据到datagridview，但是当我选择数据，所以datagridview显示空白



我尝试过：

Hello Guys, i am selecting data from database into datagridview but when i select the data so datagridview show blank

What I have tried:

connection.Open();
            DataSet dsa = new DataSet();
            DataTable dt = new DataTable();
            dsa.Tables.Add(dt);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da = new OleDbDataAdapter("SELECT * from [Sales Record] where [Cash Sales] = '" + "Cash Sales" + "'And[Date] =#"+System.DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss:tt")+"#", connection);
            da.Fill(dt);
            dataGridView1.DataSource = dt;

            connection.Close();

推荐答案

有很多问题需要解决。考虑以下备选方案

There are quite a few problems to fix. Consider the following alternative

string sql;
DataSet dsa = new DataSet();
DataTable dt = new DataTable();
dsa.Tables.Add(dt);
using (OleDbConnection connection = new OleDbConnection(connectionString)) {
   sql = @"SELECT * 
FROM  [Sales Record] 
WHERE [Cash Sales] = 'Cash Sales'
AND   [Date]       = @datecriteria";
   OleDbDataAdapter da =new OleDbDataAdapter(sql, connection);

   da.SelectCommand.Parameters.Add("@datecriteria", OleDbType.Date).Value = System.DateTime.Now;
   try {
       connection.Open();
       adapter.Fill(dt);
   }
   catch (Exception ex) {
      // do some proper error handling
   }
}

始终使用参数至关重要阻止自己 SQL注入 [ ^ ]并使用块来确保正确处理资源。



标准是什么你将静态文本连接到where子句并使用当前时间作为标准看起来有点奇怪。只是猜测但是如果你想今天搜索记录，而不是

It's vital to always use parameters to prevent yourself from SQL injection[^] and to use using blocks to ensure proper disposal of resources.

What comes to the criteria itself it looks a bit odd that you're concatenating a static text to the where clause and that you use current time for the criteria. Just guessing but if you want to search for records for today, instead of

da.SelectCommand.Parameters.Add("@datecriteria", OleDbType.Date).Value = System.DateTime.Now;

使用

use

da.SelectCommand.Parameters.Add("@datecriteria", OleDbType.Date).Value = System.DateTime.Now.Date;

不要这样做。永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。使用日期，这也可以消除转换问题，其中DB服务器将欧洲日期格式解释为US并将行与mm / dd / yyyy进行比较。



请记住（正如你所知）你的日期字符串将当前日期返回到第二个，并且不太可能存在许多或实际上任何具有该值的记录。如果这个查询在不同的PC上执行，它很可能会为它们全部返回不同的时间值--PC时钟不是特别同步。如果您需要精确到秒，您可能希望为检查和行INSERT / UPDATE使用单个一致的时间源，这意味着您应始终通过GETDATE函数使用DB服务器日期和时间值。



如果你今天在行之后，使用 DateTime.Now.Date ，将其作为参数化值传递，并进行比较用大于或等于而不是等于

Don't do it like 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. With dates, this can also eliminate translation problems where the DB server interprets the European date format as US and compares rows with mm/dd/yyyy instead.

Bear in mind that (as you have been told) your Date string returns the current date to the second and it's unlikely that there are many, or indeed any, records with that value. And if this query is executed on different PC's, it will quite likely return different time values for them all - PC clocks are not particularly synchronised. If you need accuracy to the second, you probably want to use a single, consistent time source for your checks and your row INSERT / UPDATEs, which means you should always use the DB server Date and Time values via the GETDATE function.

If you are after rows today, use DateTime.Now.Date, pass it as a parameterized value, and compare it with "Greater than or equal" instead of "Equals"

使用此代码..不要像这样使用sql quary。使用mysql参数。如果没有代码不是正品



ok试试这个代码

Use this code.. Don't use sql quary like this. use mysql parameters. if not codes are not genuine

ok try this code

private void LoadData()
       {
           try
           {
               using (OleDbConnection connection = new OleDbConnection(connectionString))
               {
                   using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM  [Sales Record] WHERE [Cash Sales] = 'Cash Sales' AND [Date] = @datecriteria", /* Please Enter Connection Name to this place*/))
                   {
                       connection.Open();
                       oda.SelectCommand.Parameters.Clear();
                       oda.SelectCommand.Parameters.AddWithValue("@datecriteria", System.DateTime.Now);
                       DataTable dt = new DataTable();
                       oda.Fill(dt);
                       if (dt.Rows.Count > 0)
                       {
                           oda.Fill(dt);
                           dataGridView1.DataSource = dt;
                       }
                       else
                       {
                           dataGridView1.DataSource = null;
                       }
                       connection.Close();
                   }

               }
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.ToString());
           }

       }

之后在这样的按钮点击事件中调用此方法

after that call this method in button click event like this

private void button1_Click(object sender, EventArgs e)
       {
           LoadData();
       }

如果有任何问题请评论...

谢谢

if any question please comment ...
thank you

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