你能帮帮我吗,我有一个按钮来过滤基于我的 2 个日期时间选择器(日期开始和日期到)的日期范围的数据, 下面是我的代码,当我单击按钮时,它什么也不显示。 你能帮我什么问题吗? 我是 C# 中的新功能。
MySqlConnection mcon = new MySqlConnection("datasource=localhost;port=3306;username=8888;password=888888");
MySqlDataAdapter mda = new MySqlDataAdapter("select * from bio_db.daily_data2 where Date between '" + datefrom.Value.ToString() + "' and '" + dateto.Value.ToString() + "' ", mcon);
mcon.Open();
DataSet ds = new DataSet();
mda.Fill(ds);
dbgrid1.DataSource = ds;
dbgrid1.Refresh();
mcon.Close();
dateFrom
值和dateTo
值转换为DateTime
DateTime dtFrom =Convert.ToDateTime(DatePicker1.Text); //some DateTime value, e.g. DatePicker1.Text;
DateTime dtTo =Convert.ToDateTime(DatePicker2.Text); //some DateTime value, e.g. DatePicker1.Text;
MySqlConnection mcon = new MySqlConnection("datasource=localhost;port=3306;username=8888;password=888888");
MySqlDataAdapter mda = new MySqlDataAdapter("select * from bio_db.daily_data2 where Date between '" + dtFrom.ToString("MM/dd/yyyy")+ "' and '" + dtTo.ToString("MM/dd/yyyy") + "' ", mcon);
DataSet ds = new DataSet();
mda.Fill(ds);
dbgrid1.DataSource = ds;
dbgrid1.Refresh();
mcon.Close();
嗨,伙计们,我已经找到了缺失的链接。这是更正的脚本。谢谢大家!
DateTime dtFrom = Convert.ToDateTime(datefrom.Text); //some DateTime value, e.g. DatePicker1.Text;
DateTime dtTo = Convert.ToDateTime(dateto.Text); //some DateTime value, e.g.
DatePicker1.Text;<n>MySqlConnection mcon = new MySqlConnection("datasource=localhost;port=3306;username=8888;password=8888888");
MySqlDataAdapter mda = new MySqlDataAdapter("select * from bio_db.daily_data2 where Date between '" + dtFrom.ToString("yyyy/MM/dd") + "' and '" + dtTo.ToString("yyyy/MM/dd") + "' ", mcon);
System.Data.DataSet ds = new System.Data.DataSet();
mcon.Open();
mda.Fill(ds, "root");
dbgrid1.DataSource = ds.Tables["root"];
dbgrid1.Refresh();
mcon.Close();
我认为您需要将日期开始和日期到值转换为数据库日期格式和类型
您的数据库具有date type
的date
值,并且您正在将string
与不匹配的date
进行比较。
您需要convert
start
并将日期end
为date
select * from bio_db.daily_data2 where Date between STR_TO_DATE('" + datefrom.Value.ToString() + "','%m/%d/%Y') and STR_TO_DATE('" + dateto.Value.ToString() + "','%m/%d/%Y') "
您需要指定存储在数据库中的确切格式来代替'%m/%d/%Y'
。
使用参数化 SQL
您当前的代码对Sql injection
最好使用 parameterized sql
.不要连接值,而是连接add parameters
查询。
缺少此行:
MySqlCommandBuilder mcb = new MySqlCommandBuilder(mda);
来源:
https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-data-adapter.html http://www.techonthenet.com/mysql/between.php
所以代码应该是:
MySqlConnection mcon = new MySqlConnection("datasource=localhost;port=3306;username=8888;password=888888");
string query = string.Format("select * from bio_db.daily_data2 where Date "
+ "BETWEEN CAST('{0}' AS DATE) AND CAST('{1}' AS DATE) ",
dateFrom.Value.ToString("M/d/yyyy"),
dateTo.Value.ToString("M/d/yyyy"));
MySqlDataAdapter mda = new MySqlDataAdapter(query, mcon);
MySqlCommandBuilder mcb = new MySqlCommandBuilder(mda); //added code
mcon.Open();
DataTable dt = new DataTable();
mda.Fill(dt);
dbgrid1.DataSource = dt;
dbgrid1.Refresh();
mcon.Close();