none
Updating MS Access Database Fails--Problems with UpdateCommand? RRS feed

  • Question

  • So I have an MS Access database and I am trying to update data inside of it through C#. I have the following bit of code:

    Code Snippet

    private void testUpdate() {

    OleDbConnection nwindConn = new
    OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data
    Source=VumiiOrdersAndInventoryV13.accdb;Persist Security Info=False;");
    OleDbDataAdapter catDA = new OleDbDataAdapter("SELECT ComponentID, ParentComponentID FROM Components", nwindConn);
    catDA.TableMappings.Add("Table", "Components");


    catDA.UpdateCommand = new OleDbCommand("UPDATE Components SET
    ParentComponentID = @ParentComponentID WHERE ComponentID =
    @ComponentID", nwindConn);
    OleDbParameter workParm = catDA.UpdateCommand.Parameters.Add("@ParentComponentID", OleDbType.Integer);
    workParm.SourceColumn = "ParentComponentID";
    workParm = catDA.UpdateCommand.Parameters.Add("@ComponentID", OleDbType.Integer);
    workParm.SourceColumn = "ComponentID";
    workParm.SourceVersion = DataRowVersion.Original;

    DataSet catDS = new DataSet("Components");

    catDA.Fill(catDS, "Components");

    DataRow cRow = catDS.Tables["Components"].Rows[0];
    cRow["ParentComponentID"] = 9;

    catDA.Update(catDS);
    Console.WriteLine("Updated!");
    Console.ReadLine();
    }


    So, as you can see I am trying to change the first record's ParentComponentID. The data from the database does successfully populate my data set and I can print it all out. After I make the change, I can see the change in the data set value. The code runs and "Updated!" prints, but the change does not take place in the database.

    My guess is that my update command is wrong. Anyone have any tips?
    Wednesday, August 6, 2008 9:50 PM

Answers

  • ADO.NET does not support named parameters when using OLEDB. Try replacing the named parameters in your SQL statement with question marks.

    UPDATE Components SET ParentComponentID = ? WHERE ComponentID = ?"

    Just remember to add the parameters to the Parameter collection in the order that they appear in the SQL statement.



    Thursday, August 7, 2008 4:24 PM

All replies

  • ADO.NET does not support named parameters when using OLEDB. Try replacing the named parameters in your SQL statement with question marks.

    UPDATE Components SET ParentComponentID = ? WHERE ComponentID = ?"

    Just remember to add the parameters to the Parameter collection in the order that they appear in the SQL statement.



    Thursday, August 7, 2008 4:24 PM
  • Ahh, you are correct! Thanks, it works now.
    Thursday, August 7, 2008 10:28 PM