none
Yet another "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" RRS feed

  • Question

  • Hello All,

    I know there are many threads on this subject. However, I could not find a solution that would resolve my issue.

    The story is simple: I insert a row on a table and, immediately after, use the OdbcDataAdapter.Update() method to update the newly inserted row as the update is executed, the exception in the subject is thrown.

    Note the following:
    - To make matters more confusing, the code works without problems in SQL 2005 and fails only in SQL Server 2008.
    - The problem happens 100% of the time with a single client accessing the table at a time.
    - The tunderlying table has a PK
    - I use DataRow.BeginEdit()/EndEdit() before performing the Update.

    Please refer to the code below. Any help would be greatly appreciated
    - CD

     private void UpdateAnEntryInCache(EntityStatus statusObj, ref DataSet entityManagerDataSet,
                                                   string tableName, ref DataRow newDataRow, bool blnISExternalProvider)
            {
    
                OdbcDataAdapter objODBCDataAdapter = null;
                try
                {                
                    dbConnection.OpenDBConnection();
    
                    string queryString = "SELECT * FROM " + tableName;
    
                    objODBCDataAdapter = new OdbcDataAdapter(queryString, dbConnection.ConnectionObject);
    
                    newDataRow = entityManagerDataSet.Tables[tableName].Rows.Find((Guid)newDataRow[Constants.DB_COLUMN_ENTITY_TOKEN]);
                    newDataRow.BeginEdit();
                    
                    newDataRow[Constants.DB_COLUMN_PERCENT_COMPLETED] = statusObj.PercentageCompleted;
                   
                    newDataRow.EndEdit();
                    DataSet newDataSet = entityManagerDataSet.GetChanges(DataRowState.Modified);
    
                    if (newDataSet != null)
                    {
                        try
                        {
                            OdbcCommandBuilder autoUpdateCommand = new OdbcCommandBuilder(objODBCDataAdapter);
                            entityManagerDataSet.AcceptChanges();
                            objODBCDataAdapter.Update(newDataSet, tableName);
                            dbConnection.CloseDBConnection();
                            Helper.LogTrace("UpdateAnEntryInCache : Updated Cache : ");
                        }
                        catch (OdbcException ode)
                        {
                            if (dbConnection != null)
                                dbConnection.CloseDBConnection();
    
                            Helper.LogTrace("Exception during update cache : exception message : " + ode.Message + " : stacktrace :" + ode.StackTrace.ToString());
                        }
                        catch (Exception ex)
                        {
                            if (dbConnection != null)
                                dbConnection.CloseDBConnection();
                            Helper.LogTrace("Exception during update cache : exception message : " + ex.Message + " : stacktrace :" + ex.StackTrace.ToString());
                        }
                        finally
                        {
                            if (dbConnection != null)
                                dbConnection.CloseDBConnection();
                        }
                    }
    
                }
                catch (OdbcException)
                {
                    if (dbConnection != null)
                        dbConnection.CloseDBConnection();
                }
                catch (Exception)
                {
                    if (dbConnection != null)
                        dbConnection.CloseDBConnection();
                }
                finally
                {
                    if (dbConnection != null)
                        dbConnection.CloseDBConnection();
                }
            }
    
    





     

    Wednesday, October 7, 2009 9:52 PM

