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

  • Question

  • I created a database application in VS 2008/.NET Framework 3.0 where I have Windows forms, each related to a one or more data base tables (Oracle 10g), and where I can add, edit, and delete data in those tables.
    An error rises in the following situation:
    1. Open a form in order to edit data.
    2. Delete a field value from the current data row by deleting the value from the appropriate TextBox.
    3. Save the current row (No error here. The value is deleted in the database table).
    4. Navigate to the next row and edit any data in this TextBox or in an other input control.
    5. Saving the current row yields the following exception:

    "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records"

       at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
       at Mainsys.MainsysDataSetTableAdapters.COSTCENTRESTableAdapter.Update(COSTCENTRESDataTable dataTable) in C:\Projekte\Visual Studio 2008\Projects\MAINSYS\Mainsys\Mainsys\MainsysDataSet.Designer.vb:Zeile 61534.
       at Mainsys.frmCostcentres.SaveData() in C:\Projekte\Visual Studio 2008\Projects\MAINSYS\Mainsys\Mainsys\frmCostcentres.vb:Zeile 68.


    Code position where error rises:

        Public Function SaveData() As String
            Try
                Me.COSTCENTRESBindingSource.EndEdit()
                Me.COSTCENTRESTableAdapter.Update(Me.MainsysDataSet.COSTCENTRES) '<<<<< ERROR
            Catch ex As Exception
                Return FormMgmt2.SaveDataExceptionHandle(Me, ex)
            End Try
            Return ""
        End Function


    Step 5 would also be ok when step 2 would be not a deleting but any other editing action.

    Wednesday, April 7, 2010 11:15 AM

