Hi All,

I'm trying to figure out what is wrong with my code for days now.... I just started to learn about the databases, so please excuse me if I make stupid mistakes.

So here is the problem:

I use MS Access database to store data in several tables. Occasionally, I want to update records in tblRequest table in my DB:

public void ModifyRecordInDataTable(ref DataTable dtable, ArrayList col, ArrayList val, string key)
        {
            //Set UPDATE Command of the DataAdapter
            if (dtable.TableName == "NodeDataTable")
            { }
            if (dtable.TableName == "RequestDataTable")
            {
                UPDATE_RequestQueryDataTable("tblRequests");
            }
            if (dtable.TableName == "QueryDataTable")
            { 
                
            }
                        
            DataRow editDataRow = dtable.Rows.Find(key);

            //Change DataColumn values of the DataRow

            editDataRow.BeginEdit();
            for (int i = 0; i < col.Count; i++)
            {
                editDataRow[col[i].ToString()] = val[i].ToString();
            }
            editDataRow.EndEdit();
          
            }
               
                int numrows = myDBAdapter.Update(dtable);
                
                dtable.AcceptChanges();

        }
  
private void UPDATE_RequestQueryDataTable(string dtable)
        {
            OleDbCommand myUpdateCommand = myDBConnection.CreateCommand();

myUpdateCommand.CommandText = "UPDATE " + dtable + " SET TimeReq = @TimeReq, AThold = @AThold, DThold = @DThold," +
                " Period = @Period, F1 = @F1, B1 = @B1 " +
                " WHERE NodeID = @NodeID";

myUpdateCommand.Parameters.Add("NodeID", OleDbType.Char, 2, "NodeID").SourceVersion = DataRowVersion.Original;
            myUpdateCommand.Parameters.Add("TimeReq", OleDbType.Char, 21, "TimeReq");
            myUpdateCommand.Parameters.Add("AThold", OleDbType.Char, 2, "AThold");
            myUpdateCommand.Parameters.Add("DThold", OleDbType.Char, 3, "DThold");
            myUpdateCommand.Parameters.Add("Period", OleDbType.Char, 3, "Period");
            myUpdateCommand.Parameters.Add("F1", OleDbType.Char, 5, "F1");
            myUpdateCommand.Parameters.Add("B1", OleDbType.Char, 5, "B1");


myDBAdapter.UpdateCommand = myUpdateCommand;
        
        }

Before that, I use OleDBAdapter.Fill() to fill the DataRequestTable with data from tblRequest (stored in myDB)

However, on line:
int numrows = myDBAdapter.Update(dtable);

I'm getting error "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."

I already searched web about this error and I still cannot figure it out why it is happening. I am the only who is updating the database, there are no other users. The DataRequestTable has primary key defined (NodeID). I have UPDATE statement defined for myDBAdapter. I have no clue why is this happening...

Please help!

Edited 7 Years Ago by John A: added code tags

I already searched web about this error and I still cannot figure it out why it is happening. I am the only who is updating the database, there are no other users. The DataRequestTable has primary key defined (NodeID). I have UPDATE statement defined for myDBAdapter. I have no clue why is this happening...

Have you ensured you don't have any other processes on your machine (viewer/editor of some sort) holding a lock on the record: application that wasn't killed, etc.

Also, is you app doing anything else that might have selected for update or delete operation.

I know you have been working on it several days, but have you tried a shutdown/restart, then running your app as the very first thing you do to know whether it is happening from your app?

No, this is the only application I'm running on my computer. also, I'm able to add new records to the table, so I shouldn't be a problem to modify one, right?
I did restart my computer this morning, I was hoping the same thing...

Welcome to Daniweb Micka10! Please use code tags when posting code:

[code=csharp] ...code here....

[/code]


To answer your question --
You need to disable optimisitic concurrency in the tableAdapter's configuration. When you configure the table adapter you will find an "Advanced" button, click on it and uncheck the middle option as seen in this screen shot.

Attachments no_long_code.png 10.36 KB
This article has been dead for over six months. Start a new discussion instead.