SQL语句与datetimepicker [英] SQL statement with datetimepicker

问题描述

这应该是一个简单的。在Windows窗体中使用日期时间选择器时，我想要执行一个SQL语句，如下所示：

  string sql =SELECT * FROM Jobs WHERE JobDate ='+ dtpJobDate.Text +'; 
  

不幸的是，由于JobDate字段作为DateTime值存储，所以并没有提供任何结果。我想能够搜索这个日期的所有记录，无论存储的时间是多少，任何帮助？

新查询：

  SqlDataAdapter da2 = new SqlDataAdapter（）; 
 SqlCommand cmd = new SqlCommand（）; 
 cmd.CommandText =SELECT * FROM Jobs WHERE JobDate> = @p_StartDate AND JobDate< @p_EndDate; 
 cmd.Parameters.Add（@p_StartDate，SqlDbType.DateTime）.Value = dtpJobDate.Value.Date; 
 cmd.Parameters.Add（@p_EndDate，SqlDbType.DateTime）.Value = dtpJobDate.Value.Date.AddDays（1）; 
 cmd.Connection = conn; 
 da2.SelectCommand = cmd; 
 da2.Fill（dt）; 
 dgvJobDiary.DataSource = dt; 
  

非常感谢所有的帮助！

解决方案

只需一个答案：使用参数化查询。

这是因为不同的原因：

• 安全性（没有 SQL
  注入的风险


• 不再是您打开主题的问题


• 演出。

因此，写下你的语句：

  SqlCommand cmd = new SqlCommand（）; 
 cmd。 CommandText =SELECT * FROM Jobs WHERE JobDate = @p_Date
 cmd.Parameters.Add（@p_Date，SqlDbType.DateTime）.Value = dtpJobDate.Value; 
  

如果你想忽略时间，那么我觉得最好的办法是做范围搜索，如果时间存储在数据库中，那就是。
这样的东西（只是SQL查询）：

  SELECT * FROM Jobs WHERE JobDate> = @p_StartDate AND JobDate< @p_EndDate 
  

StartDate将是 dtpJobDate.Value.Date 和EndDate将是 dtpJobDate.Value.Date.AddDays（1）

如果时间是不存储在数据库中，那么你可以这样做：

  SELECT * FROM Jobs WHERE JobDate = @p_Date 
  

搜索参数应为 dtpJobDate.Value.Date

This should hopefully be a simple one. When using a date time picker in a windows form, I want an SQL statement to be carried out, like so:

string sql = "SELECT * FROM Jobs WHERE JobDate = '" + dtpJobDate.Text + "'";

Unfortunately, this doesn't actually provide any results because the JobDate field is stored as a DateTime value. I'd like to be able to search for all records that are on this date, no matter what the time stored may be, any help?

New query:

        SqlDataAdapter da2 = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "SELECT * FROM Jobs WHERE JobDate >= @p_StartDate AND JobDate < @p_EndDate";
        cmd.Parameters.Add ("@p_StartDate", SqlDbType.DateTime).Value = dtpJobDate.Value.Date;
        cmd.Parameters.Add ("@p_EndDate", SqlDbType.DateTime).Value = dtpJobDate.Value.Date.AddDays(1);
        cmd.Connection = conn;
        da2.SelectCommand = cmd;
        da2.Fill(dt);
        dgvJobDiary.DataSource = dt;

Huge thanks for all the help!

解决方案

Just one answer: use parametrized queries.

This is for different reasons:

• security (no risk of SQL Injection
• no longer those problems for which you're opening a topic
• performance.

Thus, write your statement like this:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Jobs WHERE JobDate = @p_Date"
cmd.Parameters.Add ("@p_Date", SqlDbType.DateTime).Value = dtpJobDate.Value;

If you want to ignore the time, then I think the best bet is to do a range search, if the time is stored in the DB, that is. Something like this (just the SQL query):

SELECT * FROM Jobs WHERE JobDate >= @p_StartDate AND JobDate < @p_EndDate

StartDate would then be dtpJobDate.Value.Date, and EndDate would be dtpJobDate.Value.Date.AddDays(1)

If the Time is not stored in the DB, then you can do this:

SELECT * FROM Jobs WHERE JobDate = @p_Date

where the search argument should be dtpJobDate.Value.Date

这篇关于SQL语句与datetimepicker的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！