CODEWITHSUNDEEP
c#ms-accessoledb
GettingStarted
GettingStarted

Reputation: 7625

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

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.

Upvotes: 1

Views: 111

Answers (1)

Scott Chamberlain
Scott Chamberlain

Reputation: 127603

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

Upvotes: 2

Related Questions