Answers

  • Ingmar,

    I think I understand the cause of the problem.  It looks like you're running into a very Oracle-specific scenario where values get changed in the database.  Oracle treats empty strings as null values.  So after you perform the update, there's an empty string in the CC_FREETXT1 column in the DataRow, but a null value for that column in the corresponding row in the database.  Even though Oracle treats an empty string as null, a comparison in a query between an empty string and null will fail.  As a result, the update attempt fails because the contents of the original values in the DataRow and the contents of database row do not match.

    Are you using ODP.NET or Microsoft's Oracle provider (System.Data.OracleClient)?  My hope is that Oracle's ADO.NET provider (commonly called ODP.NET) is aware of this Oracle-specific behavior and includes additional logic in the updating logic it generates to accommodate for it.  If not, it's worth letting the ODP.NET team know about the scenario so they can try to handle it better going forward.

    I don't have Oracle installed on this machine, so I'm downloading it to try to confirm.  In the meantime, I've simulated the problem using SQL Server by manually setting the database value to null after the successful update.  There are a couple relatively straightforward workarounds in case ODP.NET does not accommodate for this Oracle behavior.

    1.)  After successful updates you can check the contents of the DataRow for empty strings and change them to DBNull.Value to keep the DataRow in synch with the database row.  Make sure you're not leaving this as a pending change either by making this change in the DataAdapter's RowUpdated event or by calling AcceptChanges on the DataRow immediately after making the change.  Here's an example of some code you could use in the RowUpdated event:

     

        If e.StatementType = StatementType.Delete Then Return
        If e.Status <> UpdateStatus.Continue Then Return
    
        Dim row As DataRow = e.Row
        For Each col As DataColumn In row.Table.Columns
            If col.DataType Is GetType(String) AndAlso row(col) = String.Empty Then
                row(col) = DBNull.Value
            End If
        Next col

     

     

    2.)  Refresh the contents of the row after successful inserts and updates.  With Oracle 10g and above, this process is easier than with prior versions through the RETURNING clause.  You can access the contents of the modified row in the query and pass values back into variables.

     

    3.)  You can accommodate for Oracle's null/empty string conversion in the updating logic.  By default, concurrency checks in the WHERE clause of the UpdateCommand and DeleteCommand include logic for nullable columns that effectively boils down to:

    Column = ParameterValue OR (Column IS NULL AND ParameterValue IS NULL)

    You can accommodate for Oracle's null/empty string conversion by changing this logic for character-based nullable columns to:

    Column = ParameterValue OR (Column IS NULL AND (ParameterValue IS NULL OR ParameterValue = '')

     

    My Oracle download should finish sometime tonight and I hope to get a demo working with Oracle over the weekend, but I figured I'd pass along this information as a best guess for now.  I hope this information proves helpful.


    David Sceppa
    Friday, April 9, 2010 7:51 PM
    Moderator

All replies

  • To make a long story somewhat short. You have deleted the row in the database, but the row is still in the dataset/datatable.
    When updating the dataset, optimistic concurrency is used. This means that it will create an updatestatment that includes
    all the columns and the values for the columns that was there when you filled the datatable.

    Since you have deleted a row with (for example) ID = 1, then an update as the following: UPDATE ... WHERE ID = 1,
    will fail (return 0) since there is no row with ID = 1.
    This is also why it works when you do not delete a row in the previous step.

    This example should show this (SQL Server, but same principle applies)

    Create a table:

    create table UpdateTable(id int primary key, txt nvarchar(20))
    insert into UpdateTable values (1, 'One')
    insert into UpdateTable values (2, 'Two')
    insert into UpdateTable values (3, 'Three')
    --drop table UpdateTable

    Create code:

            static void Main(string[] args)
            {
                string cs = @"<your connectionstring>;Integrated Security=true";
                using (SqlConnection con = new SqlConnection(cs))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = con.CreateCommand();
                        cmd.CommandText = "SELECT id, txt FROM UpdateTable";
    
                        // Create and fill dataadapter.
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.UpdateCommand = new SqlCommandBuilder(da).GetUpdateCommand();
                        DataSet ds = new DataSet();
                        da.Fill(ds);
    
                        // Delete in database. This will not remove row from dataset/datatable.
                        cmd.CommandText = "DELETE FROM UpdateTable WHERE id = 1";
                        cmd.ExecuteNonQuery();
    
                        // Edit the row in the datatable
                        ds.Tables[0].Rows[0]["txt"] = "Something";
                        // Show SQL that will be executed.
                        da.RowUpdating += new SqlRowUpdatingEventHandler(da_RowUpdating);
    
                        // This will fail since there is no row in database that matches the updatestatement.
                        // This is becuase we removed it earlier.
                        da.Update(ds);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex);
                    }
                }
            }
    
            static void da_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
            {
                if (e.Command != null)
                {
                    Console.WriteLine("Command type: -> {0}", e.StatementType);
                    Console.WriteLine("Command text: \n{0}", e.Command.CommandText);
                    Console.WriteLine("\nParameters:");
                    foreach (SqlParameter p in e.Command.Parameters)
                    {
                        Console.WriteLine("\t{0} - {1}", p.ParameterName, p.Value);
                    }
                }
            }

    Output:

    Command type: -> Update
    Command text:
    UPDATE [UpdateTable] SET [txt] = @p1 WHERE (([id] = @p2) AND ((@p3 = 1 AND [txt] IS NULL) OR ([txt]
    = @p4)))

    Parameters:
            @p1 - Something
            @p2 - 1
            @p3 - 0
            @p4 - One
    System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
       at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)  
       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)

    HTH
    //Michael

     


    This posting is provided "AS IS" with no warranties.
    Wednesday, April 7, 2010 12:06 PM
  • The logic makes sense and shouldn't cause a concurrency failure.  In your current project, I'd suggest checking after Step 3 to determine the RowState of the DataRow you modified in Step 2.  If I understand your scenario correctly, after submitting the update (Step 3) that DataRow should have a RowState of Unchanged.  If the row is still marked as Modified, then that would explain the concurrency failure and we can try to figure out why the RowState is still Modified.

    I'd also suggest isolating the problem in a small and separate sample project to make it easier to drill into.


    David Sceppa
    Wednesday, April 7, 2010 6:47 PM
    Moderator
  • @ Michael:
    Thank you for the explanation. But there is no error when deleting an entire row. The error appears when I delete a field value from a row, save that row and then edit and save another row.

    @ David:
    Thank you for the hints. After submitting the update (step 3) RowState is Unchanged as expected.

    @ all:
    I reduced the error situation to a project with a form having a databound TextBox:
    1. New Windows Forms Application project.
    2. New DataSource from database.
    3. Drag a database table field from the Data Sources tree into the form.
    (This automatically creates a TextBox for that field, and BindingNavigator, etc.)

    After starting the project I did the following:
    1. Remove the value in the TextBox.
    2. Click the Save button in the Binding Navigator.
    3. Enter a value in the TextBox.
    4. Click the Save button in the Binding Navigator.

    Here the error appears again.

    Ingmar

    Thursday, April 8, 2010 11:18 AM
  • As a little background, the concurrency exception occurs when the database reports that the UPDATE or DELETE query that you're executing to submit the pending change did not update any rows.  This usually occurs when the original values in the DataRow are out of synch with what's currently in the corresponding row in the database.  This can occur when trying to update the same row multiple times if one of the columns is modified by the database during the update (for example, an auto-increment or sequence on insert, or a column whose value is updated via a trigger).

    You could check the contents of the row in the database via Oracle's tools or via Visual Studio Server Explorer when the concurrency exception occurs to see if the values in the database row are what you'd expect just prior to submitting your update.  To programmatically check the original value in a DataColumn of a modified DataRow, you can use code like:

    row("Column", DataRowVersion.Original)

    Is this the first time you've built this type of functionality with your database?  Or has this approach worked with other tables in the past, which might indicate that there's something special about this particular table or query.

    With that said, is there any chance you could post that small sample, along with a simple SQL script to create and populate the table?  I'd like to take a look and reproduce the problem rather than ask a ton more questions.



    David Sceppa
    Thursday, April 8, 2010 8:26 PM
    Moderator
  • Hi David,

    Quote:
    This usually occurs when the original values in the DataRow are out of synch with what's currently in the corresponding row in the database.  This can occur when trying to update the same row multiple times if one of the columns is modified by the database during the update

    The database definitely does not modify data by itself while editing and saving data by the application. Therefore the data in the database table should be exactly the same as in the original datarow version.

    Also the test application I mentioned in my previous posting has no code added by myself. Its just "Visual Studio out of the box".

    Here is the test app you asked for:

    create table COSTCENTRES(
    	CC_ID           varchar2(10 CHAR)       not null,
    	CC_DESCR        varchar2(50 CHAR)           null,
    	CCGRP_ID        varchar2(10 CHAR)           null,
    	CC_FREETXT1     varchar2(50 CHAR)           null,
    	CC_FREETXT2     varchar2(50 CHAR)           null,
    	CC_FREETXT3     varchar2(50 CHAR)           null,
    	CC_FREETXT4     varchar2(50 CHAR)           null,
    	CC_FREETXT5     varchar2(50 CHAR)           null,
    	CC_FREENO1      number(12,2) default 0      null,
    	CC_FREEDATE1    date                        null,
    	CC_INS_DATE     date                        null,
    	CC_INS_USER     varchar2(20 CHAR)           null,
    	CC_MODI_DATE    date                        null,
    	CC_MODI_USER    varchar2(20 CHAR)           null,
    	CC_COST_CHARGE  varchar2(1)                 null
    );
    alter table COSTCENTRES add (constraint COSTCENTRES_PK primary key (CC_ID));
    
    insert into COSTCENTRES (CC_ID, CC_DESCR, CC_FREETXT1) values ('A', 'CC A', '11');
    insert into COSTCENTRES (CC_ID, CC_DESCR, CC_FREETXT1) values ('B', 'CC B', '22');
    commit;
    

    The test application istelf is as posted before:
    1. Create new Windows Forms Application project.
    2. Create new DataSource from database, select table COSTCENTRES.
    3. Drag field CC_FREETXT1 from table COSTCENTRES into the form.
    4. Start application.

    5. Now remove value "11" from CC_FREETXT1TextBox.
    6. Click the Save button in the Binding Navigator.
    7. Enter value "111" into the empty CC_FREETXT1TextBox.
    8. Click the Save button in the Binding Navigator again.

    Now the error appears.

    Maybe the problem is here (notice the comments):

    After step 6:

    Private Sub COSTCENTRESBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles COSTCENTRESBindingNavigatorSaveItem.Click
        Dim V As Object ' <- Code added by me.
        Me.Validate()
        Me.COSTCENTRESBindingSource.EndEdit()
        V = CType(Me.COSTCENTRESBindingSource.Current, DataRowView).Row("CC_FREETXT1", DataRowVersion.Original) ' <- Code added by me.
        '(a)  V = "11" as expected.
        Me.TableAdapterManager.UpdateAll(Me.DataSet1)
        V = CType(Me.COSTCENTRESBindingSource.Current, DataRowView).Row("CC_FREETXT1", DataRowVersion.Original)' <- Code added by me.
        '(b)  V = "" which now is the value of the empty TextBox.
    End Sub
    

    After step 8:

    Private Sub COSTCENTRESBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles COSTCENTRESBindingNavigatorSaveItem.Click
        Dim V As Object
        Me.Validate()
        Me.COSTCENTRESBindingSource.EndEdit()
        V = CType(Me.COSTCENTRESBindingSource.Current, DataRowView).Row("CC_FREETXT1", DataRowVersion.Original)
        '(c)  V = "" unchanged since last save action in (b).
        '     but the current value in the database is System.DbNull
        '     which is not ""!
        Me.TableAdapterManager.UpdateAll(Me.DataSet1)
        V = CType(Me.COSTCENTRESBindingSource.Current, DataRowView).Row("CC_FREETXT1", DataRowVersion.Original)
    End Sub
    

    Best Regards,

    Ingmar

     

    Friday, April 9, 2010 7:56 AM
  • Ingmar,

    I think I understand the cause of the problem.  It looks like you're running into a very Oracle-specific scenario where values get changed in the database.  Oracle treats empty strings as null values.  So after you perform the update, there's an empty string in the CC_FREETXT1 column in the DataRow, but a null value for that column in the corresponding row in the database.  Even though Oracle treats an empty string as null, a comparison in a query between an empty string and null will fail.  As a result, the update attempt fails because the contents of the original values in the DataRow and the contents of database row do not match.

    Are you using ODP.NET or Microsoft's Oracle provider (System.Data.OracleClient)?  My hope is that Oracle's ADO.NET provider (commonly called ODP.NET) is aware of this Oracle-specific behavior and includes additional logic in the updating logic it generates to accommodate for it.  If not, it's worth letting the ODP.NET team know about the scenario so they can try to handle it better going forward.

    I don't have Oracle installed on this machine, so I'm downloading it to try to confirm.  In the meantime, I've simulated the problem using SQL Server by manually setting the database value to null after the successful update.  There are a couple relatively straightforward workarounds in case ODP.NET does not accommodate for this Oracle behavior.

    1.)  After successful updates you can check the contents of the DataRow for empty strings and change them to DBNull.Value to keep the DataRow in synch with the database row.  Make sure you're not leaving this as a pending change either by making this change in the DataAdapter's RowUpdated event or by calling AcceptChanges on the DataRow immediately after making the change.  Here's an example of some code you could use in the RowUpdated event:

     

        If e.StatementType = StatementType.Delete Then Return
        If e.Status <> UpdateStatus.Continue Then Return
    
        Dim row As DataRow = e.Row
        For Each col As DataColumn In row.Table.Columns
            If col.DataType Is GetType(String) AndAlso row(col) = String.Empty Then
                row(col) = DBNull.Value
            End If
        Next col

     

     

    2.)  Refresh the contents of the row after successful inserts and updates.  With Oracle 10g and above, this process is easier than with prior versions through the RETURNING clause.  You can access the contents of the modified row in the query and pass values back into variables.

     

    3.)  You can accommodate for Oracle's null/empty string conversion in the updating logic.  By default, concurrency checks in the WHERE clause of the UpdateCommand and DeleteCommand include logic for nullable columns that effectively boils down to:

    Column = ParameterValue OR (Column IS NULL AND ParameterValue IS NULL)

    You can accommodate for Oracle's null/empty string conversion by changing this logic for character-based nullable columns to:

    Column = ParameterValue OR (Column IS NULL AND (ParameterValue IS NULL OR ParameterValue = '')

     

    My Oracle download should finish sometime tonight and I hope to get a demo working with Oracle over the weekend, but I figured I'd pass along this information as a best guess for now.  I hope this information proves helpful.


    David Sceppa
    Friday, April 9, 2010 7:51 PM
    Moderator
  • Hi David,

    1.

    Quote:
    Are you using ODP.NET or Microsoft's Oracle provider (System.Data.OracleClient)?
    I use System.Data.OracleClient.

    2. Thank you for confirming my assumption in my previous posting. Your suggested workaround helps:

    Me.COSTCENTRESTableAdapter.Update(Me.MainsysDataSet.COSTCENTRES)
    ' After saving make all fields with "" to DbNull:
    Dim Row As DataRow = CType(Me.COSTCENTRESBindingSource.Current, DataRowView).Row
    For Each Col As DataColumn In Row.Table.Columns
        If Col.DataType Is GetType(String) Then
            If Row(Col) Is DBNull.Value Then
            ElseIf Row(Col) = String.Empty Then
                Row(Col) = DBNull.Value 
            End If
        End If
    Next
    Row.Table.AcceptChanges() ' makes RowState = Unchanged
    

    3. I had no chance yet to try ODP.NET.

    Thank you very much.

    Best Regards,

    Ingmar

    Saturday, April 10, 2010 1:54 PM
  • Ingmar,

    I drilled deeper into the problem and resolving it is a little easier than I'd expected.

    I typically recommend using the following logic in a concurrency check for a nullable field in the WHERE clause of the UpdateCommand or DeleteCommand's CommandText:
      (Column = @Column_Orig OR (Column IS NULL AND @Column_Orig IS NULL))
    Since (NULL = NULL) evaluates to false according to ANSI standards, the concurrency check uses IS NULL to ensure the expression evaluates to true if both the current value in the database row and the original value in the DataRow are both null.

    When I used this logic, rather than a CommandBuilder in a simple repro, the problem no longer occurred.  In other words, Oracle evaluates (<empty string> IS NULL) to true and the query behaves as expected and the update succeeds.  I found the same results using both Microsoft and Oracle's ADO.NET provider.  In each case, using the CommandBuilder logic encountered the problem but using my own code (which appears at the end of this post) did not.

    It turns out that the base CommandBuilder class uses slightly different logic which, combined with the Oracle-specific empty string -> null conversion, is responsible for the behavior you're seeing.  The concurrency check built by CommandBuilders looks something like this:
      (Column = @Column_Orig OR (Column IS NULL AND @Column_Null = 1))
    
    The CommandBuilder class tries to help by handling the null check for the original value in the DataRow from within your code rather than at the database.  By setting the SourceColumnNullMapping property on the ADO.NET parameter object to true, the actual value for the parameter sent to the database is 1 if the original value in the DataRow is DBNull.Value, and 0 otherwise.

    The updating logic in the TableAdapters is generated at design-time using CommandBuilders, so you see the same behavior there.

    Checking for empty strings after successful updates and setting them to DBNull.Value (and accepting those changes) is probably the simplest way to handle the behavior since it avoids having to modify the updating logic built within the TableAdapters.  However, if you're writing your own code, I'd recommend sticking with the updating logic that handles the scenario without causing you to manually convert empty strings to DBNull.Value.

    One thing I will say in defense of the concurrency check that the CommandBuilder uses is that it's definitely more efficient for ADO.NET providers that support positional rather than named parameters, such as the System.Data.OleDb and System.Data.Odbc.  For those providers, the CommandBuilder's logic results in less data being passed from your .NET code to the database in order to perform the update.  If you're writing your own updating logic using a provider that only supports positional parameters, using the SourceColumnNullMapping property on your Parameter objects is worth considering.

    I hope this information proves helpful.

    'Visual Basic code (using ODP.NET)
    
        Sub EmptyStringNullUpdate(ByVal connection As OracleConnection, _
                                  ByVal useCommandBuilder As Boolean)
            Console.WriteLine("Calling EmptyStringNullUpdate with " & _
                              "useCommandBuilder = {0}", useCommandBuilder)
    
            Dim command As OracleCommand = connection.CreateCommand()
            Dim commandText As String
    
            Try
                commandText = "CREATE TABLE UpdateProblem " & _
                              "  (ID int PRIMARY KEY, " & _
                              "   Col2 varchar2(255))"
                command.CommandText = commandText
                command.ExecuteNonQuery()
            Catch ex As Exception
                command.CommandText = "DELETE FROM UpdateProblem"
                command.ExecuteNonQuery()
            End Try
    
            commandText = "INSERT INTO UpdateProblem (ID, Col2) " & _
                          "  VALUES (1, 'Initial Value')"
            command.CommandText = commandText
            command.ExecuteNonQuery()
    
            commandText = "SELECT ID, Col2 FROM UpdateProblem"
            Dim adapter As New OracleDataAdapter(commandText, connection)
    
            If useCommandBuilder Then
                Dim commandBuilder As New OracleCommandBuilder(adapter)
                commandText = commandBuilder.GetUpdateCommand().CommandText
            Else
                commandText = _
                    "    UPDATE UpdateProblem " & vbCrLf & _
                    "      SET ID=:ID, Col2=:Col2 " & vbCrLf & _
                    "      WHERE ID=:ID_Orig AND " & vbCrLf & _
                    "        (Col2=:Col2_Orig OR " & vbCrLf & _
                    "           (Col2 IS NULL AND :Col2_Orig IS NULL))"
                adapter.UpdateCommand = New OracleCommand(commandText, _
                                                          connection)
                Dim pc As OracleParameterCollection
                pc = adapter.UpdateCommand.Parameters
                pc.Add(":ID", OracleDbType.Int32, 0, "ID")
                pc.Add(":Col2", OracleDbType.Varchar2, 255, "Col2")
                pc.Add(":ID_Orig", OracleDbType.Int32, 0, "ID")
                pc(":ID_Orig").SourceVersion = DataRowVersion.Original
                pc.Add(":Col2_Orig", OracleDbType.Varchar2, 255, "Col2")
                pc(":Col2_Orig").SourceVersion = DataRowVersion.Original
                adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None
            End If
    
            'Display Update CommandText
            Console.WriteLine("  Update CommandText:")
            Console.WriteLine(commandText)
    
            'Retrieve current contents of table
            Dim table As New DataTable()
            adapter.Fill(table)
    
            'Set one column to an empty string
            Dim row As DataRow = table.Rows(0)
            row("Col2") = ""
    
            'Submit the pending change
            adapter.Update(table)
    
            'Show how many rows in database have Col2 set to ''
            commandText = "SELECT COUNT(ID) FROM UpdateProblem " & _
                          "  WHERE ID = 1 AND Col2 = ''"
            command.CommandText = commandText
            Console.WriteLine("  {0} row(s) in DB w/ Col2 = ''", _
                              command.ExecuteScalar())
    
            'Show how many rows in database have Col2 set to null
            commandText = "SELECT COUNT(ID) FROM UpdateProblem " & _
                          "  WHERE ID = 1 AND Col2 IS NULL"
            command.CommandText = commandText
            Console.WriteLine("  {0} row(s) in DB w/ Col2 IS NULL", _
                              command.ExecuteScalar())
    
            'Modify the row again
            row("Col2") = "Subsequent Change"
    
            Try
                adapter.Update(table)
    
                'Succeeds with UPDATE query in code using 
                '  (Col = :Col_Orig OR (Col IS NULL AND :Col_Orig IS NULL))
                Console.WriteLine("  Subsequent update succeeded!")
    
            Catch ex As Exception
                'Fails with UPDATE query from CommandBuilder using 
                '  (Col = :Col_Orig OR (Col IS NULL AND :Col_Null = 1))
                Console.WriteLine("  Subsequent update failed! - {0}", _
                                  ex.Message)
            End Try
    
            Console.WriteLine()
        End Sub

    David Sceppa
    Wednesday, April 14, 2010 4:37 AM
    Moderator
  • Hi,

    I am issue for updating Two different table which are in same also in Different Database. When using following code, it is not updating second table and saying "concurrency violation the updatecommand affected 0 of the expected" error. Please note in following code I am updating Grid one column and updating the Database.

    "mySymbolMastertable" table is already populated with changes.

    PLEASE HELP...

     

    private void

    UpdateDatabases()

    {

    strAddResult =new StringBuilder (); 

    try

    {

    DataTable dtsec= new DataTable () ;

    dtsec= mySymbolMastertable.GetChanges();

     if (connList != null )

    { 

    for (int i = 0; i < connList.Count; i++)

    {

     string connectionName;

     string sqlString;

     DataTable dt = new DataTable

    ();

    connectionName = connList[i].ToString();

    connectionString = System.Configuration.ConfigurationSettings .AppSettings[connectionName];

     SqlConnection mySQLConnection = new SqlConnection (connectionString);  

     if (connectionName == "SecMasterConnectionSec" )

    {

    sqlString = "SELECT * FROM [SymbolMaster_Secondary]" ;

    }

     else 

    {

    sqlString ="SELECT * FROM [SymbolMaster]" ;

     SqlDataAdapter myDataAdapter = new SqlDataAdapter (sqlString, mySQLConnection);

     SqlCommandBuilder myCmdBuilder = new SqlCommandBuilder (myDataAdapter);

     dt = dtsec; 

     // dt = mySymbolMastertable.GetChanges();  

     //Add Handler

     //myDataAdapter.RowUpdating += new SqlRowUpdatingEventHandler(myDataAdapter_RowUpdating);

     myDataAdapter.RowUpdated +=new SqlRowUpdatedEventHandler (myDataAdapter_RowUpdated);

    myDataAdapter.ContinueUpdateOnError =true ;

     //myDataAdapter.InsertCommand = myCmdBuilder.GetInsertCommand();

     //myDataAdapter.UpdateCommand = myCmdBuilder.GetUpdateCommand();

     myDataAdapter.Update(dt);

     //Add Handler

     // myDataAdapter.RowUpdating += new SqlRowUpdatingEventHandler(myDataAdapter_RowUpdating);

    myDataAdapter.RowUpdated +=new SqlRowUpdatedEventHandler (myDataAdapter_RowUpdated);

     myCmdBuilder =null ;

    myDataAdapter =null;

    mySQLConnection =null ;

     }}

    } 

    catch (Exception ex)

     

    ''Catching Exception

    }}

    

    PLEASE HELP...

     

     

    Monday, November 14, 2011 4:01 PM
  • I'm using the ODP OracleCommandBuilder to update my tables. It is including all fields of my table in the WHERE clause when executing an update or delete command.

    Can I do anything so that the OracleCommandBuilder uses only the primary key in the WHERE clause?

    Thanks. 

    Friday, December 16, 2011 10:45 PM
  • Hi Micheal,

    I have undertsood the reason for this exception error, but what is the workaround for that.I am getting this exception while using enterprise library bulk update method i.e UpdateDataSet .

    Since i have no control over how and when an update is made to the row which no more exist in the db ( that's why the error ).

    An obvious way is to do row by row comparison but that is a tedious operation.How can I avoid that and simply ignore any such rows which exist no more and continue updating the rest of the rows.

    Thanks,

    BR,

    JollyWagoner

    Friday, September 28, 2012 3:49 PM