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

Question

question

VAer-4038 asked · Jan 13 at 02:09 AM

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

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       
            
        
        
  }
    
                     }

dotnet-csharp windows-forms

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-01-14T05:33:04.607Z

DanielZhang-MSFT answered · Jan 14 at 05:33 AM

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.

· 2 · Share

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VAer-4038 · Jan 16 at 08:19 PM

I am trying to match Date format (Access date format: yyyy-mm-dd), but it does not work.

Thanks.

string sqlDelete = "DELETE FROM TableABC WHERE Username = '" + Environment.UserName + "' AND ABCDate BETWEEN '" + dateTimePickerBegin.Value.ToString("yyyy-MM-dd") + "' AND '" + dateTimePickerEnd.Value.ToString("yyyy-MM-dd") + "'";

error-when-deleting-records-from-access-database.html

0 ·

DanielZhang-MSFT VAer-4038 · Jan 18 at 07:03 AM

Hi @VAer-4038，
In response to this question, I saw that you have posted a new thread. I provided a solution for you to refer to.
Best Regards,
Daniel Zhang

0 ·

Answer 2 · 2021-01-13T04:42:37.593Z

timrprobocom answered · Jan 13 at 04:42 AM

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

· 1 · Share

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VAer-4038 · Jan 13 at 04:47 AM

Thanks. How can I write the whole FOR loop? Or is your code already completed?

No sure how to put them together (don't have computer science background). For both DELETE and INSERT.

My Access date format is yyyy-mm-dd

0 ·

question