如何在C＃中将datetimepicker放入查询中 [英] How to put datetimepicker in query in C#

问题描述

大家好，



我在c＃中创建一个报告，其中有一个组合框包含每日，每周

每月，半年，每年项目如果用户在组合框中选择其中任何一项，那么在datagridview中根据组合框中的选择显示各自的数据，截至目前它对我来说工作正常，但我的项目经理需要前端的dateTime选择器选项，所以如果用户选择组合框项目为每周和startdate（来自datepicker）：2016年3月11日和enddate（fromdatetimepicker）为2016年3月15日，所以他可以使用访问数据库将相关数据导入到datagridview中，并且只有一列使用该列的日期我必须显示我尝试结束但未发生的详细信息我在这里放一个代码告诉我如何使用datetimepicker选项



我有什么试过：

  if （comboBox1.SelectedItem。 ToString（）==  每周）
 {
 
  string  connetionString = 空; 
 connetionString = ConfigurationManager.ConnectionStrings [  AccessConnectionString]。ConnectionString; 
 cnn.ConnectionString = connetionString; 
  string  sql =   SELECT * FROM Billing Date（）和Date（）之间的日期 -  6; 
  //   StringBuilder sql = new StringBuilder（SELECT * FROM Billing）;  
  //   sql.Append（日期之间的日期（）'+ dateTimePicker1.Value.ToString（dd / mm / yyyy）+'和）;  
  //   sql.Append （'Date（） -  6+ dateTimePicker2.Value.ToString（dd / mm / yyyy）+'）;  
 
 OleDbConnection connection =  new  OleDbConnection（connetionString）; 
 OleDbDataAdapter dataadapter =  new  OleDbDataAdapter（sql，connection）; 
 DataSet ds =  new  DataSet（）; 
 connection.Open（）; 
 dataadapter.Fill（ds，  Billing）; 
 connection.Close（）; 
 dataGridView1.DataSource = ds; 
 dataGridView1.DataMember =   Billing; 
 
} 
  else   if （comboBox1.SelectedItem.ToString（ ）==  每月）
 {
  //   string connetionString = @provider = Microsoft.ACE.OLEDB.12.0; Data Source = E：\Database\BillAndReceipt.accdb; Persist安全信息=假;;  
 字符串 connetionString =  null ; 
 connetionString = ConfigurationManager.ConnectionStrings [  AccessConnectionString]。ConnectionString; 
 cnn.ConnectionString = connetionString; 
  string  sql =   SELECT * FROM Billing其中Year（[Date]）= Year（Now（））和Month（[Date]）= Month（Now（））; 
 OleDbConnection connection =  new  OleDbConnection（connetionString）; 
 OleDbDataAdapter dataadapter =  new  OleDbDataAdapter（sql，connection）; 
 DataSet ds =  new  DataSet（）; 
 connection.Open（）; 
 dataadapter.Fill（ds，  Billing）; 
 connection.Close（）; 
 dataGridView1.DataSource = ds; 
 dataGridView1.DataMember =   Billing; 
} 
 
  else   if （comboBox1.SelectedItem.ToString（ ）==   HalfYelyly）
 {
  //   string connetionString = @provider = Microsoft.ACE.OLEDB.12.0; Data Source = E：\Database\BillAndReceipt.accdb; Persist安全信息=假;;  
 字符串 connetionString =  null ; 
 connetionString = ConfigurationManager.ConnectionStrings [  AccessConnectionString]。ConnectionString; 
 cnn.ConnectionString = connetionString; 
  string  sql =   SELECT * FROM Billing其中Year（[Date]）= Year（Now（））和DatePart（'h'，[Date]）= DatePart（'h'，Now（））; 
 OleDbConnection connection =  new  OleDbConnection（connetionString）; 
 OleDbDataAdapter dataadapter =  new  OleDbDataAdapter（sql，connection）; 
 DataSet ds =  new  DataSet（）; 
 connection.Open（）; 
 dataadapter.Fill（ds，  Billing）; 
 connection.Close（）; 
 dataGridView1.DataSource = ds; 
 dataGridView1.DataMember =   Billing; 
} 
  else   if （comboBox1.SelectedItem.ToString（）== < span class =code-string> 每年）
 {
  //   string connetionString = @provider = Microsoft.ACE.OLEDB.12.0; Data Source = E：\Database\BillAndReceipt.accdb; Persist Security Info = False ;;  
  string  connetionString =  null ; 
 connetionString = ConfigurationManager.ConnectionStrings [  AccessConnectionString]。ConnectionString; 
 cnn.ConnectionString = connetionString; 
  string  sql =   SELECT * FROM Billing年份（[日期]）=年（日期（））; 
 OleDbConnection connection =  new  OleDbConnection（connetionString）; 
 OleDbDataAdapter dataadapter =  new  OleDbDataAdapter（sql，connection）; 
 DataSet ds =  new  DataSet（）; 
 connection.Open（）; 
 dataadapter.Fill（ds，  Billing）; 
 connection.Close（）; 
 dataGridView1.DataSource = ds; 
 dataGridView1.DataMember =   Billing; 
} 

