1 reply to this topic

[williamevanl]

:)
I have spent the past couple of days on this. I'm trying to update a row to a access database and I keep getting the error "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

My Code looks like:

 public partial class Form1 : Form

    {

        OleDbConnection myconnection = new OleDbConnection();

        OleDbDataAdapter mydata_adapter = new OleDbDataAdapter();

        DataTable Contacts_Table = new DataTable();

        int m_rowPosition = 0;

   

        public Form1()

        {

            InitializeComponent();


            

            myconnection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SimpleTest.MDB;Mode=ReadWrite;Persist Security Info=False";

            OleDbCommandBuilder m_cbCommandBuilder = new OleDbCommandBuilder(mydata_adapter);

            mydata_adapter = new OleDbDataAdapter("Select * From Contacts", myconnection);


            mydata_adapter.Fill(Contacts_Table);




            DataRow Contacts_Row = Contacts_Table.Rows[0];

            Contacts_Row ["Animal"] = "Dinosaur";

            this.ShowCurrentRecord();


            

            

            Contacts_Table.Rows[m_rowPosition]["Animal"] = Animal.Text;

            Contacts_Table.Rows[m_rowPosition]["Weight"] = Weight.Text;


            mydata_adapter.Update(Contacts_Table);

        }

I have read that generally this happens when a primary key isn't set but I I've done that. My update command is

UPDATE `Contacts` SET `Animal` = ?, `Weight` = ? WHERE (((? = 1 AND `ID` IS NULL) OR (`ID` = ?)) AND (`Animal` = ?) AND ((? = 1 AND `Weight` IS NULL) OR (`Weight` = ?)))

This is created automatically though so I don't know if its valid.

I've also been told to select the "refresh the data table" option but it is grayed out and I don't think it is supported by access databases. Anywho, I'm freaking out!

[BuckAMayzing]

Ok, here's the thing. You need to add update, delete, and insert commands to the "mydata_adapter" variable. I'm going to assume animal name is the primary key here. The methods are (keep in mind I'm typing this from memory, so I can't guarantee that capitalization is good):

mydata_adapter.UpdateCommand = myConnection.createCommand();
mydata_adapter.UpdateCommand.CommandText = "update Contacts set Weight = @Weight where Animal = @Animal;";
mydata_adapter.UpdateCommand.Parameters.Add("@Weight",OleDbType.[THE TYPE OF YOUR DATA GOES HERE], [THE SIZE GOES HERE], "Weight");
mydata_adapter.UpdateCommand.Parameters.Add("@Animal",OleDbType.[See above], [See Above], "Animal");

Then you should be able to properly update it. The delete command is similar, but you only need the "@Animal" parameter, assuming it's the primary key. For inserts, you don't need a where clause, just insert values(@Weight, @Animal), then add the parameters. You'll need all four commands (including the select statement that you provided in the constructor) to have a working OleDbDataAdapter. Hope that helps, and if you kill that puppy, I'll find you.