CODEWITHSUNDEEP
c#
DaneEdw
DaneEdw

Reputation: 446

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

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();
                }

        }

Upvotes: 1

Views: 1282

Answers (1)

dash
dash

Reputation: 91510

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.

Upvotes: 4

Related Questions