OleDbCommand won't UPDATE MS Access database, no errors thrown

Question

I've been stuck on this problem for a couple of hours now and would really appreciate any help.

What I'm trying to do: Update a 2003 MS Access Database using OleDb.
What happens when I execute the code: No error messages, but database is not updated at all. 0 Rows Affected.
Programming Language: C#

Extra Information:

(1) var_workid is an int, and is the primary key of my table.
(2) f2_sub and f2_field are Comboboxes.
(3) f2_date is a DateTimePicker.
(4) f2_details, f2_pacq, f2_daily, f2_area are all TextBoxes.
(5) My connection works, I can do statements such as SELECT ... FROM ... WHERE with no problem. (6) .NET2.0

In MS Access.
work_id, account_code, field_id, pacquiao, daily, area are all Numbers.
details is Text.
date_done is DateTime

private void btn_update_Click(object sender, EventArgs e)
    {
        string update_query;
        OleDbCommand SQLCommand = new OleDbCommand();

        update_query = "UPDATE [work_done] SET [account_code]= @AccountCode, [field_id] = @FieldID, [details] = @Details, [pacquiao] = @Pacquiao,[daily] = @Daily, [date_done] = @DateDone, [area] = @Area WHERE [work_id] = @WorkID;";
        SQLCommand.CommandText = update_query;
        SQLCommand.Connection = database;

        //Parameters
        SQLCommand.Parameters.AddWithValue("@AccountCode", f2_sub.SelectedValue.ToString());
        SQLCommand.Parameters.AddWithValue("@FieldID", f2_field.SelectedValue.ToString());
        SQLCommand.Parameters.AddWithValue("@Details", f2_details.Text);
        SQLCommand.Parameters.AddWithValue("@Pacquiao", f2_pacq.Text);
        SQLCommand.Parameters.AddWithValue("@Daily", f2_daily.Text);
        SQLCommand.Parameters.AddWithValue("@DateDone", f2_date.Value.ToString());
        SQLCommand.Parameters.AddWithValue("@Area", f2_area.Text);
        SQLCommand.Parameters.AddWithValue("@WorkID", var_workid);

        SQLCommand.CommandText = update_query;
        SQLCommand.Connection = database;

        //string message = "";
        //for (int i = 0; i < SQLCommand.Parameters.Count; i++) 
        //{
        //    message += SQLCommand.Parameters[i].Value.ToString() + "\n";
        //}
        //MessageBox.Show(message);


        int response = SQLCommand.ExecuteNonQuery();

        MessageBox.Show(response + " Update successful!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
        this.ParentForm.refresh();
        Close();
    }

You can easily update no rows if your `WHERE` clause is incorrect. This seems very similar to a question I answered previously: see if http://stackoverflow.com/a/12226939/242520 helps you. — ta.speot.is, Nov 03 '12 at 08:26
The order of parameters added have to be exactly in the order of fields. Try adding `"@WorkID", var_workid);` at first. I have been bitten by this before http://stackoverflow.com/questions/7165661/is-order-of-parameters-for-database-command-object-really-important but back then I had to specify the order as you posted. But I also remember sql working only when where condition is specified first. So try that. Ever since I dropped MSAccess for its such quirks — nawfal, Nov 03 '12 at 08:54
Your code works for me almost exactly as is. I substituted values for the update variables. Check your input, especially work id, as has been suggested. — Fionnuala, Nov 03 '12 at 09:52
@nawfal The where condition does not have to be specified first. How could it be? — Fionnuala, Nov 03 '12 at 09:55
@Remou, I have had such quirk sure about it. Yes I have had MS Access UPDATE query working only when WHERE condition was specified last. But previously I had got it working only when specified first. May be for a SELECT query? I have no JET now to test it. — nawfal, Nov 04 '12 at 03:07

score 2 · Answer 1 · answered Nov 03 '12 at 08:20

2

I think the OleDbCommand does not support parameters like this (OleDbCommand.Parameters Property):

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

answered Nov 03 '12 at 08:20

Simon Mourier

132,049
21
248
298

I tried replacing all of it with ? but it still doesn't work nor does throw any errors. – Beato Bongco Nov 03 '12 at 08:23
This is correct, but with the PARAMETERS syntax that is proprietary to JET/ACE you can make it work. But why bother? Just use a real RDBMS. – ta.speot.is Nov 03 '12 at 08:24
The code will work as long as the parameters are in the correct order. The names are convenient prompts. The code above works in a mock-up. – Fionnuala Nov 03 '12 at 09:53
Further to ta.speot.is - OleDb can use named parameters: see http://stackoverflow.com/questions/4857940/ms-access-named-parameters-and-column-names/20811933#20811933 – Ben McIntyre Dec 28 '13 at 07:19
@BenMcIntyre - yes, but that's not general "OleDb", that's Access/JET OleDb only. – Simon Mourier Dec 28 '13 at 08:23
@SimonMourier: True - good distinction. Note that the OP is about MS Access though. – Ben McIntyre Dec 30 '13 at 07:00

OleDbCommand won't UPDATE MS Access database, no errors thrown

1 Answers1