How to write a FOR loop to Delete/Insert records based on Date Range between two DateTimePicker(Date only, not time)?

Question

Not an IT professional, syntax is wrong, it just shows what I would like to do. Now how to write the code correctly?

Access database table TableAppointment has three fields: Username, AppointmentDate, AppointmentNote

dateTimePicker1.Value <= dateTimePicker2.Value, which has been checked.

Now, I would like to delete records WHERE Username = Environment.UserName AND AppointmentDate AND AppointmentDate >= dateTimePicker1.Value.Date AND AppointmentDate <= dateTimePicker2.Value.Date

Then I INSERT records based on same date range (ApointmentDate >= dateTimePicker1.Value.Date AND AppointmentDate <= dateTimePicker2.Value.Date) and textbox input. Maybe I only need to insert Date into database, no need to insert time into database.

Thanks.

Edit: I have set Access date format as 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       



 }

                    }

Answer 1

Hi VAer-4038,
Based on your code, you need not put delete statement in a for loop.
You can just use the delete sql to delete records that meet the conditions.
Then you can use a for loop to insert the records which date from dateTimePicker1 to dateTimePicker2.
Note that you need to use betwee operator to select values within a given range in your "sqlDelete".
Here is a test code example you can refer to.

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();  
        }  
    }  
}  

Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

As a rule, you should always let the API do your quoting for you, to prevent SQL attacks. Approximately like this:

string sqlDelete = "DELETE FROM TableAppointment WHERE Username=@User AND AppointmentDate BETWEEN @First AND @Last;";
OdbcCommand cmdDelete = new OdbcCommand(sqlDelete, Cn);
cmdDelete.Parameters.Add( "@User", SqlDbType.VarChar );
cmdDelete.Parameters["@User"].Value = Environment.UserName;
cmdDelete.Parameters.Add( "@First", SqlDbType.Date );
cmdDelete.Parameters["@First"].Value = dateTimePicker1.Value.Date;
cmdDelete.Parameters.Add( "@Last", SqlDbType.Date );
cmdDelete.Parameters["@Last"].Value = dateTimePicker2.Value.Date;
cmdDelete.ExecuteNonQuery();