解决方案

如果您按照评论中的建议使用参数化查询，这将变得更加容易。执行以下操作（注意未经测试）

  string  sql =   SELECT * FROM Billing where Date（）和@dateTo之间的日期; 
 OleDbDataAdapter dataadapter =  new  OleDbDataAdapter（sql，connection）; 
 dataadapter.SelectCommand.Parameters.AddWithValue（  @ dateTo，dateTimePicker2.Value）; 

Hi all,

Iam creating one report in c# where having one combobox which is contain Daily,Weekly
Monthly,HalfYearly,Yearly items if user select in combobox any of them item then in datagridview show respective data as per selection in combobox, as of now its working fine for me but my project manager want dateTime picker option in front end , so if user selected combobox item as weekly and startdate(from datepicker):11/03/2016 and enddate(fromdatetimepicker) as 15/03/2016 so he can get that releted data into datagridview im using access database for this and having only one column Date from using that column only i have to show details i was tried my end but not happening iam putting a code here just tell me how i use datetimepicker option in that

What I have tried:

if (comboBox1.SelectedItem.ToString() == "Weekly")
            {
        
                string connetionString = null;
                connetionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
                cnn.ConnectionString = connetionString;
                string sql = "SELECT * FROM Billing where Date Between Date() and Date()-6";
                //StringBuilder sql = new StringBuilder("SELECT * FROM Billing");
                //sql.Append("where Date between Date()'" + dateTimePicker1.Value.ToString("dd/mm/yyyy") + "'and");
                //sql.Append("' Date()-6 " + dateTimePicker2.Value.ToString("dd/mm/yyyy") + "' ");

                OleDbConnection connection = new OleDbConnection(connetionString);
                OleDbDataAdapter dataadapter = new OleDbDataAdapter(sql, connection);
                DataSet ds = new DataSet();
                connection.Open();
                dataadapter.Fill(ds, "Billing");
                connection.Close();
                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = "Billing";

            }
            else if (comboBox1.SelectedItem.ToString() == "Monthly")
            {
               // string connetionString = @" provider=Microsoft.ACE.OLEDB.12.0; Data Source=E:\Database\BillAndReceipt.accdb;Persist Security Info=False;";
                string connetionString = null;
                connetionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
                cnn.ConnectionString = connetionString;
                string sql = "SELECT * FROM Billing where Year([Date]) = Year(Now()) And Month([Date]) = Month(Now())";
                OleDbConnection connection = new OleDbConnection(connetionString);
                OleDbDataAdapter dataadapter = new OleDbDataAdapter(sql, connection);
                DataSet ds = new DataSet();
                connection.Open();
                dataadapter.Fill(ds, "Billing");
                connection.Close();
                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = "Billing";
            }

            else if (comboBox1.SelectedItem.ToString() == "HalfYearly")
            {
                //string connetionString = @" provider=Microsoft.ACE.OLEDB.12.0; Data Source=E:\Database\BillAndReceipt.accdb;Persist Security Info=False;";
                string connetionString = null;
                connetionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
                cnn.ConnectionString = connetionString;
                string sql = "SELECT * FROM Billing where Year([Date]) = Year(Now()) And DatePart('h', [Date]) = DatePart('h', Now())";
                OleDbConnection connection = new OleDbConnection(connetionString);
                OleDbDataAdapter dataadapter = new OleDbDataAdapter(sql, connection);
                DataSet ds = new DataSet();
                connection.Open();
                dataadapter.Fill(ds, "Billing");
                connection.Close();
                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = "Billing";
            }
            else if (comboBox1.SelectedItem.ToString() == "Yearly")
            {
               // string connetionString = @" provider=Microsoft.ACE.OLEDB.12.0; Data Source=E:\Database\BillAndReceipt.accdb;Persist Security Info=False;";
                string connetionString = null;
                connetionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
                cnn.ConnectionString = connetionString;
                string sql = "SELECT * FROM Billing where Year([Date]) = Year(Date())";
                OleDbConnection connection = new OleDbConnection(connetionString);
                OleDbDataAdapter dataadapter = new OleDbDataAdapter(sql, connection);
                DataSet ds = new DataSet();
                connection.Open();
                dataadapter.Fill(ds, "Billing");
                connection.Close();
                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = "Billing";
            }

解决方案

If you use parameterized queries as suggested in the comments, this becomes much easier. Do something like the following (Note-untested)

string sql = "SELECT * FROM Billing where Date Between Date() and @dateTo";
OleDbDataAdapter dataadapter = new OleDbDataAdapter(sql, connection);
dataadapter.SelectCommand.Parameters.AddWithValue("@dateTo",dateTimePicker2.Value);

这篇关于如何在C＃中将datetimepicker放入查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！