OleDbDataAdapter for MS Access updating table, why doesn't my update work?

Question

I'm not sure what it is that i'm doing incorrectly here- in the debugger the changes made to the filename are made correctly to the dataset that i'm pulling from for my update command, but when i check the database afterwards no changes have been made... so i'm a bit confused...

using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
                               "Data Source=J:\\Physics.mdb"))
        {
            using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter("select thesisID, filename FROM Theses", con))
            {

                DataSet ds = new DataSet();
                con.Open();
                dbAdapter.Fill(ds);

                for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                {
                    ds.Tables[0].Rows[j]["filename"] = ds.Tables[0].Rows[j]["filename"].ToString().Replace(',', '_');
                    string newFileName = ds.Tables[0].Rows[j]["filename"].ToString();
                    int ID = Convert.ToInt32(ds.Tables[0].Rows[j]["thesisID"].ToString());
                    using (OleDbCommand updateCommand = con.CreateCommand())
                    {
                       updateCommand.CommandText = "update theses set filename = @newFileName where thesisID = @ID";
                        updateCommand.Parameters.AddWithValue("@ID", ID);
                        updateCommand.Parameters.AddWithValue("@newFileName", newFileName);

                        updateCommand.ExecuteNonQuery();


                    }



                }
                con.Close();
                }

        }

Reverse the order of your parameter adding! It's the nature of OleDBCommand I'm afraid - see http://stackoverflow.com/questions/1476770/oledbcommand-parameters-order-and-priority. Other data providers, like SqlClient, support named parameters, and your code would be fine. — dash, May 02 '12 at 22:17
Thanks for the advice @MichaelTodd. I wasn't aware that closed questions would still be "accessible" — dash, May 02 '12 at 22:28

score 4 · Accepted Answer · edited May 23 '17 at 12:18

Try reversing the order in which you are adding the parameters:

using (OleDbCommand updateCommand = con.CreateCommand()) 
{ 
   updateCommand.CommandType = CommandType.Text;
   updateCommand.CommandText = "update theses set filename = @newFileName where thesisID = @ID"; 
   updateCommand.Parameters.AddWithValue("@newFileName", newFileName);
   updateCommand.Parameters.AddWithValue("@ID", ID);   
   updateCommand.ExecuteNonQuery(); 
}

The reason for this is that OleDb doesn't support named parameters, so the order in which you add them is important.

Note that it is often common to see OleDb queries expressed this way:

using (OleDbCommand updateCommand = con.CreateCommand()) 
{ 
   updateCommand.CommandType = CommandType.Text;
   updateCommand.CommandText = "update theses set filename = ? where thesisID = ?"; 
   updateCommand.Parameters.Add(new OleDbParameter("", "", ""...));
   updateCommand.Parameters.Add(new OleDbParameter("", "", ""...));
   updateCommand.ExecuteNonQuery(); 
}

This emphasises that the order is important - the question marks are merely placeholders which get replaced in the order in which parameters are added to the command.

OleDbDataAdapter for MS Access updating table, why doesn't my update work?

1 Answers1