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
            }
        }
    }

Steve · Accepted Answer · 2016-07-02 14:57:43Z

5

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;

edited Jul 2, 2016 at 14:57

answered Jul 2, 2016 at 13:23

Steve

217k22 gold badges242 silver badges296 bronze badges

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

13 Comments

DethoRhyne Over a year ago

I've tried that before and it didn't work. then I changed to how my code is now which didn't work either. After your answer, I've tried putting Id to the end again, but I still get an syntax with SQL statement error

Steve Over a year ago

Then it is better that you show the whole command text and the whole list of parameters. It is impossible to say what the syntax error is from this snippet.

DethoRhyne Over a year ago

I shortened the query simply because it repeats itself, and based from my previous MVC experience, you could say the class is indeed a model of the DB table with matching data types. I will make the edit in couple of seconds.

DethoRhyne Over a year ago

and regarding your side note, I wouldn't have issues using other methods, I just don't know them yet since before this I didn't work with OleDB at all.

Steve Over a year ago

The code seems to be correct, as I have said, I would only change the AddWithValue for command.Parameters.Add("@Id", OleDbType.Integer).Value = bill.Id; and the same for the other parameters paying particular attention to the datatype expected by the table and the datatype of the properties of your Bill class. Still a single line, but no space database automatic conversion from the parameters values

|

Ozan Gunceler · Accepted Answer · 2016-07-02 13:24:32Z

1

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

answered Jul 2, 2016 at 13:24

Ozan Gunceler

1,07711 silver badges21 bronze badges

11 Comments

DethoRhyne Over a year ago

They are in the exact order. except the Id which in db AND the parameters is first, but in query it's mentioned last since it's in WHERE section

Ozan Gunceler Over a year ago

That is my point... You should not follow the order in the table but the order in your query. When i check, AddWithValue for Id field comes the first, instead, it should come the last

DethoRhyne Over a year ago

Understood, but as I commented above just now, that gives me the same error

Ozan Gunceler Over a year ago

Can you share the full sql? I will also check with the brackets. Maybe they are the issue...

Ozan Gunceler Over a year ago

Can you get rid of the parenthesis and try?

|

Ozan Gunceler · Accepted Answer · 2016-07-02 14:37:34Z

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
}

Through long chat long with Steve we came up with a solution to the problem and went step by step.. Currency was a problem, it needed brackets, and after that using command.Parameters.Add("@Id", OleDbType.Integer).Value = bill.Id; to specify format type for the database fixed issues with dates and everything else. but I do commend you for your work because it worked as well! :)

Collectives™ on Stack Overflow

Updating a table row with OleDB from C#

3 Answers 3

13 Comments

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related