Error message when you update a database by using a stored procedure: "System.Data.DBConcurrencyException" (310375)

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.

1. 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
   					

2. Open Microsoft Visual Studio .NET.
3. Create a new Visual Basic Windows Application project.
4. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.
5. Place a Button control on Form1. Change the Name property of the button to btnTest, and change the Text property to Test.
6. 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 "General Declarations" section of Form1:

   Imports System
   Imports System.Data.OleDb
   Imports System.Data.SqlClient
   					

7. Add the following code after the "Windows Form Designer generated code" region in the Code window:
   Note You must change the User ID <user name> and Password <strong 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 = _
                   "User ID=<username>;Password=<strong password>;Initial Catalog=Northwind;Data Source=myServer"
           Dim myQuery As String = _
                   "SELECT * FROM Employees WHERE employeeid = 1"
           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 = "UpdateEmployee"
           End With
           Dim ds As New DataSet()
           da.Fill(ds, "EmployeeTable")
           ds.Tables("EmployeeTable").Rows(0)("FirstName") = "Jack"
   
           Dim iRowsAffected As Integer
           iRowsAffected = da.Update(ds, "EmployeeTable")
           MessageBox.Show(iRowsAffected.ToString)
           cn.Close()
       End Sub
   					

8. Modify the connection string (myConnString) as appropriate for your environment.
9. Save your project. On the Debug menu, click Start to run your project.
10. Click Test. Notice that the exception is not generated even if the stored procedure tries to update a record that is not present.
11. Add the SET NOCOUNT OFF statement to the stored procedure, and then click Test again. Notice that the DataConcurrencyException exception is generated as expected.