My records are not updating (only 1st row of DataGridView)

Question

private void applyUpdates(OleDbCommand myCommand, OleDbConnection Conn)
     foreach (DataGridViewRow row in dataGridView1.Rows)
     {
          String SQL = "Update UserList SET ActiveToday=@ActiveToday WHERE POID=@POID";
          myCommand = new OleDbCommand(SQL, Conn);
          myCommand.Parameters.AddWithValue("@POID", row.Cells["POID"].Value.ToString());
          myCommand.Parameters.AddWithValue("@ActiveToday", 1);

          Conn.Open();
          myCommand.CommandType = CommandType.Text;
          myCommand.ExecuteNonQuery();
          Conn.Close();
    }
 }

The following is passed into the method above

string connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Path\\database_be.accdb;Persist Security Info=False;";
OleDbConnection Conn = new OleDbConnection();
Conn.ConnectionString = connectionstring;
OleDbCommand myCommand = Conn.CreateCommand();

There is no error or crashing. When I open the Access 2013 database, I see that only the 1st record updated. The others are untouched.

I am not sure what I am doing wrong.

Answer 1

There is a known issue with parameters and Access. It does not look at the name of the parameters but instead at the order they are declared and used in the query. If you switch the order of your two AddWithValue calls it should fix it.

Here is also a updated version of your code using "best practices" using things like using statements to dispose of your commands and connection and renamed the variables and methods to follow the naming guidelines.

private void SomeOtherMethod()
{
    string connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Path\\database_be.accdb;Persist Security Info=False;";
    using(var conn = new OleDbConnection(connectionstring))
    {
        conn.Open();
        ApplyUpdates(conn);
    } //The using statement closes the connection for you. 
}


private void ApplyUpdates(OleDbConnection conn)
{
     var sql = "Update UserList SET ActiveToday=@ActiveToday WHERE POID=@POID";
     foreach (DataGridViewRow row in dataGridView1.Rows)
     {
          using(var myCommand = new OleDbCommand(sql, conn);
          {
              myCommand.CommandType = CommandType.Text; //I think it is text by default and this is unnessary
              myCommand.Parameters.AddWithValue("@ActiveToday", 1);
              myCommand.Parameters.AddWithValue("@POID", row.Cells["POID"].Value.ToString());
              myCommand.ExecuteNonQuery();
          }
    }
}