时间戳

首页 > 解决方案 > SQL 请求 - 显示两个日期之间条目的日期

c# - SQL 请求 - 显示两个日期之间条目的日期

问题描述

我想取回开始和结束之间的所有日期以及开始和结束本身。出于某种原因，结果仅显示开始和结束和结束之间的日期，但不显示开始。

我试图切换更大的然后更小并且是相等的符号，因为我无法解释错误应该在哪里

这是事件代码：


private void monthCalendar1_DateSelected(object sender, DateRangeEventArgs e)
        {
            string language = System.Globalization.CultureInfo.CurrentCulture.Name;

            if (!(language == "de-DE"))
            {
                if (language == "es-ES")
                {
                }
                else if (language == "en-GB")
                {
                    DateTime start = monthCalendar1.SelectionStart;
                    DateTime end = monthCalendar1.SelectionEnd;

                    MySqlConnection conn = DBUtils.GetDBConnection();
                    conn.Open();

                    MySqlDataAdapter adapterid;
                    DataTable dtschichten = new DataTable();

                    MySqlCommand feedid = conn.CreateCommand();
                    feedid.CommandText = "SELECT id, datum, atotal, ptotal, total FROM arbeitszeiten WHERE fullname = '" + comboBox1.Text + "' and active = 'NEIN' and datum >= '" + start + "' and datum <= '" + end + "'";
                    adapterid = new MySqlDataAdapter(feedid);
                    adapterid.Fill(dtschichten);

                    dtschichten.Columns["id"].ColumnName = "ID";
                    dtschichten.Columns["datum"].ColumnName = "Shiftdate";
                    dtschichten.Columns["atotal"].ColumnName = "Worktime";
                    dtschichten.Columns["ptotal"].ColumnName = "Breaktime";
                    dtschichten.Columns["total"].ColumnName = "Total time";
                    BindingSource bSourceschichten = new BindingSource();

                    bSourceschichten.DataSource = dtschichten;
                    dataGridView.DataSource = bSourceschichten;
                    adapterid.Update(dtschichten);

                    DataGridViewColumn column = dataGridView.Columns[0];
                    column.Width = 30;

                    conn.Close();
                }
                else
                {
                    MessageBox.Show("Your language setting is currently not supported" + "\n" + "Please change your primary language setting for Windows" + "\n" + "\n" + "The Programm will shut down now !", "Critial Failure", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                    this.Close();
                }
            }
            else
            {
                DateTime start = monthCalendar1.SelectionStart;
                DateTime end = monthCalendar1.SelectionEnd;

                MySqlConnection conn = DBUtils.GetDBConnection();
                conn.Open();

                MySqlDataAdapter adapterid;
                DataTable dtschichten = new DataTable();

                MySqlCommand feedid = conn.CreateCommand();
                feedid.CommandText = "SELECT id, datum, atotal, ptotal, total FROM arbeitszeiten WHERE fullname = '" + comboBox1.Text + "' and active = 'NEIN' and datum >= '" + start + "' and datum <= '" + end + "'";
                adapterid = new MySqlDataAdapter(feedid);
                adapterid.Fill(dtschichten);

                dtschichten.Columns["id"].ColumnName = "ID";
                dtschichten.Columns["datum"].ColumnName = "Schichtdatum";
                dtschichten.Columns["atotal"].ColumnName = "Arbeitszeit";
                dtschichten.Columns["ptotal"].ColumnName = "Pausenzeit";
                dtschichten.Columns["total"].ColumnName = "Zeit gesammt";
                BindingSource bSourceschichten = new BindingSource();

                bSourceschichten.DataSource = dtschichten;
                dataGridView.DataSource = bSourceschichten;
                adapterid.Update(dtschichten);

                DataGridViewColumn column = dataGridView.Columns[0];
                column.Width = 30;

                conn.Close();
            }
        }

标签： c#sqldatabasedate

解决方案

使用 SQL 参数来避免 SQL 注入，并使用 BETWEEN 来保持整洁。

MySqlCommand feedid = conn.CreateCommand();
feedid.CommandText = "SELECT id, datum, atotal, ptotal, total FROM arbeitszeiten WHERE fullname = @fullname and active = 'NEIN' and datum BETWEEN @start AND @end";
feedid.Parameters.AddWithValue("@fullname", comboBox1.Text);
feedid.Parameters.AddWithValue("@start", start.AddDays(1));
feedid.Parameters.AddWithValue("@end", end);

推荐阅读