CODEWITHSUNDEEP
c#winformsms-access
Orientos
Orientos

Reputation: 151

OleDb parameter query in Winforms (C#): no errors but no rows updated

I'm trying to update a table in MS Access, but the update operation doesn't work and I don't get an error.

The rowsAffected are 0, so nothing was updated.

Here is my code:

public bool UpdateAddress(AddressModel address)
    {
        using (OleDbCommand dbCommand = new OleDbCommand())
        {
            // Set the command object properties
            dbCommand.Connection = new OleDbConnection(this.ConnectionString);
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandText = "Update Addresses " +
        " Set [Street] = @Street, [PostalCode] = @PostalCode, " +
        "  [City] = @City, [Contact] = @Contact"+
        " Where [Address_ID] = @Address_ID";

            // Add the input parameters to the parameter collection
            dbCommand.Parameters.AddWithValue("@Street", address.Street);
            dbCommand.Parameters.AddWithValue("@PostalCode", address.PostalCode);
            dbCommand.Parameters.AddWithValue("@City", address.City);
            dbCommand.Parameters.AddWithValue("@Address_ID", address.Address_ID);
            dbCommand.Parameters.AddWithValue("@Contact", address.Contact);

            // Open the connection, execute the query and close the connection
            dbCommand.Connection.Open();
            var rowsAffected = dbCommand.ExecuteNonQuery();
            dbCommand.Connection.Close();

            return rowsAffected > 0;
        }
    }
 AddressModel-Class:
  public class AddressModel
{
    public Int64 Address_ID { get; set; }

    public Int64 Customer_ID { get; set; }

    public string Street { get; set; }

    public string PostalCode { get; set; }

    public string City { get; set; }

    public string Contact { get; set; }

    public bool FirstAddress { get; set; }
}

What could be the problem?

Upvotes: 1

Views: 344

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

With System.Data.OleDb, parameters are strictly positional. OleDb ignores parameter names and only pays attention to the order in which the parameter placeholders appear in the CommandText. Therefore, you need to .Add or .AddWithValue the parameters in exactly the same order that they appear in the CommandText.

In your case you need to reverse the order of the last two dbCommand.Parameters.AddWithValue statements: @Address_ID must be the last parameter added because its placeholder appears last in the CommandText.

Upvotes: 4

Related Questions