在C＃中的两个日期之间过滤数据网格 [英] Filter datagrid between two dates in C#

问题描述

嗨伙计们！我有两个日期选择器。我想在日期选择器的选定日期之间填充数据网格。我的代码无效。



我尝试过的事情：

 OleDbCommand cmd =  new  OleDbCommand（  SELECT * FROM invoice其中DateTime介于' + dateTimePicker1.Value.ToString（）+   'AND' + dateTimePicker2.Value.ToString（）+  '，con ）; 
 con.Open（）; 
 OleDbDataReader sdr = cmd.ExecuteReader（）; 
  if （（sdr.Read（）==  true ））
 { 
 DataSet ds =  new  DataSet（）; 
 da.Fill（ds，  invoice）; 
 dataGridView1.DataSource = ds.Tables [  invoice]; 
  //   dataGridView1.DataSource = sdr;  
 
} 
  else  
 {
 MessageBox.Show（ 查询未执行）; 
} 

解决方案

试试这个：

 OleDbCommand cmd =  new  OleDbCommand（  SELECT * FROM invoice其中DateTime ' + dateTimePicker1.Value.ToString（）+  'AND' + dateTimePicker2 .Value.ToString（）+  '，con）; 
 con.Open（）; 
 DataSet ds =  new  DataSet（）; 
 da.Fill（ds，  invoice）; 
 dataGridView1.DataSource = ds.Tables [  invoice]; 

这适用于

  string  connectionstring = Settings.Default.SandboxConnectionString; 
 使用（OleDbConnection con =  new  OleDbConnection（connectionstring））
 {
 OleDbCommand cmd =  new  OleDbCommand（  SELECT * FROM发票，其中DateTime介于@ date1和@ date2之间，con）; 
 cmd.Parameters.AddWithValue（  @ date1，dateTimePicker1.Value）; 
 cmd.Parameters.AddWithValue（  @ date2，dateTimePicker2.Value）; 
 OleDbDataAdapter da =  new  OleDbDataAdapter（cmd）; 
 
 con.Open（）; 
 DataSet ds =  new  DataSet（）; 
 da.Fill（ds，  invoice）; 
 dataGridView1.DataSource = ds.Tables [  invoice]; 
} 

但是，只有当桌面上的实际数据与我选择的日期相符时才会发生。

注意事项：

1.我使用了参数化查询。

2.在dateTimePicker值上没有.ToString（）

3.你应该真的枚举你想从表中返回的列，即使用

  SELECT  col1，col2，...，colN  FROM  invoice 

而不是

  SELECT  * 来自发​​票

4.您应该避免对表格上的列名使用保留字 - 使用 InvoiceDate 而不是 DateTime 例如

 OleDbCommand cmd =  new  OleDbCommand（ @  SELECT * FROM invoice wher e日期时间' + 
 dateTimePicker1.Value.ToShortDateString（）+  'AND'  + dateTimePicker2.Value.ToShortDateString（）+ 
  '， CON）; 
 
 DataSet ds =  new  DataSet（）; 
 
 da.SelectCommand = cmd; 
 con.Open（）; 
 da.Fill（ds，  invoice）; 
 dataGridView1.DataSource = ds.Tables [  invoice]; 
 con.Close（）; 
} 

hi guys! i have two date pickers. and i want to fill data grid between selected dates from date pickers. my code is not working.

What I have tried:

OleDbCommand cmd = new OleDbCommand("SELECT * FROM invoice where DateTime between '" + dateTimePicker1.Value.ToString() + "' AND '" + dateTimePicker2.Value.ToString() + "'",con);
            con.Open();
            OleDbDataReader sdr = cmd.ExecuteReader();
            if ((sdr.Read() == true))
            {
                DataSet ds = new DataSet();
                da.Fill(ds, "invoice");
                dataGridView1.DataSource = ds.Tables["invoice"];
                //dataGridView1.DataSource = sdr;

            }
            else
            {
                MessageBox.Show("query not executed");
            }

解决方案

Try this :

OleDbCommand cmd = new OleDbCommand("SELECT * FROM invoice where DateTime between '" + dateTimePicker1.Value.ToString() + "' AND '" + dateTimePicker2.Value.ToString() + "'",con);
con.Open();
DataSet ds = new DataSet();
da.Fill(ds, "invoice");
dataGridView1.DataSource = ds.Tables["invoice"];

This works

string connectionstring = Settings.Default.SandboxConnectionString;
using (OleDbConnection con = new OleDbConnection(connectionstring))
{
    OleDbCommand cmd = new OleDbCommand("SELECT * FROM invoice where DateTime between @date1 AND @date2", con);
    cmd.Parameters.AddWithValue("@date1", dateTimePicker1.Value);
    cmd.Parameters.AddWithValue("@date2", dateTimePicker2.Value);
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    con.Open();
    DataSet ds = new DataSet();
    da.Fill(ds, "invoice");
    dataGridView1.DataSource = ds.Tables["invoice"];
}

But only if there is actually data on the table that matches the dates I selected.
Things to note:
1. I've used a parameterised query.
2. There is no .ToString() on the dateTimePicker values
3. You should really enumerate the columns you want returned from the table i.e. use

SELECT col1, col2, ..., colN FROM invoice

instead of

SELECT * from invoice

4. You should avoid using reserved words for column names on tables - use InvoiceDate rather than DateTime for example

OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM invoice where DateTime between '" +
            dateTimePicker1.Value.ToShortDateString() + "' AND '" + dateTimePicker2.Value.ToShortDateString() +
                "'", con);
           
            DataSet ds = new DataSet();

            da.SelectCommand = cmd;
            con.Open();
            da.Fill(ds, "invoice");
            dataGridView1.DataSource = ds.Tables["invoice"];
            con.Close();
        }

这篇关于在C＃中的两个日期之间过滤数据网格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！