Updating a table row with OleDB from C#

Question

I am working on an invoice managament application that draws information from an Access database (modern .accdb format) and getting the data works fine, but when I try to update it (following some tutorials, or should I say answers, nothing works.. What is wrong with this code, it... should work.

    public int UpdateDBBill(Bill bill)
    {
        using (var connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\BC.accdb"))
        using (var command = connection.CreateCommand())
        {
            connection.Open();

            command.CommandType = CommandType.Text;
            command.CommandText = "UPDATE Bills SET Payer = @Payer, Category = @Category, Recipient = @Recipient, Currency = @Currency, Amount = @Amount, IBANOfRecipient = @IBANOfRecipient, Model = @Model, ReferenceNumber = @ReferenceNumber, Description = @Description, DueDate = @DueDate, ForMonth = @ForMonth, Paid = @Paid, DatePaid = @DatePaid WHERE Id = @Id";

            command.Parameters.AddWithValue("@Payer", bill.Payer);
            command.Parameters.AddWithValue("@Category", bill.Category);
            command.Parameters.AddWithValue("@Recipient", bill.Recipient);
            command.Parameters.AddWithValue("@Currency", bill.Currency);
            command.Parameters.AddWithValue("@Amount", bill.amount);
            command.Parameters.AddWithValue("@IBANOfRecipient", bill.IBANOfRecipient);
            command.Parameters.AddWithValue("@Model", bill.Model);
            command.Parameters.AddWithValue("@ReferenceNumber", bill.ReferenceNumber);
            command.Parameters.AddWithValue("@Description", bill.Description);
            command.Parameters.AddWithValue("@DueDate", bill.DueDate);
            command.Parameters.AddWithValue("@ForMonth", bill.ForMonth);
            command.Parameters.AddWithValue("@Paid", bill.Paid);
            command.Parameters.AddWithValue("@DatePaid", bill.DatePaid);
            command.Parameters.AddWithValue("@Id", bill.Id);

            try
            {
                return command.ExecuteNonQuery();
            }
            catch
            {
                return -1;//for error
            }
        }
    }

Answer:

    public int UpdateDBBill(Bill bill)
    {
        using (var connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\BC.accdb"))
        using (var command = connection.CreateCommand())
        {
            connection.Open();

            command.CommandType = CommandType.Text;

            command.CommandText = "UPDATE Bills SET Payer = @Payer, Category = @Category, Recipient = @Recipient, [Currency] = @Currency, Amount = @Amount, IBANOfRecipient = @IBANOfRecipient, [Model] = @Model, ReferenceNumber = @ReferenceNumber, DueDate = @DueDate, ForMonth = @ForMonth, Paid = @Paid, DatePaid = @DatePaid WHERE Id = @Id";
            command.Parameters.Add("@Payer", OleDbType.VarChar).Value = bill.Payer;
            command.Parameters.Add("@Category", OleDbType.VarChar).Value = bill.Category;
            command.Parameters.Add("@Recipient", OleDbType.VarChar).Value = bill.Recipient;
            command.Parameters.Add("@Currency", OleDbType.VarChar).Value = bill.Currency;
            command.Parameters.Add("@Amount", OleDbType.VarChar).Value = bill.GetAmount();
            command.Parameters.Add("@IBANOfRecipient", OleDbType.VarChar).Value = bill.IBANOfRecipient;
            command.Parameters.Add("@Model", OleDbType.VarChar).Value = bill.Model;
            command.Parameters.Add("@ReferenceNumber", OleDbType.VarChar).Value = bill.ReferenceNumber;
            command.Parameters.Add("@DueDate", OleDbType.Date).Value = bill.DueDate.Date;
            command.Parameters.Add("@ForMonth", OleDbType.Date).Value = bill.ForMonth.Date;
            command.Parameters.Add("@Paid", OleDbType.Boolean).Value = bill.Paid;
            command.Parameters.Add("@DatePaid", OleDbType.Date).Value = bill.DatePaid.Date;
            command.Parameters.Add("@Id", OleDbType.Integer).Value = bill.Id;

            try
            {
                int Rows = command.ExecuteNonQuery();
                return Rows;
            }
            catch
            {
                return -1;//for error
            }
        }
    }

Answer 1

With OleDb the position of the parameters matters a lot.
OleDb doesn't associate parameters' placeholders with the parameters' names but follows a strictly positional order. So, your query is correct, but when you add the parameters to the collection you should follow the parameter placeholders order.

command.CommandType = CommandType.Text;
command.CommandText = "UPDATE Bills SET ([Payer] = @Payer, [Category] = @Category, ...) WHERE Id = @Id";
command.Parameters.AddWithValue("@Payer", bill.Payer);
command.Parameters.AddWithValue("@Category", bill.Category);
....
command.Parameters.AddWithValue("@Id", bill.Id);

With Access you can name your parameters as you do for its big cousin Sql Server albeit the OleDb docs say that you should use the question mark as parameter placeholder, however the names are simply ignored when the OleDb provider associates the values to the placeholders.

As a side note, consider that AddWithValue is an handy but dangerous method. The parameter type is extracted by the value passed and sometimes this could create a 'DataType mismatch Exception' or wrong conversions (in particular if you pass dates or decimals as strings to AddWithValue)

See Can we stop using AddWithValue already?

EDIT After a long debug session in chat the final problem is identified in the Currency field written withot brackets. Currency is a reserved words in Access and should be enclosed in square bracket. This was not initially evident because the first query proposed by the OP was correctly typed with square bracket but then the square brackets disappeared from the query for whatever reason. The suggestion to NOT use AddWithValue stands to avoid unnecessary conversions from dates to strings and then back to strings....

command.CommandText = "UPDATE Bills SET ([Payer] = @Payer, [Category] = @Category, ...) WHERE Id = @Id";
command.Parameters.Add("@Payer", OleDbType.VarWChar).Value = bill.Payer;
command.Parameters.Add("@Category", OleDbType.VarWChar).Value = bill.Category;
....
command.Parameters.Add("@DueDate", OleDbType.Date).Value = bill.DueDate.Date;
....
command.Parameters.Add("@Id", OleDbType.Integer).Value = bill.Id;

Answer 2

Ok... I found the issue.

First of all, you should put the Currency field in brackets because Access considers it as a datatype if you don't and you get Syntax error.

Then, keep all AddWithValue statements order untouched.

Finally, while adding date fields (DueDate, ForMonth and DatePaid), use ToString("yyyy-MM-dd") so that Access will interpret the value as date. Also, parse the amount field to double.

Below is my version of the code. Hope this will work :)

command.CommandType = CommandType.Text;
command.CommandText = "UPDATE Bills SET Payer = @Payer, Category = @Category, Recipient = @Recipient, [Currency] = @Currency, Amount = @Amount, IBANOfRecipient = @IBANOfRecipient, Model = @Model, ReferenceNumber = @ReferenceNumber, Description = @Description, DueDate = @DueDate, ForMonth = @ForMonth, Paid = @Paid, DatePaid = @DatePaid WHERE Id = @Id";

command.Parameters.AddWithValue("@Payer", bill.Payer);
command.Parameters.AddWithValue("@Category", bill.Category);
command.Parameters.AddWithValue("@Recipient", bill.Recipient);
command.Parameters.AddWithValue("@Currency", bill.Currency);
command.Parameters.AddWithValue("@Amount", Convert.ToDouble(bill.amount));
command.Parameters.AddWithValue("@IBANOfRecipient", bill.IBANOfRecipient);
command.Parameters.AddWithValue("@Model", bill.Model);
command.Parameters.AddWithValue("@ReferenceNumber", bill.ReferenceNumber);
command.Parameters.AddWithValue("@Description", bill.Description);
command.Parameters.AddWithValue("@DueDate", bill.DueDate.ToString("yyyy-MM-dd"));
command.Parameters.AddWithValue("@ForMonth", bill.ForMonth.ToString("yyyy-MM-dd"));
command.Parameters.AddWithValue("@Paid", bill.Paid);
command.Parameters.AddWithValue("@DatePaid", bill.DatePaid.ToString("yyyy-MM-dd"));
command.Parameters.AddWithValue("@Id", bill.Id);

try
{
  return command.ExecuteNonQuery();
}
catch
{
  return -1;//for error
}

Answer 3

I came across with this before. My issue was, I was not providing the parameters in the order they were present in the query.

In your case, as your update goes on with parameters Payer, Category...,Id your AddWithValues should follow the same order.

I hope this helps