Updating OLEDB Database using UPDATE Query

Question

I've Edited this question to prevent asking another. I'm trying to update a datatable in MS Access using an UPDATE query and Parameters. The code doesn't give an error and runs fine, but it does't save the updated data and I can't work out why.

Here's the code I'm using :

if (Editing)
        {                
            string Query = "UPDATE [Employee] SET PayrollNo=@PayrollNo, FirstName=@FirstName, LastName=@LastName, AnnualHolidayEntitlement=@AnnualHolidayEntitlement, DaysTakenToDate=@DaysTakenToDate WHERE PayrollNo = @SentPayroll"; 
            string ConnString = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\HoliPlanData.accdb;Persist Security Info=False";
            using (OleDbConnection conn = new OleDbConnection(ConnString))
            using (OleDbCommand UpdateEmployee = new OleDbCommand(Query, conn))
            {                
                conn.Open();
                UpdateEmployee.Parameters.AddWithValue("@SentPayroll", OleDbType.Integer).Value = Convert.ToInt32(PassPayrollNo);
                UpdateEmployee.Parameters.AddWithValue("@PayrollNo", OleDbType.Integer).Value = Convert.ToInt32(TxtPayrollNo.Text);
                UpdateEmployee.Parameters.AddWithValue("@FirstName", OleDbType.VarChar).Value = TxtFirstName.Text;
                UpdateEmployee.Parameters.AddWithValue("@LastName", OleDbType.VarChar).Value = TxtLastName.Text;
                UpdateEmployee.Parameters.AddWithValue("@AnnualHolidayEntitlement", OleDbType.Integer).Value = Convert.ToInt32(TxtAHE.Text);
                UpdateEmployee.Parameters.AddWithValue("@DaysTakenToDate", OleDbType.Integer).Value = Convert.ToInt32(TxtDTTD.Text);
                UpdateEmployee.ExecuteScalar();
                BtnSubmit.Enabled = false;
                MessageBox.Show("Data Edited Successfully");
            }

How can I change this code so that it works? Thanks in advance.

EDIT/ANSWER. I didn't know and chances are if you're having problems with an UPDATE query you may not know it either. When adding parameters you need to add them in the same order they are called in the query. In my code the first parameter should be added last, since it is last in the stringQuery. I moved it and it worked fine!

Remove the single quotes, the parametrization will do that for you. `@?` looks decidedly odd. — Alex K., Mar 02 '16 at 16:10
is the payrollNo a varchar in the database? could it be that you are putting a int32 into the parameter but you are setting it to a varchar in the addwithvalue? — Lewis Taylor, Mar 02 '16 at 16:10
FYI you have `AddWithValue(name, DBTYPE)` it should be `AddWithValue(name, value)` although it looks like it would still work as you then overwrite — Alex K., Mar 02 '16 at 16:18
Please edit the code with your changes and use `@something` instead of `@?` and `"?"` — Alex K., Mar 02 '16 at 16:18
I can see that `?` could cause problems as its a non-named OLEDB parameter marker mixed with named paramaters, change it to the same @name format as all the others — Alex K., Mar 02 '16 at 16:28

score 0 · Accepted Answer · edited May 23 '17 at 12:07

0

Why does invoking my UPDATE query in an Access database via OleDb not work?

If anyone else has this issue with UPDATE queries, It took me too long to find this question, where someone clears it up great.

edited May 23 '17 at 12:07

Community

1
1

answered Mar 03 '16 at 09:23

Josh

115
1
3
17

Updating OLEDB Database using UPDATE Query

1 Answers1