CODEWITHSUNDEEP
ado.net
Noha Amer
Noha Amer

Reputation: 25

Database is not updated ADO.Net

I have a customer class in my program and a customer table in an access database, i want to update customer balance in the database by given value. I wrote all statements which seems to be correct, but unfortunately the database isn't affected. This is the method which I use to update the DB thanks in advance

public static bool UpdateCustomer(Customer customer)
{
   bool EditSucceeded = false;
   string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\GarageProTuning.accdb;";

   using (OleDbConnection _con = new OleDbConnection(connectionString))
   {
      string commandText = "UPDATE Customer SET Balance=@Balance WHERE CustomerID=@CustomerID";

      using (OleDbCommand _cmd = new OleDbCommand(commandText, _con))
      {
         _cmd.Parameters.AddWithValue("@CustomerID", customer.CustomerID);
         _cmd.Parameters.AddWithValue("@Balance", customer.Balance);
         _con.Open();
         int rowsAffected = _cmd.ExecuteNonQuery();
         if (rowsAffected > 0)
            EditSucceeded = true;
         _con.Close();
      }
   }
   return EditSucceeded;
}

Upvotes: 2

Views: 70

Answers (1)

Brian
Brian

Reputation: 5119

You have:

public static bool UpdateCustomer(Customer customer)
{
   bool EditSucceeded = false;
   string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\GarageProTuning.accdb;";

   using (OleDbConnection _con = new OleDbConnection(connectionString))
   {
      string commandText = "UPDATE Customer SET Balance=@Balance WHERE CustomerID=@CustomerID";

      using (OleDbCommand _cmd = new OleDbCommand(commandText, _con))
      { 
         _cmd.Parameters.AddWithValue("@CustomerID", customer.CustomerID);
         _cmd.Parameters.AddWithValue("@Balance", customer.Balance);
         _con.Open();
         int rowsAffected = _cmd.ExecuteNonQuery();
         if (rowsAffected > 0)
            EditSucceeded = true;
         _con.Close();
      }
   }
   return EditSucceeded;
}

What you need is:

public static bool UpdateCustomer(Customer customer)
{
   bool EditSucceeded = false;
   string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\GarageProTuning.accdb;";

   using (OleDbConnection _con = new OleDbConnection(connectionString))
   {
      string commandText = "UPDATE Customer SET Balance=@Balance WHERE CustomerID=@CustomerID";

      using (OleDbCommand _cmd = new OleDbCommand(commandText, _con))
      { 
         _cmd.Parameters.AddWithValue("@Balance", customer.Balance);  // Explanation below
         _cmd.Parameters.AddWithValue("@CustomerID", customer.CustomerID);
         _con.Open();
         int rowsAffected = _cmd.ExecuteNonQuery();
         if (rowsAffected > 0)
            EditSucceeded = true;
         _con.Close();
      }
   }
   return EditSucceeded;
}

OleDbCommand.Parameters are a very particular animal when it comes to how they are added/processed. From the documentation I linked:

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

In a nutshell, you must add your parameters in the same order that they appear in your command text.

Upvotes: 3

Related Questions