如何根据两个 DateTimePicker（仅限日期，而不是时间）之间的日期范围编写 FOR 循环来删除/插入记录？

Question

不是 IT 专业人士，语法是错误的，它只是表明我想做什么。现在如何正确编写代码？

Access 数据库表 TableAppointment 有三个字段：Username、AppointmentDate、AppointmentNote

dateTimePicker1.Value <= dateTimePicker2.Value，已检查。

现在，我想删除记录 WHERE Username = Environment.UserName AND AppointmentDate AND AppointmentDate >= dateTimePicker1.Value.Date AND AppointmentDate <= dateTimePicker2.Value.Date

然后，我根据相同的日期范围（ApointmentDate >= dateTimePicker1.Value.Date 和 AppointmentDate <= dateTimePicker2.Value.Date）和文本框输入插入记录。也许我只需要将 Date 插入数据库，而不需要在数据库中插入时间。

谢谢。

编辑：我已将访问日期格式设置为yyyy-mm-dd

for (DateTime dt = dateTimePicker1.Value; dt <= dateTimePicker2.Value; dt=dt.AddDays(1))
                {


 using (OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString)) //Access database
 {
     //Access database table TableAppointment has three fields: Username, AppointmentDate, AppointmentNote
 //dateTimePicker1.Value <= dateTimePicker2.Value, which has been checked.

     string sqlDelete = "DELETE FROM TableAppointment WHERE Username = '" + Environment.UserName + "' AND AppointmentDate >= dateTimePicker1.Value.Date AND AppointmentDate <= dateTimePicker2.Value.Date";

     OdbcCommand cmdDelete = new OdbcCommand(sqlDelete, Cn);

     Cn.Open();

     cmdDelete.ExecuteNonQuery();
 }

 using (OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString)) //Access database
 {
     //Access database table TableAppointment has three fields: Username, AppointmentDate, AppointmentNote


     string sqpInsert = "INSERT INTO TableAppointment (Username, AppointmentDate, AppointmentNote) Values ('" + Environment.UserName + "', '" +  dt.Value + "', '" textBox.Note);";

     OdbcCommand cmdInsert = new OdbcCommand(sqlInsert, Cn);

     Cn.Open();

     cmdDelete.ExecuteNonQuery();

 //AppointmentNote = textBoxNote.Text
 //Username = Environment.UserName
 //AppointmentDate = any days between dateTimePicker1.Value and ateTimePicker2.Value       

 }
                    }

Note：此问题总结整理于: How to write a FOR loop to Delete/Insert records based on Date Range between two DateTimePicker(Date only, not time)?

Answer 1

根据您的代码，您无需将 delete 语句放在 for 循环中。 您可以直接使用delete sql删除满足条件的记录。 然后，可以使用 for 循环插入从 dateTimePicker1 到 dateTimePicker2 的记录。 请注意，您需要使用 betwee 运算符在“sqlDelete”中选择给定范围内的值。 下面是一个测试代码示例，您可以参考。

private void button1_Click(object sender, EventArgs e)  
{  
        using (OdbcConnection Cn = new OdbcConnection(connectionString)) //Access database  
        {  
        string sqlDelete = "DELETE FROM Table1 WHERE Name = '" + textBox1.Text + "' And Field2 BETWEEN '" + dateTimePicker1.Value.Date + "' AND '" + dateTimePicker2.Value.Date + "'";  
        OdbcCommand cmdDelete = new OdbcCommand(sqlDelete, Cn);  
         Cn.Open();  
          int i = cmdDelete.ExecuteNonQuery();  
          //You can also check if the delete statement is executed successfully with the "i"  
          //if (i>0)   
          //{  
                //    MessageBox.Show("delete successfully");                   
           //}  
        for (DateTime dt = dateTimePicker1.Value.Date; dt <= dateTimePicker2.Value.Date; dt = dt.AddDays(1))  
        {  
                     
            string sqpInsert = "INSERT INTO  Table1(Name, Field2, Field1) Values ('" + textBox2.Text + "', '" + dt.Date + "', '" + textBox3.Text + "')";  
            OdbcCommand cmdInsert = new OdbcCommand(sqpInsert, Cn);  
             cmdInsert.ExecuteNonQuery();  
        }  
    }  
}  

---

如果回复有帮助，请点击“接受答案”并点赞。

注意：如果您想接收此线程的相关电子邮件通知，请按照我们文档中的步骤启用电子邮件通知。