Answers

  • Hi CD,

     

    Welcome to ADO.NET Data Providers forum!

     

    First, please make sure you have updated to Visual Studio 2008 SP1. 

     

    I attempt to reproduce this issue at my lab, but with no avail.  Have try the workaround in these related threads?

    Calling FillSchema before Fill the DataSet and calling FillShema before Update the DataSet

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/a65869d4-09a3-4e35-b041-41b6b71e8896

     

    Manually set Update command by only checking the primary key value
    http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/bfdb40a8-0e29-4897-8251-6368abe24516

     

     

    If these workarounds do not help, I recommend you to build a testing project and data table and we can check whether the problem is caused by the ODBC driver on SQL Server 2008 or only occurs on the specific project.   Please build a console or WinForm application and create a simple data table, maybe Person with PersonID and PersonName in both SQL Server 2008 and SQL Server 2005 databases.   Then please use the following codes to make some updates and check whether the problem exists. 

    ===================================================================================
                string connStr = @"Driver={SQL Server};Server=(local);Database=Test;Trusted_connection=yes";

     

                using (OdbcConnection conn = new OdbcConnection(connStr))

                {

                    OdbcDataAdapter da = new OdbcDataAdapter("SELECT PersonID, PersonName FROM Person", conn);

                    conn.Open();

                    DataTable dt = new DataTable();

                    da.Fill(dt);

                    DataRow dr = dt.Rows[1];

                    dr.BeginEdit();

                    dr["PersonName"] = "New Name";

                    dr.EndEdit();

                    var newDt = dt.GetChanges(DataRowState.Modified);

                    if (newDt != null)

                    {

                        OdbcCommandBuilder builder = new OdbcCommandBuilder(da);

                        dt.AcceptChanges();

                        int result = da.Update(newDt);

                        MessageBox.Show(result.ToString());

                    }

                }
    ===================================================================================

    Please tell me the testing results for further investigation.  Besides, some other information is also quite important, like both the client and server operation system, your Visual Studio version, and detailed information about your project (it seems that you are using cache). 

     

     

    Hope you have a great day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 8, 2009 5:40 AM
    Moderator

All replies

  • Hi CD,

     

    Welcome to ADO.NET Data Providers forum!

     

    First, please make sure you have updated to Visual Studio 2008 SP1. 

     

    I attempt to reproduce this issue at my lab, but with no avail.  Have try the workaround in these related threads?

    Calling FillSchema before Fill the DataSet and calling FillShema before Update the DataSet

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/a65869d4-09a3-4e35-b041-41b6b71e8896

     

    Manually set Update command by only checking the primary key value
    http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/bfdb40a8-0e29-4897-8251-6368abe24516

     

     

    If these workarounds do not help, I recommend you to build a testing project and data table and we can check whether the problem is caused by the ODBC driver on SQL Server 2008 or only occurs on the specific project.   Please build a console or WinForm application and create a simple data table, maybe Person with PersonID and PersonName in both SQL Server 2008 and SQL Server 2005 databases.   Then please use the following codes to make some updates and check whether the problem exists. 

    ===================================================================================
                string connStr = @"Driver={SQL Server};Server=(local);Database=Test;Trusted_connection=yes";

     

                using (OdbcConnection conn = new OdbcConnection(connStr))

                {

                    OdbcDataAdapter da = new OdbcDataAdapter("SELECT PersonID, PersonName FROM Person", conn);

                    conn.Open();

                    DataTable dt = new DataTable();

                    da.Fill(dt);

                    DataRow dr = dt.Rows[1];

                    dr.BeginEdit();

                    dr["PersonName"] = "New Name";

                    dr.EndEdit();

                    var newDt = dt.GetChanges(DataRowState.Modified);

                    if (newDt != null)

                    {

                        OdbcCommandBuilder builder = new OdbcCommandBuilder(da);

                        dt.AcceptChanges();

                        int result = da.Update(newDt);

                        MessageBox.Show(result.ToString());

                    }

                }
    ===================================================================================

    Please tell me the testing results for further investigation.  Besides, some other information is also quite important, like both the client and server operation system, your Visual Studio version, and detailed information about your project (it seems that you are using cache). 

     

     

    Hope you have a great day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 8, 2009 5:40 AM
    Moderator
  • Hi CD,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, October 12, 2009 12:39 PM
    Moderator
  • Lingzhi Sun,

    So, if I prepare a test project, will you look at it?  I can keep it small and to the point.  If so, how shall I get it to you?

    Thanks in advance,

    Daniel.

    Windows Forms Developer
    Saturday, December 12, 2009 7:58 PM
  • Hi Daniel, 

    Yes, of course!   Could you please open a new thread to discuss this issue?  BTW, my mail address is v-micsun @ microsoft.com.  

    Have a nice day!
     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, December 14, 2009 4:23 AM
    Moderator
  • Can anyone direct me to the link to open a new thread?

    Thanks.
    Windows Forms Developer
    Tuesday, December 15, 2009 10:41 PM
  • Hi Daniel, 

    Thank you for sending me the mail.  I have sent a feedback to you.

    Have a nice day!
     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, December 16, 2009 12:30 AM
    Moderator