选择日期进入SQL查询

Selected dates into sql query
2021-05-11
  •  译文(汉语)
  •  原文(英语)

我试图在日期选择器中选择两个日期,并将选定的日期转移到sql查询中.

我有一个与此类似的SQL查询:

SELECT * FROM Product_sales 
WHERE From_date >= '03-Jan-2013'
AND To_date <= '09-Jan-2013'

我希望将日期更改为我在datepicker中选择的日期,即startDate和endDate.

我在日期选择器中选择日期,然后转到需要我的结果的另一页.

如何将所选日期放入查询中?

速聊1:
这是什么数据库?SQL只是查询语言-许多数据库系统都使用....
解决过程1

当然,您将要使用参数化查询:

DateTime fromDate = dateTimePickerFrom.Value;
DateTime toDate = dateTimePickerTo.Value;

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();

    using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM Product_sales WHERE From_date >= ? AND To_date <= ?", conn))
    {
        cmd.Parameters.AddWithValue("@from", fromDate);
        cmd.Parameters.AddWithValue("@to", toDate);

        using (OleDbDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
                ...
        }
    }
}
速聊1:
从SQL Server更改SqlConnection/-CommandOldDbConnection/-Command.请注意,您不能在此处命名参数,但是需要使用?,但是可以在添加参数时命名它们.
解决过程2

您需要为使用日期格式的MySQL格式化日期YYYY-MM-DD.

string mySqlDateTime = dateValue.ToString("yyyy-MM-dd HH:mm:ss");
速聊1:
您为什么认为它是MySQL?
速聊2:
在已删除的问题(或评论)中提到了MySQL.
解决过程3

您需要将查询更改为此:

SELECT * FROM Product_sales 
WHERE From_date >= @Date
AND To_date <= @Date

然后在获取数据时,您将执行以下操作:

using (MySqlConnection c = new MySqlConnection(cString))
using (MySqlCommand cmd = new MySqlCommand(sql, c))
{
    cmd.Parameters.AddWithValue("@Date", datePicker.Value);
    cmd.ExecuteNonQuery();
}

sql我在上面显示的查询在哪里.此外,我不知道这是否ValueDatePicker您想要的财产,但我认为是.如果不是,则只需替换正确的属性.

速聊1:
您为什么认为它是MySql?
速聊2:
,我原来看到的问题中有一条评论或文本,但我不再看到了.我现在不确定.无论哪种方式,它都不会改变实现.
速聊3:
我只是感到困惑,因为您和Andy都一直在谈论MySQL,而对我而言,没有迹象表明正在使用或要求使用MySQL.:-)

I'm trying to select two dates in a datepicker and transfer the selected dates into a sql query.

I have a sql query similar to this:

SELECT * FROM Product_sales 
WHERE From_date >= '03-Jan-2013'
AND To_date <= '09-Jan-2013'

and I want the dates to be changed to what I select in the datepicker which is the startDate and endDate.

I select the dates in the datepicker and go to another page where I want my result.

How do I put the selected dates into my query?

Talk1:
WHAT database is this for? SQL is just the query language - used by many database systems ....
Solutions1

Of course you will want to use parameterized queries:

DateTime fromDate = dateTimePickerFrom.Value;
DateTime toDate = dateTimePickerTo.Value;

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();

    using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM Product_sales WHERE From_date >= ? AND To_date <= ?", conn))
    {
        cmd.Parameters.AddWithValue("@from", fromDate);
        cmd.Parameters.AddWithValue("@to", toDate);

        using (OleDbDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
                ...
        }
    }
}
Talk1:
Changed from the SQL Server specific SqlConnection/-Command to OldDbConnection/-Command. Please note that you can't name parameters here, but need to use ?, but you can name them when adding them.
Solutions2

You need to format your dates for MySQL, which uses the YYYY-MM-DD format.

string mySqlDateTime = dateValue.ToString("yyyy-MM-dd HH:mm:ss");
Talk1:
Why do you assume it's MySQL?
Talk2:
there was a mention of MySQL in the question (or perhaps a comment) that has been removed.
Solutions3

You need to change your query to this:

SELECT * FROM Product_sales 
WHERE From_date >= @Date
AND To_date <= @Date

and then when you get the data, you would do something like this:

using (MySqlConnection c = new MySqlConnection(cString))
using (MySqlCommand cmd = new MySqlCommand(sql, c))
{
    cmd.Parameters.AddWithValue("@Date", datePicker.Value);
    cmd.ExecuteNonQuery();
}

where sql is the query I showed above. Further, I don't know if it's the Value property of the DatePicker you want, but I think it is. If it's not, just substitute the right property.

Talk1:
Why do you assume it's MySql?
Talk2:
, there was a comment or text in the question that I saw originally that I no longer see. I'm not sure now. Either way, it doesn't change the implementation.
Talk3:
I was just puzzled, because both you and Andy kept talking about MySQL and to me there was no indication of MySQL being used or asked for. :-)
转载于:https://stackoverflow.com/questions/20217642/selected-dates-into-sql-query

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入