question

VAer-4038 avatar image
0 Votes"
VAer-4038 asked ·

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-csharpwindows-forms
10 |1000 characters needed characters left characters exceeded

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

DanielZhang-MSFT avatar image
0 Votes"
DanielZhang-MSFT answered ·

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

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

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 Votes 0 ·

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 Votes 0 ·
timrprobocom avatar image
0 Votes"
timrprobocom answered ·

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

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

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 Votes 0 ·