Microsoft KB Archive/310375

= Error message when you update a database by using a stored procedure: &quot;System.Data.DBConcurrencyException&quot; =

Article ID: 310375

Article Last Modified on 5/13/2007

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft Visual Basic .NET 2002 Standard Edition

-



This article was previously published under Q310375



This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Data.OleDb
 * System.Data.SqlClient



SYMPTOMS
When you use a DataAdapter object to write changes back to a database by means of a stored procedure, you may receive the following error message:

An unhandled exception of type 'System.Data.DBConcurrencyException' occurred in system.data.dll

Additional information: Concurrency violation: the UpdateCommand affected 0 records.

If the data is not updated correctly, you should receive a DataConcurrencyException exception instead of the above-mentioned error message.



CAUSE
This problem can occur if the stored procedure runs the SET NOCOUNT ON statement.



RESOLUTION
To receive an exception when the data is not updated properly, you must explicitly add the SET NOCOUNT OFF statement to the stored procedure.



STATUS
This behavior is by design.



MORE INFORMATION
To determine if a concurrency problem exists, the DataAdapter relies on the RecordsAffected property that the stored procedure returns. If you set NOCOUNT to ON, the stored procedure does not return the correct number of affected records, and the DataAdapter cannot determine that an error has occurred.

Steps to Reproduce the Behavior
This sample code uses the Employees table of the Northwind sample database that is included with Microsoft SQL Server.   Paste the following statements into the SQL Query Analyzer tool or the ISQL utility: CREATE PROCEDURE UpdateEmployee AS   SET NOCOUNT ON    UPDATE Employees SET FirstName = 'Jay'  WHERE EmployeeID = 1000 SELECT * FROM employees GO                    Open Microsoft Visual Studio .NET. Create a new Visual Basic Windows Application project. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not. Place a Button control on Form1. Change the Name property of the button to btnTest, and change the Text property to Test .</li>  Use the Imports statement on the System, System.Data, and System.Data.SqlClient namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add the following code to the &quot;General Declarations&quot; section of Form1: Imports System Imports System.Data.OleDb Imports System.Data.SqlClient </li>  Add the following code after the &quot;Windows Form Designer generated code&quot; region in the Code window:

Note You must change the User ID and Password to the correct values. Make sure that the User IdD has the appropriate permissions to perform these operations on the database.

Private Sub btnTest_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles btnTest.Click Dim myConnString As String = _ &quot;User ID= ;Password= ;Initial Catalog=Northwind;Data Source=myServer&quot; Dim myQuery As String = _ &quot;SELECT * FROM Employees WHERE employeeid = 1&quot; Dim cn As New SqlConnection(myConnString) cn.Open

Dim da As New SqlDataAdapter da.SelectCommand = New SqlCommand(myQuery, cn)

da.UpdateCommand = New SqlCommand With da.UpdateCommand .Connection = cn           .CommandType = CommandType.StoredProcedure .CommandText = &quot;UpdateEmployee&quot; End With Dim ds As New DataSet da.Fill(ds, &quot;EmployeeTable&quot;) ds.Tables(&quot;EmployeeTable&quot;).Rows(0)(&quot;FirstName&quot;) = &quot;Jack&quot;

Dim iRowsAffected As Integer iRowsAffected = da.Update(ds, &quot;EmployeeTable&quot;) MessageBox.Show(iRowsAffected.ToString) cn.Close End Sub </li> Modify the connection string (myConnString) as appropriate for your environment.</li> Save your project. On the Debug menu, click Start to run your project.</li> Click Test. Notice that the exception is not generated even if the stored procedure tries to update a record that is not present.</li> Add the SET NOCOUNT OFF statement to the stored procedure, and then click Test again. Notice that the DataConcurrencyException exception is generated as expected.</li></ol>

<div class="references_section">