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

Question

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?

Maybe the Address_ID does not exist in the database? Therefore nothing will match the WHERE clause and therefore nothing will be updated. I am just guessing, it's not really possible to be sure with only the information you have provided. But if there are no errors, then it's likely the query is being executed successfully, which means it just isn't doing what you expect, and with a simple query like this then the where condition is the most obvious culprit. Put a breakpoint in there and see what address.Address_ID is at runtime — musefan
– musefan, Commented Jun 9, 2015 at 14:54
here is a debug of the function UpdateAddress: the address is: Address_ID 4 long City "2222" string Contact "max" string Customer_ID 1 long FirstAddress false bool PostalCode " " string Street " " string — Orientos
– Orientos, Commented Jun 9, 2015 at 14:57
in the table you are updating, are there any required fields that you are not supplying a value for? also, could you print out the value of dbCommand.CommandText before it tries to update the table? — Gene
– Gene, Commented Jun 9, 2015 at 15:07
here is it: "Update Addresses Set [Street] = atStreet, [PostalCode] = atPostalCode, [City] = atCity, [Contact] = atContact Where [Address_ID] = atAddress_ID" . at =@ but it is not allowed here to use so many of the @ — Orientos
– Orientos, Commented Jun 9, 2015 at 15:25

Gord Thompson · Accepted Answer · 2015-06-09 15:21:35Z

4

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.

answered Jun 9, 2015 at 15:21

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Hall Over a year ago

@Mohammad_N you should mark Gord's response as the answer if it solved your problem.

Orientos Over a year ago

thats righ :). i forgot it

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related